David Taylors' Business Intelligence Blog

I’ve installed BusinessObjects about a hundred times and there is very little that’s changed about the installation wizard from a user interface perspective since Crystal Enterprise 10.  BusinessObjects has always included “in the box” all the components necessary to successfully install BusinessObjects for a single server configuration.

That said, there is ONE change I make every time I do an installation.

History of the Embedded Database

On Windows, it’s gone from SQL Server Embedded (CE10) to MySQL (for support of Unix and Linux) and back to SQL Server.  Now that SAP has acquired it’s own database technology, don’t be too surprised if it comes bundled with Sybase in the future.

Personally I’ve never liked uses the embedded database and I wouldn’t recommend you use it either.  In fact, I recently has a situation with a client who due to any overly restrictive server/firewall configuration was unable to get the embedded database working and we wasted hours trying to troubleshoot the problem.

Installation Best Practice

I always choose “Custom Install” so that I can:

• Modify the installation location
• Uncheck the default embedded database (for the CMS)

I really don’t like to include the embedded database because I want to give BusinessObjects as much on-server resources as possible – especially with v4.0.

Always create space in an existing database environment to support BusinessObjects.  There are many supported CMS databases including:  SQL Server, MySQL, IBM DB2, Oracle, MaxDB and Sybase.

NOTE:  Always test connectivity to the database from the server on
which you will be installing BusinessObjects to make sure the connectivity
is working.

During the installation you will want to NOT include the embedded database.  That means doing a CUSTOM install and deselecting Integrated Database.

De-Select the Integrated Database

What I love about the installer is that it will check the database connectivity before the installation begins.  If there is an issue with the database client configuration, permissions, etc., the installation will warn me of the situation and not continue.  This gives me the confidence to know that assuming I have enough hard drive space, when I select “Begin Installation”,  it will complete successfully.

«Good BI»

Say it loud, Say it proud

With the latest development of SAP Business Suite Enhancement Pak 5, SAP users can now enjoy the rich, flexible formatting functionality right from within the application.

ALVs Get An Upgrade

For many years, users have been limited in their ability to provide formatted reporting from an SAP ALV (ABAP List Viewer).  Users could do basic sorting, grouping and filter, but that was about it.  Now the output from an ALV can be pushed directly into a Crystal Report using the new Crystal Reports ALV Adapter.

This solution is currently available for ALV Grid (SAP GUI ALV) and for Web Dynpro ABAP ALV.  ALV List and ALV Classic are not supported.  In my case, I will be running an SAP GUI ALV.

Using the Crystal Reports ALV Adapter

Before installing the adapter you need to be running Microsoft .NET Framework 2.0 or above.  In addition, because I will be running ALVs run directly within the SAP GUI, I must have also installed SAP GUI 7.10 or above.

Downloading the Adapter

One of the hardest things about installing the adapter is finding in the Service Marketplace. Here is a screenshot of the location

Personally, I recommend searching for it.  Here is how I found it:

From the search screen, search for CR ADD-ON

Software Search Screen

Search for CR ADD-ON

You should see CR ADD-ON FOR BS APPS 1.0 come back in the search results.  This is what you want.

Search Results

Once you select CR ADD-ON FOR BS APPS 1.0, you will be able to download the ZIP archive file.

Service Marketplace Software Download

After extracting the application you will see the xSAPCRVAdpt.exe, which is what we will be installing.

Installing the Adapter

Begin the installation by double-clicking on the install file, xSAPCRVAdpt.exe.

Here we will see the Crystal Reports ALV Adapter ready to be installed.

After choosing Next, the installation will begin:

After a few minutes you should see the following message, which indicates the installation was successful.

Configuring the Adapter

The final step after installing the adapter is telling Business Suite to allow Crystal Reports to be used with ALVs.

We need to use tcode, SALV_GUI_CUST

Here we want to make sure we Allow Crystal Reports to be an option.

Next we need to use tcode, SALV_WD_CUST to go into the Web Dynpro Settings

Here we want to make sure we Allow Crystal Reports to be an option.

Testing the Adapter

You can use any ALV Grid (SAP GUI ALV) or Web Dynpro ABAP ALV for testing.  In my case, I’m going to use the t-code KSB1, Display Actual Cost Line Items for Cost Centers.

Note that at the end of the input screen, I select the layout for this report.  This is important because with Enhancement Pak 5, you can save Layouts that leverage Crystal Reports.

Here are the default results in an ALV Grid:

Pretty boring, eh?

Choose the Change Layout Icon

Under the View tab you can change the Preferred View to Crystal Reports.  This will cause the data to be sent to a Crystal Report using the SAP_GenericTemplate.rpt.

Now you can see the output in a Crystal Report.

Modifying the Crystal Report

