WEBI - Current level (number) we're at, in a hierarchy

[mhmohammed]

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.



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


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

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 :smile: