In case you missed the first two parts of my blog series I have included the links to them here:
In this blog I will explain how Crystal Reports is able to leverage hierarchies from a SAP BW query and in particular I will also show a time-dependent hierarchy and the impact of a keydate variable for Crystal Reports.
The query that I will use contains a characteristic Product with an activated hierarchy in the rows and several keyfigures in the columns. In addition the query contains a Keydate variable for the Product Hierarchy that allows the user to select a specific date for the time-dependent product hierarchy.
When I create a new report using the Crystal Reports designer based on the above query I receive the following meta-data from the BW query shown in the Field Explorer:
In the picture the characteristic Product is shown with a Product Node ID and a Product Parent Node ID. These two fields return the hierarchy information in form of a parent-child relationship to Crystal Reports.
To leverage this parent-child relationship in Crystal Reports I am creating in the first step a new group in my report (Menu Insert > Group) and select the Product Node ID as the grouping field.
In the second step I need to configure the group to become a hierarchical group.
I select the menu Report > Hierarchical Grouping Optionsand select the Product Parent Node ID as the parent for the just created group.
I configure the Group Indent to be 0.2 inches and preview my report.
Based on the Keydate Variable in the underlying BW query, a Crystal Reports parameter is coming up and I need to select a date for my report. I enter the current date and continue to preview my report.
The image below shows the grouping in my report on page one (right hand side of the image) and the navigation tree in Crystal Reports for all created groups (left hand side of the image).
Based on the parent-child relationship Crystal Reports resolved dynamically the hierarchy and created a report for me that allows me to navigate along the hierarchy from SAP BW.
As the next step I can add now the keyfigures.
I am going back to the design of my report and add the keyfigure Order Amount into the detail section.
Now I select the Order Amount and select the menu Insert > Summary from the context menu.
Based on the hierarchical grouping, Crystal Reports Designer is offering me the option to summarize the Order Amount across the hierarchy.
This option is important in cases where the hierarchy nodes are not containing already the summaries.
In my case the BW query did already return all the aggregated totals to Crystal Reports, so I leave the option unchecked and include my new summary into the report.
This results in a very simple report using a hierarchy from the underlying BW query and creating dynamically a grouping in my report.
Now I refresh my report and I am being asked to provide a value for the keydate variable.
In the initial run I enter the value Feb 02, 2008 and receive the result as shown in the image above.
Now I refresh the report again and enter the value Feb 02, 2004 and receive the following:
In the second refresh the country FRANCE is shown to be part of Northern Europe and in the initial run the country FRANCE was part of the hierarchy node for Southern Europe.
This shows that Crystal Reports is also able to leverage the keydate variables from a BW query and resolve the time dependency of the hierarchy.
I hope this quick description is giving everyone a good idea how easy it is to create a report in Crystal Reports and leverage hierarchies – including time dependent hierarchies – inside the report.