We first need to get a copy of the report, so we will chose the option “Export Report”.

By selecting this option, I will be presented with a dialog box which will allow me to Save the Report to my hard drive.  At first, I found this button confusing because I expected it to work like it does in standard Crystal Reports and ask me if I wanted to export the report in PDF.  It left me wondering how I would go about exporting the results to PDF if I wanted to.  Hmm.

NOTE:  Once the report is exported, you can make changes to it using
Crystal Reports 2011, since at the time of this writing Crystal Reports
for Enterprise does not support direct data connectivity.  Also this template
was created in the pre-Crystal Reports 9 format; therefore you could modify
these reports with older versions of Crystal Reports.

When you open the report to modify it, you will see that the data is being pushed into the report via an ADO.NET (XML) database connector.  This means that this report cannot be refreshed from within Crystal Reports during the report modification process.

Rather than showing you the details about how to modify a Crystal Report, I will simply assume that after exporting the report, you have been able to make a number of changes to suit your needs and are ready to load those changes into the SAP List Viewer.

Select your update report from the dialog box:

After after being imported successfully:

… the new report layout will appear on the screen

Saving the Layout for Future Use

Now that we’ve got the new report loaded, we would like choose this view or layout.  SAP accommodates this.  All you need to do is save the layout under a new name by following the prompts in the Save Layout dialog:

In my case I named my new report layout /ZCRKSB1

After the layout has been saved, users can reference it from the Setting section of the original SAP List Viewer prompt screen.  Under the last section, Settings the user can change the default layout by selecting the layout of their choice.

Summary

For many years now, we’ve been talking to customers and partners about the value of embedded analytics.  Now we are finally beginning to see they rolled out in earnest.  Not only is Crystal Reports now embedded directly into SAP Business Suite, but SAP Dashboards (aka Xcelsius) are being provided out of the box for HR, Finance and other key areas.

The only drawback of embedded analytics today is the lack of built-in intelligence about how to navigate the data.  In the SAP List Viewer today, if you click on a column, the List Viewer is intelligent enough to drill to the associated supporting document.  But even with this limitation, there is still real value in better reporting from SAP Business Suite.

«Good BI»

Every once and a while I run across a reporting technique that makes me say:  Wow – that was really clever.

In going through the new charting in BusinessObjects v4.0, I noticed that there was no histogram available.  After doing a Google search I found a great article by Alastair Gulland.

Alastair uses  a combination of straight-forward formulas and out-of-the-box thinking.  A brilliant combination.  He also introduces a way of making the histogram dynamic by using a Input Control.  Very nice.  It’s definitely worth checking out.

You will find the full blog post here:
http://www.gulland.com/wp/?p=662

Click on the image to see the finished report with Histogram and Input Control

«Good BI»

Have you ever been in a situation where you wanted to display a date on a report but the date was in date/time format?

I run into this all the time when dealing with detailed data from transactions with date time stamps.

The best way I’ve found to do it is by converting the value from DateTime to Text and back to Date.

Convert Text to Date

Believe it or not, it’s not too bad after you figure it out.  It did take me a few tries so I thought I’d share my solution with you.

=ToDate(Left(UserResponse("As Of Date");
Pos(UserResponse("As Of Date");" ")-1);"M/d/yyyy")

Let’s break it down so we can understand the individual components.

In this case, the field I am formatting is a date/time prompt and I will reference it by the name “As Of Date”.

I will need to perform 3 steps in able to convert it:

1. Use the Pos function and find the space between the date & time, e.g. 1/1/2011 15:03:03 returns the position value of 9.
2. Next use the Left function and grab everything to the left of the space – 1.  e.g. 1/1/2011 15:03:03 returns the text “1/1/2011″.
3. ToDate now takes the date text and converts it to a date.  e.g. 1/1/2011 becomes a date type field 2011-01-01

Display As Text

Optionally now that I have the value in a date-field format, I can now convert it back to a formatted date using the FormatDate function.

=FormatDate(ToDate(Left(UserResponse("As Of Date");
Pos(UserResponse("As Of Date");" ")-1);"M/d/yyyy");"yyyy-MM")

What about you?

What’s your favorite WebI syntax for converting data types in WebI?

Do you have any conversions that are giving you trouble?

«Good BI»

A very common question when it comes to SAP BusinessObjects is:  How should I manage my SAP BusinessObjects security authorizations?

Although that is an extremely broad topic, today I want to discuss the issue of leveraging an external corporate directory to manage user authorizations.

NOTE:  It's important to remember that only the
user/group membership gets delegated out.
The group/object rights assignments must still be
performed by the BI administrator.

In most large organizations, there is a security team which manages users ids and user groups.  These users and groups are typically stored in a Corporate Directory such as LDAP, Windows Active Directory, etc.

