Through this blog post we will learn

1. How to create cascading filters for the Hierarchy node levels (2 levels) in two different dropdown filters.
2. Add logic for “ALL”  as a list of value which will help you to reset the selected value and allow Table_1 to display all values.

Example: I am using SAC trail version Model BestRunJuice_SampleModel to demonstrate cascading filters.

I am doing it for Product Dimension which has two levels in Hierarchy. The Parent node ( i.e. level 1 ) of Product will be shown as a first dropdown filter named Product Level 1 and the next level of hierarchy (i.e. level 2) of Product will be shown as a second dropdown filter as Product Level 2.

Product Level 1 will list only the level 1 of the product hierarchy as list of values.

Product Level 2 will list only the Level 2 of the product hierarchy as list of values.

Cascading filter means, when user will select any Level 1 node value from Product Level 1 drop down, the list of values for Product Level 2 should get automatically restricted only to the list of values which are from Product Level 1 (parent) node selected by user.

On execution of Analytic application report, you can see Product Level 1 and Product Level 2 is restricted on ALL values by default.(check the script logic section to see how we can set ALL value as default)

Product Level 1 as you can see is restricted only on level 1 nodes of the product hierarchy and Product Level 2 as you can see is restricted only on level 2 nodes of the product hierarchy, as I have selected ALL in Product Level 1, we can see all level 2 nodes listed in Product Level 2 dropdown.

Now I will restrict the Product Level 1 on Alcohol and we should see the only level 2 nodes which belong to Alcohol in Product Level 2 also the Table_1 is restricted only to Alcohol.

Now I’ll  select Dark Beer on Product Level 2. we can now see data is restricted on Alcohol(Product Level 1) and DarkBeer(Product Level 2).

If you select ALL in Product Level 2 it will show all level 2 nodes again but the ones only under Alcohol as Product Level 1 is still restricted. And if you select ALL in both the filters the Table_1 will show all the data as expected.

So let’s start with the Build Objects required for the above business requirement.

1. Go to the analytic application, add two text boxes for Product Level 1 and Product level 2 and name them as Dropdown_1_Label and Dropdown_2_Label as shown below.

2. Add Input control dropdown as Dropdown_1 and Dropdown_2  add a Table as Table_1 on which Product Level 1 and Product Level 2 filters will be applied.

1. Create two script variables parentId(to store the parent of the Product Level 2) and PL1(to store the Product Level 1 dropdown value selected by user)
2. For scripting, below is the sequence in which the Analytic application works

• “onInitialization” is called first which will load all the drop down values in both Product Level 1 and Product Level
• After you select a value from Dropdown_1(Product Level 1), Dropdown_1(Product Level 1) script logic gets called, we have to add logic here to make sure the selected value gets applied on Table_1
• When you select value from Dropdown_2, Dropdown_2 script logic gets called, we have to add logic here to make sure the selected value gets applied on Table_1

You will have to add logic in above 3 places for the dropdown to work.

Use of script variables:  script variables are used in cases where you want to store a value from any of the 3 places mentioned above (for example) and use it in any other script logic within the Analytic application report, it behaves as a global variables which can hold derived value even after you have exited the script logic where it was derived and you can use the value in other script logic calculations, it helps to avoid the repetitive code execution.

Lets now begin with the Script Logic:

1. We will initialise list of values for both Dropdown_1(Product Level 1) and Dropdown_2(Product Level 2) as soon as we load the report.  We will have to add one more value as ALL in the dropdown list of values, so that if you want to select all values for the dropdown we should be able to reset the previously selected value and table   should get filtered on ALL value of either level 1 or level 2 of the product hierarchy.Click on Cavas->”onInitialization”The logic will fetch level 1 and level 2 values for Dropdown_1(Product Level 1)  and Dropdown_2(Product Level 2) list of values , for Dropdown_2(Product Level 2)it should not again repeat the Level 1 node values from Dropdown_1(Product Level 1) hence we have to do a check for the Parentid and if it’s not part of Parent ID then add the LOV to the Dropdown_2(Product Level 2) list of values for level 2 nodes.

   //logic will get 1st level hierarchy nodes for Product in variable m
   Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 1); 
   var m = Table_1.getDataSource().getResultSet();
   console.log(m);
   //console log will display the values of variable m when in debug .
   for (var i = 0; i < m.length; i++){
   var id = m[i]["Product_3e315003an"].id;
   var description = m[i]["Product_3e315003an"].description;
   //appending values from m variable in Dropdown_1
   Dropdown_1.addItem(id, description);
   //Adding value ALL as one of the dropdown filter values for Dropdown_1
   Dropdown_1.addItem("all", "ALL");
   }
   //by default setting the Dropdown_1 to value ALL on initiatilization 
   Dropdown_1.setSelectedKey("all");
   //logic will get 2nd level hierarchy nodes for Product in variable n
   Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 2);
   	var n = Table_1.getDataSource().getResultSet();
   console.log(n);
   //console log will display the values of variable n when in debug .
   	for (  var j = 0; j < n.length; j++){
   	var id2 = n[j]["Product_3e315003an"].id;
   	var description2 = n[j]["Product_3e315003an"].description;
   	 parentId =  n[j]["Product_3e315003an"].parentId;
   //skip its parent node (level 1). For level 2, can check whether its parent id is NULL. But when you populate Level 3, need to write a function to skip Level 2 memebers
   	if (parentId) {
   	Dropdown_2.addItem(id2, description2);
   	}		
   	}
   //Adding value ALL as one of the dropdown filter values for Dropdown_1
   	Dropdown_2.addItem("all","ALL");
   //by default setting the Dropdown_2 to value ALL on initiatilization 
   	Dropdown_2.setSelectedKey("all");
   ​

2. Logic for Dropdown_1 script: This is to apply the selected value from Dropdown_1(Product Level 1) to the Table_1 output and also include the cascading filter behavior for Dropdown_2(Product Level 2). Dropdown_2(Product Level 2) will restrict the level 2 nodes of the Product hierarchy only for the Parent node (level 1) which is selected from Dropdown_1(Product Level 1)

3.  Dropdown_2 script logic will include logic to restrict the data for the Table_1 based on the list of value selected from the Dropdown_2 (Product Level 2).

It also has logic for ALL selection, whenever user selects ALL in both Dropdown_2 and Dropdown_1 it should show all data in Table_1.

Console.log will help you to see the variable values generated during debugging the script, we shall cover the use of console log in the debugging blog post.

Summary: This way we can achieve Cascading filter behavior for hierarchy node levels and  “ALL”  as a list of value which will help you to reset the selected value and allow to display in table all values using Analytic Application Designer via scripting.

If you think this post has helped you in any way please like and share your thoughts in the comments section below. Incase of any questions please ask in the comments section below  #SAP Analytics Cloud.

Thanks for reading.

Regards,

Shraddha Mundwadkar