Technical Articles

February 2, 2021 7 minute read

Create Cascade Filter Panel in SAP Analytics Cloud, Analytics Designer

Introduction

In the current SAP Analytics Cloud, Analytics Designer release no widget is available that corresponds with a Filter Panel

In this blog post I try to explain a possible solution.

It includes a full cascade filtering and also deals with dimensions with high number of values.

All code is prepared with the use of SAP Analytics Cloud, analytics designer Developer Handbook

Preparation

As preparation a model is created on top of the below excel sheet and SAP Analytics Cloud model

Any model can be used.

Steps to create the application

Create 2 panels

Create as show below 2 panel on the canvas

Create the Drop Down boxes

Create the required number of Drop Down boxes and the corresponding text fields in the “Filter_Panel”. For this exercise, 10 Drop Down boxes are required

Create the Filter Tables

Create the required number Table components corresponding with the Dimensions used in the Drop Down boxes. We start with 10 tables. Later a step will explain the reduction of number of tables.

Hide this panel and uncheck “Show this item at view time”

Script Variables

Create the below script variables

v_chart: Array containing the charts to be filtered via the filter panel

Tip: Ensure you already have a chart in the application , otherwise the Type Chart will not appear

v_dd: Array containing the Drop Down boxes used during processing

v_filter_table: Array containing the Dimension tables to be filtered via the filter panel

v_level: Array with the level used during processing

v_number_chart: Number of CHARTs to be filtered

v_number_of_filters: Number of Filters used in the Panel

v_number_table: Number of TABLEs to be filtered

v_selectedkey: Array to store the selected keys from the drop down boxes

v_table: Array containing the tables to be filtered via the filter panel

Script Objects

Create the below Script Objects

Untils.FILL_DD: Populate the Drop Down boxes

The code

for ( var j=1; j<v_number_of_filters+1; j++)
{
	v_dd[j].removeAllItems();
    var res= v_filter_table[j].getDataSource().getResultSet();
    if (res.length >= 500)
    {
    	v_dd[j].addItem("many","To many values");
        v_dd[j].setSelectedKey("many");
	}
    else
    {
    	for ( var i=0; i<res.length; i++)
        {
        	v_dd[j].addItem("all","All");         
            v_dd[j].addItem(res[i][v_level[j]].id,res[i][v_level[j]].description);
            v_dd[j].setSelectedKey(v_selectedkey [j]);         
        }
	}          
}

INIT: Initialize Drop Down , Levels , Tables

The first piece of code:

In this part of the code the Dimension is linked to the drop down box and the corresponding text is populated

The code

// Define the filters to be used and load the description
v_number_of_filters = 10;
v_level [1] ="Continent"; DD_Tx_1.applyText("Continent");
v_level [2] ="Country"; DD_Tx_2.applyText("Country");
v_level [3] ="City"; DD_Tx_3.applyText("City");
v_level [4] ="Company"; DD_Tx_4.applyText("Company");
v_level [5] ="Line"; DD_Tx_5.applyText("Product Line");
v_level [6] ="Category"; DD_Tx_6.applyText("Product Category");
v_level [7] ="Product"; DD_Tx_7.applyText("Product");
v_level [8] ="Sales_Rep"; DD_Tx_8.applyText("Sales Rep");
v_level [9] ="Manager"; DD_Tx_9.applyText("Manager");
v_level [10] ="Order_Id"; DD_Tx_10.applyText("Order Id");

The next part of the code is where the Dimension Tables are stored in an array for later processing

The code

// Define the tables that will be used for the filters
v_filter_table[1] = Table_1;
v_filter_table[2] = Table_2;
v_filter_table[3] = Table_3;
v_filter_table[4] = Table_4;
v_filter_table[5] = Table_5;
v_filter_table[6] = Table_6;
v_filter_table[7] = Table_7;
v_filter_table[8] = Table_8;
v_filter_table[9] = Table_9;
v_filter_table[10] = Table_10;

The next part of the code is where the Drop Down boxes are stored in an array for later processing

The code

// Load the Drop Down filters into an array
v_dd [1] = DD_1;
v_dd [2] = DD_2;
v_dd [3] = DD_3;
v_dd [4] = DD_4;
v_dd [5] = DD_5;
v_dd [6] = DD_6;
v_dd [7] = DD_7;
v_dd [8] = DD_8;
v_dd [9] = DD_9;
v_dd [10] = DD_10;

The below code will load some defaults to the Drop Down boxes

// Add and Define the "All" as DD Item
for ( var i=1; i<v_number_of_filters+1; i++) {v_dd[i].addItem("all","All");}
for ( var j=1; j<v_number_of_filters+1; j++) {v_selectedkey[j]="all";}

The last part of the code is about the business charts and table used in the application

The code

// Define the number of CHART to be filtered
v_number_chart = 1;

// Load the CHARTs into an array
v_chart [1] = Cost;

// Define the number of TABLES to be filtered
v_number_table = 1;

// Load the TABELs into an array
v_table [1] = Company;

SET_CHART_TABLE_FILTERS: Enable the filters to the Charts and Table

This function has 1 parameter: Level as Integer

The code

for ( var i=1; i<v_number_chart+1; i++)
{
    v_chart[i].getDataSource().removeDimensionFilter(v_level[Level]);
    if (v_selectedkey [Level] !== "all")
    {
	v_chart[i].getDataSource().setDimensionFilter(v_level[Level],v_selectedkey [Level]);
    }
}

for ( i=1; i<v_number_table+1; i++)
{
    v_table[i].getDataSource().removeDimensionFilter(v_level[Level]);
    if (v_selectedkey [Level] !== "all")
    {
	v_table[i].getDataSource().setDimensionFilter(v_level[Level],v_selectedkey [Level]);
    }
}

SET_DIM_FILTERS: Enable the filters to the Drop Down boxes

This function has 2 parameters: Selected_key as String and Level as String

The code

for ( var i=1; i<v_number_of_filters+1; i++)
{
   v_filter_table[i].getDataSource().removeDimensionFilter(Level);  
   if (Selected_key !== "all")
   {
      v_filter_table[i].getDataSource().setDimensionFilter(Level,Selected_key);            
   }
}

Enable the functions on the Drop Down boxes

For each Drop Down boxe the below functions must be enabled.

The code

v_selectedkey [1] = DD_1.getSelectedKey();
v_dd [1] = DD_1;
Utils.SET_DIM_FILTERS(v_selectedkey [1],v_level[1]);
Utils.SET_CHART_TABLE_FILTERS(1);
Utils.FILL_DD();