The userid and password from the Corporate Directory is the method through which users are able to authenticate themselves (via secLDAP, secWinAD, secSAP, etc.)  So the question naturally arises:  Should I manage all my authorizations through groups managed via external security solutions?

The answer for your organization will depend on the answers to the following questions:

1. How much cooperation is there between the BI Administrator and the Corporate Directory Team?
2. How quickly can new groups be added to the Corporate Directory?
3. How many groups would you need to add to the Corporate Directory to manage all the BI security scenarios?
4. Do I want to delegate the bulk of my BI security management to the Corporate Directory Team?

There are pros and cons to any software implementation and the right solution must be made according to the culture and policies that fit your organization.

Leveraging External Security Exclusively

In certain cases the Corporate Directory team may be able to create and manage all the groups BI Administrators will need for security within the Corporate Directory and they are able to assign the correct security when a user is provisioned.

The advantages include:

• Single location for user/group membership
• Delegated Model – less for the BI administrator to manage

The disadvantages include:

• Delegating group creation and user group membership

This model has been successfully implemented at a large insurance company with 500+ BusinessObjects-related groups mapped to an external Active Directory server.

Leveraging A Mixed Model

At a high-level the mixed model leverages:

1. External security management for course grain security
2. Internal secEnterprise groups for fine grain security

I recommend course grain security to manage which users should have access to the BI environment (e.g. NY Users, Georgia Users, Reporting Users, etc.)  Once the user has been imported into the system, we can fine tune their access via fine grain security.

NOTE:  We refer to these as Data Access Roles because
the users in the Georgia Users group only get to see Georgia reports
and the corresponding Georgia data.

Administrators can use internal secEnterprise groups for managing fine grain security.  You can create roles such as:  WebI Viewer, WebI Developer, InfoView user, which can control the product specific rights allowed to the users of that role.

NOTE:  We refer to these as Entitlement Roles because
the users in the WebI Viewer group are allowed limited
application functionality.

The advantages include:

• A clean delineation between the Corporate Directory and the BI Security
• Balance between corporate control and system flexibility

The disadvantages include:

• Security being managed in two places
• The need for the BI Administrator to be notified of fine grain role changes so users can be remapped

It’s hard to say for sure whether the Mixed Model tends to work better in most organizations than the delegated model.  You need to determine which model will work best for your organization.  What I can say is that  both models allow for extremely flexible deployments amongst the multitude of different scenarios that I have come across.

Don’t Forget

Regardless of which model you use there is something you should know…

One organization which had communication problems with their LDAP server and as a result of a synchronization problem, most of their users were deleted from their BusinessObjects environment.  Users – Inboxes – Personal Folders.   Gone.

There is one extremely important rule when leveraging an external Corporate Directory.  Make sure that every external user in your BusinessObjects environment is mapped to at least one secEnterprise group.  This will guarantee the the mapped users id will never accidently be deleted from the system.  For Java SDK samples around user management, go here.

«Good BI»

Software companies are beginning to give all customers an equal voice and I love it.  SAP had launched Idea Place – a great place for you to share you best ideas directly with the product developers.

In the past SAP has used user groups, product councils, customer suggestions via technical support, surveys and other methods to gather feedback on how to make our products better, but now you can get involved and it’s easy.

SAP is asking you to join the party and submit your best ideas for innovations, feature enhancements and all around great stuff.  SAP wants you!

SAP Wants You!

Have you ever thought:

• Why doesn’t the software let me do this?
• This interface doesn’t make sense!
• Why hasn’t anyone thought of adding this feature

Well – instead of being frustrated by great ideas and nowhere to send them, now you can post them on Idea Place.

Idea Place

Here’s how it works:

1. Join the party at https://ideas.sap.com/
2. Find your favorite product
3. Look at the ideas that other customers have submitted
4. Comment on the ideas of others
5. Add your vote to your favorite ideas

Next thing you know, you’ll be saying, “Oh, that’s a great idea – why didn’t I think of that!”

What I also love about idea place is that you can see which feature suggestions have been rejected, which are still under consideration and finally, which have already made it into the product and when.

Submit Your Own

If you have a great idea of your own, simply add it!

This website is all about building a community of people with a passion to make SAP’s product better.  Please sign-on the Idea Place asap so you can contribute your ideas to make your job easier and make the product better.

Each year we make resolutions to improve our lives.  Help improve everyone’s life – share your ideas and let your voice be heard.

«Good BI»

Have you ever been using SAP BusinessObjects WebIntelligence and wanted to turn a prompt from a date time into a date?

I do a lot of demos using Microsoft SQL Server and by default Microsoft SQL Server saves everything as a date & time.  So what if I don’t want the time?  Simply convert the datetime into a date within the semantic layer.

