Skip to Content

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
To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. SAPBW 2006
    Hi Ingo,

    This is excellent Blog but just for your FYI, it is not posted in ‘Business Objects’ area. Currently it is posted only in areas ‘BI Accelerator’, ‘Business Intelligence (BI)’, ‘SAP NetWeaver Platform’.

    Once again thanks for such nice blogs.

    (0) 
  2. Sebastian Koeffer
    Hi Ingo,

    really great blog entry again! Keep it up!

    What if I want to open just one subtree of the hierarchy and let the others closed. I will need more parameters, don’t I?

    Can the drill down also be performed on one page, instead of always opening a new window for the drill down page?

    (0) 
    1. Ingo Hilgefort Post author
      Hi,

      drill down directly on the page is not possible.

      on the “one subtree” – could you be  a little bit more specific and perhaps provide an example based on the items mentioned in the blog ?

      thanks
      Ingo

      (0) 
  3. Hagen Kunze
    Hi Ingo,

    thank you for the really good and again useful blog!!!

    Concerning this theme we need to know, how BW hierarchies can be displayed in reverse
    presentation (Position of lower level entries Up)

    Uusing your example a presetation like this:
      Sales Channel 1.    Value 1
      Sales Channel 2.    Value 2
      Sales Channel 4.    Value 4
    Datsegno Corp.        Summary

    This kind of presentation is often needed within standard reports (f.e. balance sheets, DB II,
    et cetera). Thats why I think this question could be of general interest.

    Thank you & best regards
    Hagen

    (0) 
  4. Goezdem Guengoer Derin
    Hi Ingo,

    I have a P&L query in BEX with one structure and a profit center hierarchy in the rows. There are some key figures in the columns also.

    How can I combine this structure and this hierarchy in Crystal Reports?

    Thanks in advance
    Goezdem

    (0) 
  5. Neeraj Singh
    Hi. Ingo
    first let me thank you for this blog. This blog is quite informational and sorted out many of my issues.

    But just wanted to know is it possible to carry the headings of main report to the drill-down levels also, if yes how to we do that.
    Thanks&Regards
    Neeraj

    (0) 
    1. Ingo Hilgefort Post author
      Hi,

      you can go to the Options / Report Options in the menu File and there you will find a property called “Show all Group Headers On Drill Down”

      regards
      Ingo

      (0) 

Leave a Reply