Hierarchies can be activated for characteristics in the BEx Query Designer and offer the user the capability to navigate along a hierarchical structure. In Crystal Reports when you use the MDX Query driver hierarchies are fields representing a parent-child relationship. This parent-child relationship allows you use the hierarhically grouping features in Crystal Reports. In this way the hierarchy that exists in the data is reflected in the report.
In this article you will find how to design Crystal Reports using BW hierarchies, including enhancing drill down capabilites trough the hierarchical structure.
This example is using a BEx Query based on the InfoProvider SAP Demo Sales and Distribution Overview. The hierarchy for Sales Organization has been activated in the query, this will be leveraged in Crystal Reports as a parent-child relationship so you can create a hierarchical group.
Before you create your report, there are a couple of settings to configure in Crystal Reports
- In Crystal Reports on the SAP menu, click Settings
- Select "MDX driver with support for multiple structures". This is so you use the recommended driver for BW reporting of BEx Queries.
- On the File menu, click Options
- Click on the Database tab and in Table and fields select Show description. With this option you will see the long descriptions rather than technical names.
In Crystal Reports you have the option to group data in a report to show hierarchical relationships. This the option that you can use to leverage the hierarchies from BW. To display a hierarchy relationship in Crystal Reports you have to create a group based on the data that contains the relationship and then you can use the Hierarchically grouping options to configure the hierarchy.
- Create the report by clicking on the New report from Query icon in the SAP toolbar.
- Select the SAP system and enter your SAP userid and password
- Select the query
- The hierarchy activated in BEx query will appear as a parent-child relationship field in Crystal Reports. You will see a NodeId (this is the child and a ParentNode Id for the hierarchy.
- On the Insert menu click Group to insert a group. Select the NodeId of the hierarchy for the group.
- Click on the Options tab and select "Customize group name field".
- Select the Name of the Sales organization charactertistic and click OK.
- The group is created, now on the Report menu click Hierarchical Grouping Options. Select the Sort Data Hierarchically option. In the ParentID select the ParentNode Id field (in this example Sales organization Parent Node ID)
- Enter a value in Group indent (for example .15). This is to specify how much indentation you wish to have between the hierarchies. Click OK.
- Preview the report to see the hierarchy
- Now you are going to add a keyfigure into the details section. In this example Incomming orders value keyfigure.
- Insert a summary. Right click on the keyfigure you just added. In the Insert Summary window select Sum as the calculation and select Group#1 for the Summary location.
The option Summarize across hierarchy is selected when calculating summaries in hierarchical groups. But when reporting and summarizing hiearchies from BW this option is not selected as the underlying SAP BW query returns the aggregated values for all hierarchy nodes to Crystal Reports.
- Move the summary to the Group Header 1.
- Select Supress(No DrillDown) for Details and Group Footer sections. Preview your report
The Group indent setting in the Hierarchical Grouping options dialog, not only affects the hierarchy but all other fields that are in the same section as the hierarchy. Crystal Reports provides some formula functions that are helpful with hierarchical grouping like the GroupingLevel and HierarchyLevel functions which can be used to determine where, within the hierarchy a particular group resides (level1, level2, level3, etc.). Using these two hierarchy formula functions in a conditional formula, you can change the horizontal position of certain fields according to their hierarchy. This gives you more granular formatting control than that provided by the Group Indent setting.
- In the Report menu select Hierarchical Grouping Options,set Group Indent to 0.
- Right click the Group Header field and select Size and Position
- Click the Conditional Formula button beside the X position value field.
- This is the formula you will use
- In this example the formula looks like:
- Preview the report, now the group headers appear indentend, but the other fields in the same section appear in their original position.
If you try drill down in this example report you will notice that the resulting drill down page will have all the levels rather than only the level below the current level where you drill down. This is because the hierarchical grouping feature does not behave like a regular grouping. To have several levels with regular grouping, you have several groups. With hierarchically grouping you have only group and Crystal Reports is sorting the data hierarchillay based on the parent child relationship, so when you drill down you will always drill down to all levels because it is only one group.
In this section you will find how to use conditional formating to supress the group header, so you have a report that it first displays Level 1 of the hierarchy, then when you drill down on Level 1 you will get only Level 2 of the hierarchy and so forth, getting only one level at a time.
You have to create 3 formulas that will help to decide when a group should be suppresed:
|IsDrill||This is to check if report is in drill down. DrillDownGroupLevel function returns a number that indicates the group level of the current drill down view, or 0 if it is not a drill down view.|
|LevelNum||This formula is to determine on which level of the hierarchy is the current group. For this you can use the GroupingLevel and HierarchyLevel functions.|
|UptoLevel||This formula is used to store the value of the hierarchy levels that you want see in the report. You need to know the hierarchy level of group at the top of the page (the first group), because when you drill down you want to see the group where you did drill down and all groups on the next level.(but not other levels). The GroupNumber function returns the current group number and when you drill down the group at the top of the page is GroupNumber 1. This means that when GroupNumber is 1 you can get the hierarchy level of this group and then store that hierarchical level. You know that you want to see current level and level+1 groups, and supress anything that is greater than level+1|
Here is an example of the result of the formulas before you drill down
When you drill down o a group on Level 2 you get all the levels, but once you add the conditional formating using this formulas you will only get the group where you drill down and grpups on the next level.
Now you can create the conditional formating on Group Header 1 that will use the formulas you just created.
- Right click on Group Header #1 and select Section Expert.
- In Section Expert click on the conditional formula next to Supress(No DrillDown)
- Enter the following formula and click Save and Close:
What is this formula doing?
This formula will supress the current group when the report is in drill view and the level of the current group is greater than the level you want to see or when you are not in drill view supress all the groups that are greater than level 1.
When you preview your report you should only get groups that are on level 1, if you drill down on one of the groups you will only get the groups on level 2 and you can continue drilling down trough the hierarchy.
After you drill down