I’ve seen few people requesting for a formula that shows the level of hierarchy we’re currently at, while drilling (down/up) and/or depending on that number, they can do some other actions.
Ex: We have a hierarchy State -> City -> Store Name, the Level (formula) should show 1 when we’re on State (before drillingh), 2 when we drill down to City and 3 when we drill down to Store Name. Let’s create a simple formula to achieve that.

BO 4.1 SP4 used for this walk through:
1. Create a sample Webi report using eFashion universe with just Year and Sales Revenue
2. Create a variable as below
Current Level of Hierarchy =Concatenation(“Level “;If(DrillFilters([State]))=”” Then 1 Else Length(DrillFilters())-Length(Replace(DrillFilters();”-“;””))+2)
3. Drop that variable in a Blank Cell, the variable shows Level 1 before we drill on State.

Snap 01.PNG

4. Enable Drilling and drill on the state Texas, the formula shows Level 2.
Snap 02.PNG

5. Drill on the city Houston, the formula shows Level 3.Snap 03.PNG

How the formula works is, it shows Level 1 when we don’t have drilled on State and when we start drilling, it calculates the number of “-” in the result of DrillFilters() function and adds 1 to it.
Hope that helps someone, someday 🙂

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply