You can combine Xcelsius visualization with a report created in Crystal Reports to take advantage of the summarized data in Xcelsius and the detailed reporting in Crystal Reports. I will show you how to enhance an Xcelsius dashboard so that the user can view aggregated data in the dashboard. The user can then navigate from the dashboard to a detailed Crystal Reports object, which retrieves the data from a DataStore object (DSO) layer.

In "Give Managers Detailed, Visual Dashboards for Improved Decision-Making," I created an Xcelsius dashboard that shows the open orders value broken down by country. The user can select the time frame for the shown data. For the Crystal Reports object, you use the DSO layer and show the open order value by region and material. In addition, you can filter the data by country and a time range, whereby the selection values are transferred to the Crystal Reports object from the Xcelsius dashboard each time the user navigates to the detailed report.

For the Crystal Reports object, I’m using the SAP NetWeaver BW query shown in Figure 1.

Figure 1

SAP NetWeaver BW query for the Crystal Reports object

The query shows the Open orders value with the characteristics Region and Material. The query also has an interval variable for the Calendar Year characteristic and a single-value variable for the Country characteristic. By using variables, you ensure that the selections passed from the Xcelsius dashboard to the Crystal Reports object are processed on the SAP NetWeaver BW back end and not from the Crystal Reports client or server-side. This guarantees that the processing occurs on the SAP server and not on the much smaller client hardware.

Before you link the Crystal Reports object to the Xcelsius dashboard, you first need to create the report. The following steps do not outline every step to create the Crystal Reports object. If you are new to using Crystal Reports on top of your SAP system, you can find more details in the article “ SAP NetWeaver BI and BusinessObjects: Integrate Crystal Reports with SAP NetWeaver BI for Enhanced Report Formatting.”

Start the Crystal Reports Designer and click the create icon to create a new report on top of the SAP NetWeaver BW query that contains the data from the DSO layer. Add the description from the Material characteristic and the two key figures — Open orders and Open orders Qty — to the detail section of your report.

Create a group based on the Region characteristic by going to Insert > Group. Use the key field from the Region characteristic as the field for grouping and use the description from the Region characteristic as the information to be shown in the report.

Next, create a subtotal for both key figures for the group you just created. By now your report should look similar to Figure 2.

Figure 2

Crystal Reports preview

Now move the subtotals from your group footer into the group header by navigating to the Design tab of your report. Select the summary fields in the group footer and drag and drop them to the group header. Right-click the Details section and select the menu item Hide (Drill Down OK) from the context menu. This enables the report to show a summary view and allows you to drill down to the details. Then go to Insert > Chart and add a pie chart in the report header. This visualization shows the open orders value by country.

Click the Design tab in the upper left corner of the Crystal Reports screen in Figure 2. Right-click Group Header #1 and select the option Insert Section below. This adds another empty section to the Group Header so you can separate the chart that you added to the group header from your existing group header. You can then format these two group headers independent of each other. In the new Group Header #1b section, add a chart that shows the open order value by material.

When you preview the report now, it shows both charts — Open Order Value by Material and Open Order Value by Region. However, to show the detailed chart in the report after the user has drilled down on the Region characteristic, you need to hide the newly added section Group Header #1b, in which no drill-down menu exists.

To hide the new section, go to Report > Section Expert (Figure 3). In the Section Expert screen, select the section Group Header #1b. Click the formula icon next to Suppress (No Drill-Down). Enter the formula DrillDownGroupLevel=0. This formula hides the Group Header #1b when no group drill-down exists.

Figure 3

Enter the formula to suppress Group Header #1b

By now you should have a report that in the initial view presents the open orders per region (Figure 4). The report should also show the open orders value per material as a chart and table when the user drills down on the region level (Figure 5).

Figure 4

Report of open orders per region

Figure 5

Report of open orders by material

Next, you need to link the Crystal Reports object to the Xcelsius dashboard and hand over the context from the Xcelsius dashboard. For this, I open the Xcelsius dashboard that I created in the previous article (Figure 6).

Figure 6

Open orders dashboard in Xcelsius Designer

To open the Crystal Reports object from the Xcelsius dashboard, use the standard openDocument application, which allows you to pass several URL parameters to the report, including parameter values.

Before you include the navigation to the Crystal Reports object in the Xcelsius dashboard, you need the following information:

• The Crystal Report ID from the SAP BusinessObjects Enterprise server to uniquely identify the report
  
  
• The technical names of the parameters to which you want to send the values
  
  
• The user-selected Country characteristic value from the Xcelsius dashboard

First, go to the Central Management Console (CMC) to find the Crystal Reports ID. Navigate to the folder that contains your Crystal Reports object, select the Crystal Reports object, right-click and select the menu item Properties (Figure 7). In my example, the ID is 7311.

Figure 7

CMC Crystal Reports object properties

Second, find the technical names of the parameters in the Crystal Report by opening the report in Crystal Reports Designer. Open the Field Explorer with the menu View > Field Explorer and look at the parameters in the Field Explorer (Figure 8). In my example, the technical names of the parameters are: [0D_COUNT] and [0I_CALYE].

Figure 8

Find the technical names of the parameters under Parameter Fields

You can store the Country value that the user selects in the Xcelsius dashboard in a cell on the spreadsheet that Xcelsius uses. This integrates the Country value into the overall URL that you use to call the Crystal Reports object, as I show later in the article. In Xcelsius Designer, select the chart that you created in "Give Managers Detailed, Visual Dashboards for Improved Decision-Making," right-click, and select Properties. Click the Drill Down button and select Enable Drill Down (Figure 9).

