Custom BW hierachy level selection in WEBI

April 8, 2013 by: David Lai

Connecting to BW using a BICS connection in WEBI is pretty cool, but unfortunately the ability to select a desired level from a hierarchy proves to be very difficult without any tricks.

A common request that I’ve been finding from clients these days is the ability to select a desired level in a hierarchy and not have to expand all the way through in order to see that level.

Manual selection of hierarchy level at the report level

For example if I have 5 levels and I want to select level 3 from the member selector, it doesn’t allow you to skip level 1 and level 2.  It is mandatory that those are included in the selection.

Can't skip levels on member selector

We can technically create customized hierarchies for each level combination selection we want to see.  But that would require alot of work especially if you have to replicate security authorization for each hierarchy.

We can also go the universe route, but since any future UNV development is not part of SAPs roadmap, we would want to stay away from UNVs when possible.
In addition going the UNV route limits the prompt, report and input control hierarchy functionality that is available using a BICs connection.

Fortunately we can use a combination of the depth function and report filters to accomplish the customized level section using a BICs connection.

In our first example we’ll want to build a report that just shows level 3 of a hierarchy.

Step 1:

In our query let’s choose up to level 3 on the member selector.  If we choose all levels that will impact performance. Once that’s done you can execute your query.

Member Selection

Step 2:

Create a variable with the following formula. “=hierarchy.depth”

Note that the depth starts at 0.  So level 1 = depth 0, level 2 = depth 1, etc.

Create depth variable

Step 3:

Now add a filter on your report, or section, or table.  It’s up to you where you want to limit your hierarchy view to level 3.

Report level depth filter

Report showing only level 3 of hierarchy

That’s it! Piece of cake right? 🙂

Now here are some other cool examples we can use with the filter.

If we want to see everything below level 2, we can just put a filter where the hierarchy depth variable is >= 1

Select level 2 and all of its descendants

Level 2 and greater results

If we want to see just levels 1 and 3, you can do an inlist filter where the hierarchy depth variable is in the list of 0 and 2.

Note: if you select past level 3 on your member selection it will show the + sign but you cannot expand past it

Filter level 1 and 3

 Level 1 and 3 results

Comments

3 Responses to “Custom BW hierachy level selection in WEBI”
  1. Yang Luo says:

    Hi David,

    for the customized hierarchy level, it does work when am designing the report, but if i log back in and view it, it does not show any rows, unless i go back to design mode and re-apply the filter, then the data will show.

    am at BO SP04 Build 936.

    Thanks
    Yang

  2. Niranjan says:

    Hi David,

    I was going through the custom Hierarchy using in SAP webi report. I have question in regards to that, now that I have a requirement where we are using region hierarchy and it expands to 10 levels. now we have to pick the leaf nodes only out of this. we have two tabs one using the hierarchy structure and the other using the flat structure. now that the flat structure is coming up with parent node as well the leaf nodes. which is causing incorrect summation.

    also i tried to look for a member selector screen, but i couldnt find specific to the hierarchy dimension in the webi Query.

Leave a Reply


nine + 2 =