Technical Articles
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 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.