Figure 9

Enter the drill-down properties

You have several options for the Insertion Type. Select the Row option, which allows you to define the source data area — in my example, the country keys and descriptions in the spreadsheet columns A and B.

Define the Destination for the selected value. From the spreadsheet, select cells G1 to H1 for the open order quantity series and G2 to H2 for the open orders series. (You select two cells as the destination cells because you selected the key and the description as the source data.)

Now you need to construct the URL to call the report. The standard openDocument URL syntax to call an object from the BusinessObjects Enterprise system is:

https://<server
name>:<port>/OpenDocument/opendoc/OpenDocument.jsp?iDocID=<Crystal
Report ID>

&lsS<parameter name>=<parameter value>

&lsR<parameter name>=[<start value>..<end value>]

In addition to calling the actual object, you need to ensure that you pass the selected values from the Xcelsius dashboard to the Crystal Reports object. My dashboard has a single value for the country characteristic and a range value for the calendar year to pass to the Crystal Reports object. The syntax for a single value as URL parameter is:

&lsS<parameter name>=<parameter value>

and the syntax for a range value is:

&lsR<parameter name>=[<start value>..<end value>]

For my example, the standard URL looks similar to the following:

https://vmwsap16.wdf.sap.corp:8080/OpenDocument/opendoc/OpenDocument.jsp?iD
ocID=7311
&lsS[0D_COUNT]=CA
&lsR[0I_CALYE]=[2002..2009]

In the example above CA and 2002..2009 are example key values for the variables and parameters; vmwsap16.wdf.sap.corp represents my server name and 8080 the port.

To construct the URL, you need to combine several values from your spreadsheet by using standard Excel formulas. In cell I1, enter the following syntax:

=”https://vmwsap16.wdf.sap.corp:8080/OpenDocument/opendoc/OpenDocument.jsp
?iDocID=7311&lsS[0D_COUNT]=”&G1&”&lsR[0I_CALYE]=[“&F2&”..”&F3&”]”

Keep in mind that you need to replace vmwsap16.wdf.sap.corp:8080 with your own server name and port.

Here are the definitions I used in the example above:

• Cell G1 is the destination cell for the drill-down menu from the chart
  
  
• Cell F2 holds the starting value for the range for the year variable
  
  
• Cell F3 holds the end value for the range for the year variable

After you add the URL syntax to your spreadsheet, you can now add a URL button component to your Xcelsius canvas. You can find the URL button in the Components tree under Web Connectivity on the left side of the screen (Figure 10). Right-click the URL button and select Properties from the context menu. Click the selection icon for the URL field to select the URL syntax in cell I1.

Figure 10

Add a URL button to the Xcelsius canvas

Before you publish the Xcelsius dashboard and use the navigation from the Xcelsius dashboard to the Crystal Reports document, you need to make some changes to the default settings for the openDocument application. For the Java version of openDocument (Windows environment), you need to edit the file web.xml, which is located (assuming Tomcat as the application server) in the directory Program FilesBusiness ObjectsTomcat55webappsOpenDocumentWEB-INF.

For the .NET version of openDocument you need to edit the file web.config, which is located in the directory Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0Web ContentInfoViewAppOpenDocument. You need to change the parameter values according to Table 1.

In addition to the changes in Table 1, you should also make sure the Default System in the Options tab of the SAP authentication is set to your SAP system. You can access the Options by logging on to the CMC and navigating to the Authentication Area. You use the default system to validate a logon token if an SAP system is not explicitly specified in the URL or as part of the iView you are using in an SAP NetWeaver Portal environment.

Now you can export your Xcelsius model by going to File > Export > BusinessObjects platform to your SAP BusinessObjects Enterprise system. After you log on to InfoView with your SAP credentials, you can view your Xcelsius dashboard and click any of the bars representing the data per country (I chose Australia in Figure 11). Click the URL button to navigate to the detailed Crystal Reports objects for Australia (Figure 12).

Figure 11

Xcelsius dashboard in InfoView

Figure 12

Crystal Reports objects shown in InfoView

In addition, the report also allows you to drill down even further to show the details per region — in my example, New South Wales (Figure 13).

Figure 13

Crystal Reports with drill-down objects shown in InfoView

Ingo Hilgefort

Ingo Hilgefort started his career in 1999 with Seagate Software/Crystal Decisions as a trainer and consultant. He moved to Walldorf for Crystal Decisions at the end of 2000, and worked with the SAP NetWeaver BW development team integrating Crystal Reports with SAP NetWeaver BW. He then relocated to Vancouver in 2004, and worked as a product manager/program manager (in engineering) on the integration of BusinessObjects products with SAP products. Ingo's focus is now on the integration of the SAP BusinessObjects BI suite with  SAP landscapes, such as SAP BW and SAP BW on SAP HANA, focusing on end-to-end integration scenarios. In addition to his experience as a product manager and in his engineering roles, Ingo has been involved in architecting and delivering deployments of SAP BusinessObjects software in combination with SAP software for a number of global customers, and has been recognized by the SAP Community as an SAP Mentor for SAP BusinessObjects- and SAP integration-related topics. Currently, Ingo is the Vice President of Product Management and Product Strategy at Visual BI Solutions, working on extensions to SAP’s product offering such as SAP BusinessObjects Design Studio and SAP Lumira. You may follow him on Twitter at @ihilgefort.

You may contact the author at Ingo@visualbi.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.