How to drill down to next level in a hierarchy using DropDown

Using a DropDownBox in a WebI report

The ideal use of a Drop Down in the Filter Bar is to display the data for the selected value.

Now if we select California then the table and chart shows the data for California as shown below

Changing the Requirement

Now if we change the requirement slightly where we want to see the data for the next level in the hierarchy of what we select in the drop down.

For e.g. if we select California from State Drop Down, table and chart should show data for all Cities in California. If we select Houston from City Drop Down then table and chart should show all data for Stores in Houston and so on.

For this the first option that we have in our mind is to enable the Drill option.

One issue with this option is that the dropdownbox becomes dynamic in filter bar. They appear and disappear based on the drill that we do. We don’t want this to happen

Another issue with the Drill option is if we select All State in the above scenario, it will show all Stores. We need to drill up to display State. This is wrong because it should be showing all States if we select All State in Drop Down.

Due to this limitation we don’t enable the Drill option, but use a different approach which is further explained in detail in this document.

Solution

Going back to how our report was originally.

We will create 3 variables which is nothing but taking the count of State, City and Stores. 

Next step is to create the most important variable which provides the required solution to our scenario. So, create a variable with the following code.

Quick Explanation of the Code

=If(Length(DrillFilters([State]))=0 And Length(DrillFilters([City]))=0 And Length(DrillFilters([Store name]))=0;[State];

If([Count_State]=1 And Length(DrillFilters([City]))=0 And Length(DrillFilters([Store name]))=0;[City];

If([Count_State]=1 And [Count_City]=1 And Length(DrillFilters([Store name]))=0;[Store name];

If([Count_State]=1 And[Count_City]=1 And [Count_Store]=1;[Store name]))))

• This is a nested If..Else Condition
• First check the length of the drill filter in all the drop downs. If it is 0, it means nothing is selected in any drop down. So, we should display State.
• Second we check if Count of State is 1 and length of City and Store Drop down is 0 which means State drop down is selected. So, we should display City
• Third we check if Count of State and City is 1 and length of the Store Drop Down is 0 which means Store is selected. We don’t have any hierarchy further so we will display Store.
• Fourth if all drop downs are selected it means again to display Store

So, we will use this variable in the Table column and the chart instead of State, City or Store objects.

One thing we can see is the table header does not look good. It displays the variable name. So, we will create a header formula with the same code. Here instead of using the Object, we will use the Text

Now we have everything in place. So, we will test the functionality

Testing the Functionality

Nothing selected in drop down means the table and chart will display all the States.

If we select Texas from the state, we can see that the table and chart are showing data for all the Cities in Texas.

Now if we select Houston, table and chart shows data for all the Stores in Houston.

Similarly if we select Store, the table and chart shows the data for that store.

Now if we switch back to California as State, it will show the data for all Cities of California. it will not show data for all stores as it did with the Drill Option that we discussed before.

Also when we select All for all Drop Downs, it will bring back all the States instead of All Stores as it did with the Drill Option