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

[former_member189638]

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

[former_member203645]

Rakesh, really great solution !!!

We are looking for this kind of solution, I have quick question. I tried implementing the same but I am missing 2 things.

Is your hover working on the Pie chart ??

It is just showing ":" & measure value.

Column heading is still displaying drill_down ??? How did you manage to change the name of heading.

Pie chart legend is still displaying Drill_down ??/

[former_member189638]

Table Header: If you check the 9th image, that is the formula you need to use in the Table header. So, when you Drill using the Dropdown, the Table header will also change according to it.

Chart Label: Right click on the pie Chart -> Format Chart -> Legend -> Title. Here in the Title Label, select Custom Title. Then in the Title label, select Formula option. Use the above formula here as well..So, the legend title changes with the selection.

Pie Chart Hover: When I do a mouseover, it does show me the measure values. but not showing the Dim values. May be because of the variable created at WebI level. I will take a look at it and will let you know if I find any workaround for this.

[former_member203645]

Awesome !!! We are looking for this solution from many days.

Pie Chart Hover: When I do a mouseover, it does show me the measure values. but not showing the Dim values. May be because of the variable created at WebI level. I will take a look at it and will let you know if I find any workaround for this.

If the hover works then it will great solution for us.Please let me know if you find some . I will try from my end.

[former_member203645]

Rakesh,

I know you provided a great solution but due tool limitation we are having a hard time.

What if the user add a new filter on filter bar. Few days I started looking for some security right to disable the filter button.

http://scn.sap.com/thread/3371503

But thanks again & again, SAP do some miracle that we encourage gurus to come up with lot of tricks and solutions