Create Cascade Filter Panel in SAP Analytics Cloud, Analytics Designer

[chris_schepmans]

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();

Initialize the app

Add the INIT function to the In the Application oninitialization

The code:

Utils.INIT();

Utils.FILL_DD();

Test

Now run and test the application

When Europe is selected

The charts/tables are adapted and also the other drop down boxes are filtered.

Only the Countries from Europe are shown

Only the Cities from Europe are shown

All other drop down boxes work in the same way.

Enhance

It is possible to reduce the number of filter tables used for the drop down boxes in the Filter Panel

In the below sample the 10 tables are converted into 4 tables. Also the names are changed.

Keep in mind that there is a limit on the number of dimension values to be retrieved. This is the case in my sample for the order id.

What needs to be done in the code? In the INIT function the corresponding v_filter_table’s need to be changed as shown below.

 

I hope this is helpful in developing SAP Analytics Cloud, Analytics Designer applications.