Cascading filters are a combination of filters where the selection of one filter determines the list of values available for selection in the subsequent filters. The primary objective is to ensure a better user experience by providing users with valid combinations of input parameters.

In this blog, lets look at how cascading filters can be achieved in SAP HANA through Value Help (commonly called F4 help) which is a property of Input Parameters. Given below are some scenarios of cascading filters being consumed in Analysis for Office and Lumira Designer.

SAP HANA Cascading Filter consumed in Analysis for Office

SAP HANA Cascading Filter consumed in Analysis for Office

SAP HANA Cascading Filter consumed in Lumira Designer

HANA Table used in this example

We require 2 HANA views for this purpose. Let’s start with the first view – we’ll name it ‘CV_Value_Help’. We will build this view as a dimension based calculation view because it will be used only for value help.

We then add 4 input parameters (IP_CATEGORY, IP_COUNTRY, IP_STATE, IP_CUSTOMERNAME) and assign them as filters to their corresponding columns.

This will return 2 rows as seen below:

Now let’s create the second calculation view and name it ‘Cascading_Multiple_IP’. We follow a similar procedure and create 4 Input parameters but this time we have the value help column mapped to the previous view ‘CV_Value_Help’.

Once the Input parameters are created with the respective columns, we can add them as filter to the existing columns. Each input parameter of the current view is then mapped to the previous view. For instance, for the IP_CASCADE_CATEGORY input parameter we map the other 3 input parameters with ‘CV_Value_Help‘ view. Other input parameters are also mapped in a similar manner to implement cascading across all filters.

Now, ‘Cascading_Multiple_IP’ view can be consumed in a frontend application to achieve the cascading filter functionality.

You can learn more about Visual BI’s SAP HANA Offerings here.