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.
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 🙂