With cascading filters, the consecutive dropdown boxes influence each other. Meaning the selection made in dropdown 1 will filter the results in dropdown 2 and 3, thus only showing the items that make sense. In this article an example will be used of product category, subcategory and product.
Steps for creating cascading filters with dropdown boxes (BW)
The first step is to create a data source of type Query with the three required dimensions in the rows.
Script On Startup
In the “On startup” script, the dropdown boxes need to be populated with the correct items.
Script Dropdown 1
When a selection is made in the first dropdown, “Product Category”, two things need to happen:
(1) The data source must be filtered to the selection just made
(2) The other two dropdowns influenced by this selection should be repopulated
This will result in the last two dropdown only showing the items that are applicable for the selection made. So if the category “Clothing” is selected, the second dropdown will only show the sub categories belonging to that category. Same for the third dropdown, it will only show those items applicable for the category and sub category selected previously.
Script Dropdown 2
When a selection is made in the second dropdown, basically the same things need to happen as in the first dropdown. The difference is, now only the last dropdown box needs to be repopulated.
Cascading filters with Filter Panel (BW)
The filter panel can have the same cascading effect as when using dropdown boxes. The disadvantage is that after each selection, Apply has to be clicked first before the items are filtered, and of course it will then filter the dashboard as well. So if for example you choose a product category, you have to hit the Apply button, only then the sub categories list will be filtered accordingly. So it takes more clicks than with the dropdown boxes to get the same effect. With the dropdown boxes you also have more control via scripting over what exactly should happen when a selection has been made.
It is not possible to create cascading filters with a Universe or HANA as a data source, it only works with BW. This applies to both the dropdown and panel filter component. The list of items will always show all possible values instead of showing only those values applicable for the selections made previously.
As shown in the example below, in the first dropdown, “Clothing” is selected, but the second dropdown is still showing all sub categories, hence no filter was applied.
However, the dropdowns do filter the data as can be seen in the table which uses the same data source as the dropdown boxes.
Unfortunately there is no way yet to create cascading filters when using a Universe or HANA as a data source. It would of course be nice if this usefull feature would become available in the future. But for now you can use it when working with BW. So then the only decision left is, dropdown boxes or filter panel?