Ignoring the time 12:00:00

There are many solutions, including many which rely on some form of string parsing, but why make it more complicated than it has to be?  This is what I like to use:

CONVERT(DATETIME, CONVERT(INT, GETDATE()))

This is the perfect solution if all the data is stored as 1/1/2010 12:00:00, wherein the time element is 0.

Ignoring time and rounding down

If indeed there is a time and it’s important to round down, e.g. 1/1/2010 11:59pm should be rounded down to 1/1/2010, then in this case I use:

CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

This is great when accessing call center data and I need to group calls around a specific day, but the time is still extremely relevant.

Anyone else have any commonly used tips or tricks within the semantic layer?  Post it in the comments below!

«Good BI»

ASUG is offering a complimentary membership for North American customers and partners of the SAP BusinessObjects solution portfolio who operate in a non-ERP data environment. Whether you used to be a member of Global BusinessObjects Network (GBN) or not, you can now apply for membership.

At first, I didn’t like the idea of SAP charging customer for a user group, but now that I’ve been able to explore all the vast resources available on the website and see how easy it is to connect with other SIGs and people in an industry, I see it as a truly invaluable resource.   There are regular webinars on every conceivable topic and all webinars are archived in case you miss them.

Complimentary ASUG Membership Process

Take immediate advantage of your complimentary ASUG membership:

• Click here to get the application started
  
• Search for your company and complete an individual login application. If you do not see your company name just complete a corporate application for your organization to receive the complimentary  membership here.
• If you are an SAP BusinessObjects partner, you will need to contact ASUG here and they will assist you through the application process.

Are You An Influencer?

View this special video invitation from ASUG CEO, Bridgette Chambers, to the inaugural ASUG Influencer Summit for SAP BusinessObjects customers. Nominate yourself or another Influencer in the community and join us to help shape the future direction of the communities that support SAP BusinessObjects customers. Nominations will be accepted through September 15, 2010. The Summit will be held on Monday, Oct. 4 from 1-6 p.m. at the Walt Disney World Swan and Dolphin Resort in Orlando, the day before the SAP BusinessObjects User Conference starts.

ASUG Members Get User Conference Discount

After you join ASUG, you can also get additional discounts on the SAP BusinessObjects User Conference.   View the agenda here.  It’s full on BusinessObjects from start to finish.

«Good BI»

I was recently working with a universe and attempting to calculate a ratio between two numbers and coming up with some strange results.  I wanted to share this with you so you didn’t waste as much time as I did trying to solve the problem.

Always Returning Zero

In my example, I had two measures and i wanted to calculate the ratio between this.  I was working on a call center universe and I needed to calculate First Call Resolution Ratio.  That would be First Calls/Total Calls.  The problem was that my calculation was always returning 0.  Here is what the SQL looked like:

select count(dbo.Current_Facts.first_call) /count(dbo.Current_Facts.call_id)
from dbo.Current_Facts

There is nothing wrong with the SQL.  The problem was with the data type.  In my case, both the numerator and denominator are integers, therefore the resulting value is also an integer.  Since there are always less first calls than total calls, the values would be a fraction and therefore were truncated to 0.

An Easy Fix

Once you realize the problem, the fix is simple.  Simply modify the numerator to be a float value.  This will cause the calculate to result in a float value.  In my case I changed the first_call value as casted it as a float so my SQL came out like this:

select cast(count(dbo.Current_Facts.first_call) as float) /count(dbo.Current_Facts.call_id)
from dbo.Current_Facts

Although this should fix the problem, there are probably databases that may require you to be more specific in which case you might modify the SQL to look like this:

select cast(cast(count(dbo.Current_Facts.first_call) as float)/cast(count(distinct dbo.Current_Facts.call_id) as float) as float)
from dbo.Current_Facts

Hopefully this little tip will safe you tons of time spent scratching your head.

«Good BI»

If you need to troubleshoot Xcelsius 2008 runtime messages here is another link you might want to check out. I recently ran across an error message “2170″ but was not able to find it on my original list of error messages, but then I finally found it.

Click Here for the description of the runtime message.

The solution to the 2170 message when using Xcelsius was provided via YOUR posted comments to my blog.  Thank you!!

Most of the time, the issue has to do with the crossdomain.xml file.

<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
 <allow-http-request-headers-from domain="*" headers="*" secure="false" />
 <allow-access-from domain="*" secure="false" />
</cross-domain-policy>

For more information about installing the crossdomain.xml file, read my previous article.  If you are STILL having problems check out these two posts.

Check out the answer on the BusinessObjects forums :

https://forums.sdn.sap.com/thread.jspa?messageID=5656068#56560685656068

There is also additional information in the BOB forums:
http://www.forumtopics.com/busobj/viewtopic.php?t=110827