In the previous blog in this series, you learned about bookmarking features in Analytics Designer. In this blog, we will discuss how to implement cascading filter functionality leveraging result members and data selection APIs.

Imagine having multiple filters and using them all at once to reveal nothing underneath. Such cases can be challenging to handle when manually filtering each option without knowing the structure of the data. In such cases, cascading filters can make this task easy and automated when you have filters enabled for multiple dimensions. Even though cascading filters are supported natively in Story, it can only be achieved using a workaround in Analytics Designer since data binding is not yet supported (blog is based on Q2 2020 version). In this blog, let us see how you can leverage a table component and its scripting functions to provide cascading filter functionality to end users in Analytics Designer of SAP Analytics Cloud. You can see the cascading filter’s functionality in action below.

Necessity for cascading filters

Previously, we used the getMembers() API of the chart or table’s data source to get members of a dimension and bind them to any selector widget like dropdowns as filters in Analytics Designer using scripting. This way, there have been some challenges in members available for filtering when multiple dimensions with relevant filters were used. Let us assume a dashboard having two relevant filtering options, say checkboxes – State and Store. The user decides to filter on the State – ‘California’. Now in the Store checklist, the user might expect only relevant store information as in ‘California’ State. Sadly, this is not the case. The store filter will show all members rather than that of the State filter related to ‘California’.

To avoid this behavior, you can implement cascading filters using data source APIs that automate this task for you. There are two predominant APIs used to achieve this functionality:

• getDataSelections – Returns selections of data cells.
• getResultMember – Returns result member info by passing the dimension and its respective selections.

1. Setting up Canvas and Widgets

As a first step, you need to add the necessary widgets to the canvas. Here the application has two charts, one table and four checkboxes – one for each dimension filter: State, Stores, Product Category and Sub-category. Checkboxes facilitate multiple selections for filtering.

2. Binding the members for filtering

The script to populate all the members of each dimension to their respective checkbox for filtering can be added to the onInitialization event.

The code above shows how to implement a cascading filter considering two checkboxes – ‘State’ and ‘Store’ as an example for simplicity. After this, the getMembers() API is used to get the members for each dimension in respective checkboxes in the onSelect event followed by iterating and binding each member as item lists.

3. Implementing Cascading effect using Scripting

Let us see how to populate the Store checklist items relevant to the selection made in the State checkbox. The steps involve the following logic after we set initial members for respective checkboxes:

1. Removing checklist items in subsequent checkboxes to bind relevant items based on the new selection
2. Clearing previously applied filters in subsequent dimensions
3. Filtering the respective dimension based on the new member selected
4. Fetching data selections (getDataSelections) of those applied filter members
5. Getting result members (getResultMember) from the above steps and populating the new checklist items

Below is an example demonstrating how State checkbox (CG_State) performs the logic in order and achieves cascading for relevant Store items.

Using the getDataSelections API, the data selections of the selected State filter are retrieved. This can now be used on the getResultMember API to fetch the relevant checklist items that can be added to the checkbox on the canvas via an iteration.

To achieve cascading using multiple filter widgets, repeat by adding scripts in each filter component’s onSelect event following the cascading logic.

Click here to talk to our experts about SAP Analytics Cloud. To know more about the Analytic Applications of SAP Analytics Cloud, please check out our blog series here.