Crystal Reports and SAP BW Hierarchies - Part 2 of 2

[IngoH]

In the Crystal Reports and SAP BW Hierarchies - Part 1 of 2of this two part blog we created a Crystal Report on top of a BW hierarchy. In the second part we will take the report and enhance it so that the user can use a drill down along the hierarchical structure

 

Before we are going into the creation of the formulas here the logic:

• I want to offer the user the capability to select the initial drill level for the hierarchy

           >>> This means I need a parameter in Crystal Reports  

• After that selection the user should be able to use the standard drill down capability.

You might ask yourself why that is worth a blog ? Simply because the hierarchical grouping option in Crystal Reports is not a real "grouping" in that way that Crystal Reports creates several groups (one for each hierarchy level); Crystal Reports creates one group and "sorts" the data in a hierarchical manner based on a parent child relationship. As a consequence the drill down will always drill down to all levels because it is only one group.

The information we need from Crystal Reports now is:

• We need to know if the user performed a drill down
• We need to know the level that the user started the drill down on so that we can hide the levels that are not in the drill down normally. Example : the user drills down on Level 1 of the hierarchy, that means we want to show Level 2 - but only Level 2 and not Level 3 or Level 4 or higher.

   

First of all I go to my Field Explorer in Crystal Reports into the area for Parameter Fields and create a new Parameter to allow the user to enter an initial hierarchy level.

 

In the next step I create a new formula calledHierarchyLevel with the following syntax:

HierarchyLevel (

    GroupingLevel (

 {0D_DX_C01_BUSINESSOBJECTS_QRY_VAR_HRY.[0D_CUSTOMER]-NodeId}  

    )

)

The part "{0D_DX_C01_BUSINESSOBJECTS_QRY_VAR_HRY.[0D_CUSTOMER]-NodeId}  "  in the syntax is the Node ID of the characteristic of your hierarchy - so the syntax might be different in your case based on the BW query you created. This formula will return the level of the item in the hierarchy.

The second formula that I create is called "Drill" and only contains one line :

DrillDownGroupLevel

Here the details to the function DrillDownGroupLevel:

 

Returns a number that indicates the group level of the current drill down view, or 0 if it is not a drill down view.

 

Examples

If a report has groups on Country and Region, then DrillDownGroupLevel returns 0 in the view without drill downs. Within the drill down view for a Country, DrillDownGroupLevel returns 1, and within the drill down view of a Region, DrillDownGroupLevel returns 2. To check if you are in a drill down view, test to see if DrillDownGroupLevel > 0 returns true.

   

This formula will return 0 in cases where the user did not perform a drill down and in our case it will return 1 when the user did perform a drill down.

 

The next formula that I create is called GroupNumber and contains as well only one line:

GroupNumber

The formula will return the GroupNumber of our report and I will show how we will use that information.

I placed the formulas on my report into the Group Header #1 section

• GroupNumber - Yellow
• HierarchLevel - Blue
• Drill - no background color

   

     

On the formula GroupNumber there is one important behavior that you need to recognize.

The screenshot above represents a drill down from Sales Channel 2 and we see now items from level 2 and level 3 of our hierarchy. Interesting enough though is that the GroupNumber starts on 1 again. We can use that information because we can then pick the hierarchy level from the top group (GroupNumber=1) and use that to suppress hierarchy levels we don't want to show right away. In the example above the top entry is our GroupNumber 1 and the hierarchy level (blue) is 2. So in a drill down case we want to show the hierarchy level and one additional level (+1) but not more - or in other words, anything that is > HierarchyLevel+1 needs to be hidden.

The syntax of the formula SuppressLevel (placed in the Group Header #1 section)

global numbervar suppresslevel;

if groupnumber=1 then

suppresslevel:={@Hierachy Level}+1;

suppresslevel

   

The formula is creating a variable and stores the hierarchy level from the Group where GroupNumber=1 in the variable. So now we can use this logic and use the formula in the condition for the suppression of our Group Header.

Select the menu Report - Section Expert and select the Group header.

Click on the symbol next to the option "Suppress (No Drill-Down) and enter the following formula:

({@Drill}=0 and {@Hierachy Level}>{?Initial Hierarchy Level})

or

({@Drill}=1 and {@Hierachy Level}>{@SuppressLevel})

Drill is the formula we created to show if a drill down was performed.

HierarchyLevel is the formula showing the hierarchy level

Initial Hierarchy Level is the parameter we created to ask for the initial drill down level.

So the formula is checking in the first part if no drill was done (Drill=0) and sets the suppression level to any level higher than the selected level via the parameter (HierarchyLevel>Initial Hierarchy Level).

In the second part the formulachecks if a drill down was performed and the sets the Hierarchy level to any level higher than our formula Suppress Level we created previously.

Now your report should behave as follows:

• The user is being asked on the start of the report up to which level of the hierarchy the report should be shown
• The user is then shown the hierarchy with the selected amount of levels
• The user is able to drill down along the hierarchy, level by level