Many people who currently have SAP NetWeaver BI and BusinessObjects software, such as Crystal Reports and Xcelsius, ask how they can use these applications today to create optimized reports. In a series of articles, I will explain some of the major capabilities that are currently available for companies that have SAP NetWeaver BI and BusinessObjects. This article focuses on Crystal Reports. When you look at the integrated BI roadmap between SAP and Business Objects, an SAP company, you will see that Crystal Reports becomes the leading tool for enterprise reporting as part of your SAP landscape. Going forward, it will replace BEx Report Designer but more importantly, you can use Crystal Reports on top of SAP NetWeaver BI and SAP ERP now. I will explain which types of data connectivity are available today and what this connectivity looks like in a real-use case. Upcoming articles will focus on Web Intelligence, Xcelsius, and Polestar. Discussion of the details of the combined roadmap of SAP and Business Objects is outside the scope of this article. However, I will point out important aspects of the overall strategy going forward when necessary. (For more information about the roadmap, refer to Davin Wilfrid's article, “Get Smart: What You Need to Know About SAP and Business Objects BI Tools”.)

Technical Prerequisites

The following represents a list of technical prerequisites for Crystal Reports if you want to report on top of SAP NetWeaver BI or SAP ERP:

• You must install Crystal Reports Designer on the client
• You must install the client components for the BusinessObjects Integration Kit for SAP Solutions
• You must install an SAP front end with the SAP NetWeaver BI 7.X Add-On and the SAP BW 3.5 Add-On components on the client when using the connectivity on top of SAP NetWeaver BI
• Users need to have the necessary authorizations in the SAP system. For SAP NetWeaver BI, this means that users require the authorizations to view and execute BI queries. There are additional authorizations for SAP ERP access in authorizations class ZSSI that are specific to the connectivity that BusinessObjects provides.
• You must release the BI queries that you want to use with Crystal Reports for external access. You can set this property in the BI Query Designer.

Data Connectivity

Figure 1 provides an overview of the data connectivity among Crystal Reports, SAP NetWeaver BI, and SAP ERP. On the SAP NetWeaver BI side, the BW MDX connectivity is an enhanced version of the BI query connectivity. The major differences from the BW query connectivity to the BW MDX connectivity is the additional support for free characteristics. BW MDX connectivity also offers support for SAP NetWeaver BI queries with multiple structures. The BW MDX connectivity is based on a custom integration with SAP NetWeaver BI and requires that you import ABAP transports into your SAP system to use it.   From a technical stance, the Crystal Reports connectivity uses Remote Function Calls (RFCs) to communicate with SAP NetWeaver BI. For the most part, Crystal Reports uses the OLAP BAPI interface for this connectivity, but there are also custom elements included in the ABAP transports to take advantage of the full capabilities and metadata inside Crystal Reports. The DataStore object (DSO) connectivity uses BAPI functions from SAP NetWeaver BI. This connectivity requires that you import the ABAP transports delivered as part of the BusinessObjects Integration Kit for SAP Solutions. The benefit of this connectivity over the BW MDX connectivity is a direct path to the DSO layer for a more operational reporting approach, without needing to build a BI query. For your SAP ERP system you can apply the connectivity towards transparent, pool, and cluster tables, as well as ABAP functions, data clusters, classic InfoSets, and InfoSet queries with Crystal Reports.

BI Query vs. BI InfoCube

As Figure 1 shows, the BW MDX connects to BI queries and BI InfoProviders directly. When connecting directly to the InfoCube level in an SAP NetWeaver BI system, you can use InfoCubes and MultiProviders. Often organizations ask why they should use the BI query as a starting point for their reporting needs. Table 1 provides an overview on the supported elements for the current BW MDX connectivity to Crystal Reports when connecting to BI queries or InfoCubes and MultiProviders.

SAP NetWeaver BI metadata SAP OLAP BAPI support level Characteristics (including time and unit) InfoCube and BI query Hierarchies InfoCube and BI query Basic key figures InfoCube and BI query Navigational attributes InfoCube and BI query Display attributes InfoCube and BI query Calculated key figures and formulas BI query only Restricted key figures BI query only Custom structures BI query only Variables BI query only

Table 1

Supported metadata elements

Mapping BI Metadata to Crystal Reports

In Table 2 you can see how you can map the BI metadata to the elements in Crystal Reports, assuming that you are following the recommended approach of using the BW MDX connectivity on top of BI queries.

SAP NetWeaver BI metadata Crystal Reports element Characteristic Each characteristic results in separate fields for the key and description value Hierarchy A hierarchy is being mapped to a parent-child relationship represented by a parent ID and a child ID Key figure Each key figure in Crystal Reports has three fields: a numeric value, a unit field, and a formatted value field. The formatted value represents the field as string value formatted based on the user preferences inside SAP NetWeaver BI. Calculated key figure A calculated key figure is treated like a key figure. The report designer does not have access to the underlying calculation in Crystal Reports Designer. Restricted key figure A restricted key figure is treated like a key figure. The report designer does not have access to the underlying restricted key figure definition in Crystal Reports Designer. Filter If you apply a filter in the BI query to limit the overall result set, the filter is not visible in Crystal Reports Designer Navigational attribute A navigational attribute is treated like any other characteristic Display attribute Each display attribute has up to two fields in Crystal Reports Designer (key and description). The display attributes are shown as subordinates in the field explorer. SAP variables Each SAP variables that is marked as “Ready for input” turns into a parameter field in Crystal Reports

Table 2

BI metadata mapping for Crystal Reports

I will use an example to show this metadata mapping more concretely. The BI query shown in Figure 2 contains the following elements:

• The characteristic distribution channel as a free characteristic
• The characteristics calendar year/month, material, and sold-to-party as rows
• The key figures open orders and open orders quantity as key figures

  When I build a report in Crystal Reports on top of this simple BI query, I see a Field Explorer listing in Crystal Reports similar to the one shown in Figure 3. The Field Explorer shows all the data elements available for reporting. The two key figures (open orders and open orders quantity) are represented with three fields (value, formatted value, and unit). Each characteristic is represented with a key and description. In my example, these are Material, Distribution Channel, Sold-to-Party, and Calendar Year/Month. Each characteristic can have the display attributes — in my example, these are Material group, Gross weight, and Weight unit.  

Report Example in SAP NetWeaver BI

When you create a new report object with Crystal Reports Designer on top of SAP NetWeaver BI you have two options to start the actual process. One option is to use the SAP toolbar that appears in Crystal Reports Designer after you install the client components of the BusinessObjects Integration Kit for SAP Solutions. Another option is to use the standard menu path File > New > Standard Report and select the SAP BW MDX connectivity as your data source. I recommend that you select the SAP toolbar for the following reasons:

• You can use SAP-specific menus, such as selecting a BI query from the role menu
• You can configure a Crystal Reports object as a template (via the menu path SAP > Settings) that you can use when creating a new report
• You receive a list of generated values for the parameters that the system creates based on the SAP variables in the BI query
• You can open and save Crystal Reports content directly from and to the SAP NetWeaver BI Repository

For this report I will use a BI query based on the InfoCube 0D_DX_C01 (Demo Scenario: DalSegno Company) with the following characteristics:

• Calendar year
• Calendar year/month
• Product group
• Product
• Distribution channel

The query also has the key figures billed quantity and net sales. Step 1. In the SAP menu, select Settings and then select the Use MDX driver with support for Multiple structures option (Figure 4). This enables the SAP toolbar to use the more enhanced BW MDX connectivity instead of the older BI query connectivity.   Step 2. Go to File > Options > Database. Set the option Show Description (Figure 5). This setting allows you to use the characteristic and key figure descriptions instead of the technical names.   Step 3. Select the menu item Create new report from a query from the SAP menu. The system brings up a list of SAP systems from which you can select your system. You then need to provide your SAP credentials. After a successful authentication, the system brings up the typical UI for selecting a BI query. Select your BI query from the roles or from the InfoArea (Figure 6).   Step 4. Identify the elements from your BI query. After you select your BI query, you see an empty report in Crystal Reports. Here you can view the elements from your BI query in the Field Explorer (Figure 7).   Step 5. Place the characteristics Product group and Product and the key figure Net sales into the Detail section of the design view (Figure 8). At this point I preview the report to see what it will look like. My BI query contains a variable, so before I can preview my report, Crystal Reports prompts me to select the proper values for the Product group and Product (Figure 9).     Crystal Reports offers the option Set to Null for this example, which means that this variable in the BI query is optional. I opt to use the Set to Null option so that Crystal Reports returns all the values for the dataset (Figure 10). When I preview my report now, I see it organized by product group and product. However, I would like to view the products grouped by distribution channel, so I continue formatting the report.   Step 6. Go to Insert > Group. I select Distribution channel as the grouping field and choose to have the system sort the data in ascending order (Figure 11). Click OK.   Next, I want to update how the net sales appear in the report so that the reports show the net sales for each product group. I go to the Net Sales key figure in my report and open the context menu by right-clicking on the field. I select the option Insert > Summary and select Sum for Group #1, which is the group I created previously (Figure 12). I then select Insert > Chart and place the chart in the Report Header. Crystal Reports creates a chart automatically based on the grouping and the summary (Figure 13).     At this point I save my report. Click Save on the SAP toolbar (or use SAP > Save) to save the report into the SAP NetWeaver BI Repository. Select a role and provide a name for the report. After providing the name, the options dialog screen comes up (Figure 14). Here I can decide to prepare the report for translation, which causes the system to extract all the translation-relevant strings. I can translate the strings in transaction SE63 so that Crystal Reports uses the translated strings during the publishing process. In addition, I can decide to publish my report to the BusinessObjects Enterprise system so that I can share my report with all my SAP users.  

Create a Report Based on a BI Query with a Hierarchy

Most likely, you are using hierarchies that you created as part of your InfoCube in your reports. An example of this is a financial report that includes a cost center or a profit center hierarchy. I will explain how you can use these hierarchies for your report design in Crystal Reports. For this example, I am using a query on top of InfoCube 0D_DX_C01 with the customer characteristic, an activated hierarchy, and the key figures billed quantity and net sales. Step 1. In the SAP toolbar, select Create new report from a query. The SAP System dialog appears. Select the system and enter your SAP credentials and password. Step 2. Select the BI query. Crystal Reports creates an empty report for you. Step 3. Open the Field Explorer in Crystal Reports (Figure 15). The activated hierarchy from the underlying BI query results in a parent-child relationship in Crystal Reports.   Select the menu Insert > Group. In the Common tab, select the node ID (in this example, Customer Number Node ID) as the field by which to group the records (Figure 16). Then navigate to the Options tab and select the Customize Group Name Field option (Figure 17). Here you can set the characteristic description value to be the hierarchy description (in my example, the Medium Name of the customer hierarchy).     Step 4. Go to Report > Hierarchical Grouping options and activate the option Sort Data Hierarchically for your existing group. For the Parent ID Field select the Parent Node ID field of the Customer Hierarchy and set the Group Indent to 0.2 inches (Figure 18).   Step 5. Now I take another look at my report in progress. Select View > Print Preview. I can now view the hierarchical structure, but so far the report does not have any key figures (Figure 19). To add the key figures, navigate to the Design tab and add a key figure to the details section of the report. Right-click the added key figure and select Insert > Summary. Select Sum for the calculation option and select the previously created group as the summary location (Figure 20).     Step 6. Move your summary to the Group Header #1 and hide the Group Footer #1 in the report design (right-click the section and select Hide). Now preview the report via menu path View > Print Preview (Figure 21).   At this point, the order amount values appear, but they are indented. Crystal Reports Designer offers some additional formulas and functions that allow the user to create a better report design and provide conditional formatting even in the context of a hierarchy. The first formula for hierarchical reporting is the formula HierarchyLevel(), which retrieves the level of the hierarchical grouping. The formula needs a GroupLevel as an input. The following is an example for the formula syntax assuming Customer Node ID is the grouping field in the report:

HierarchyLevel ( GroupingLevel ( {Customer Node ID}) )

The second formula is CountHierarchicalChildren() , which counts the number of children in a hierarchy level. The formula needs a GroupLevel as input. The following is an example for the formula syntax assuming Country Node ID is the grouping field in the report:

CountHierarchicalChildren (GroupingLevel ({Country Node ID}) )

The count of children refers to the number of children in the hierarchy level. With these formulas, you can format the report so that the group headings follow an indentation and the key figures stay aligned. To change the layout, navigate to the Design tab of your report and select Report > Hierarchical Grouping Options. In the screen that appears, set the option Group Indent to 0. Then right-click the Group #1 Name field and enter the size and position (Figure 22).   Next, click the conditional formatting icon for the x-axis value. Figure 23 shows the screen that appears. Enter the following formula:

(HierarchyLevel (GroupingLevel ({Hierarchy Node ID} ) ) ) *150

  This code creates a conditional formatting for your group heading. The number 150 is the amount of pixels multiplied by the hierarchy level. The result is used to move your group heading on the x-axis. Save the formula and select View > Print Preview (Figure 24). Now my report appears as I'd like it to appear.