In Analytic Designer report after restricting the list of values for dropdown input control, we often have the issue of sorting values of dropdown. It is not automatically sorted by system as per the description of LOVs. Additionally if we have “All” as one of the LOV then it will not reflect in the sorted order making it unpresentable to the user.

In continuation with the blog post Cascading filters for hierarchy node levels | SAP Blogs,

In this blog post you will learn how we can sort the LOV values for the dropdown list including sorting for cascading dropdown filter.

Screen shot of LOV values without applying the Sort logic.

After applying Sort logic

Code will have two sections

1. Logic for sorting
2. Logic to derive id for selected description and restrict Table_1 data as per selected description (Sorting is on description).

Oninitialization script:

//restrict the data of table to Product level 1 for 1st level hierarchy nodes list of values dropdpown
Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 1); 
var m = Table_1.getDataSource().getResultSet();
var a = [""];
for (var i = 0; i<m.length ; i++)
 { a[i] = m[i]["Product_3e315003an"].description; 
}
a[i] = 'All';
//after adding all LOV's including All value we have to sort the array data.
a.sort();
//added the lov's only for Dropdown_1, Product Level 1 LOV's only.
{for ( var b = 0;b<a.length;b++)
{Dropdown_1.addItem(a[b]);}}
Dropdown_1.setSelectedKey("All");

Dropdown_1 script:

// PL1 variable will have Product Hierarchy Level 1 selected value by user
var PL1 = Dropdown_1.getSelectedKey();
//if user selects ALL as a value from dropdown then we should reset all the filters on Table_1 so all values are shown, else logic to restrict the table with selected value.
if (PL1 === "All") {
Table_1.getDataSource().removeDimensionFilter("Product_3e315003an");	
}
else {
//as we have description selected in PL1 variable the Table_1 can be restricted only by ID value and not description value so below logic will map the descriprtion to the ID value.
Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 1); 
var x = Table_1.getDataSource().getMembers("Product_3e315003an");
for ( var z=0 ; z<x.length ; z++)
{ if  ( PL1 === x[z].description) 
{ var p = x[z].id ; //once the ID value is derived it is stored in variable P
 Table_1.getDataSource().removeDimensionFilter("Product_3e315003an");
 //value from variable p is passed to set the filter for Table_1 and also once its filter , we have restricted the table data on Product Level 2 so we can add values for Dropdown_2
Table_1.getDataSource().setDimensionFilter("Product_3e315003an",p);
Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 2); 
}	
}}
//as we have restricted filtered data on level 2 of hierarchy, variable n will have only level 2 Product hierarchy nodes.
var n = Table_1.getDataSource().getResultSet();
console.log(n);
//adding hierarchy level 2 values in Dropdown_2 for product hierarchy after Dropdown_1 is selected.
Dropdown_2.removeAllItems();
var b =[""];
for (var j = 0; j < n.length; j++) {
 var parentId =  n[j]["Product_3e315003an"].parentId;
//skip its parent node (level 1). For level 2, can check whether its parent id is NULL. this is to avoid adding level 1 nodes again in Dropdown_2 list.
if (parentId){
 b[j] = n[j]["Product_3e315003an"].description;

}

}
b[j] = 'All';
b.sort();
	for ( var y=0 ;y<b.length;y++)
	{
	Dropdown_2.addItem(b[y]);

	}
//We have to se the default value for Dropdown_2 to All value.
	Dropdown_2.setSelectedKey('All');
	

Dropdown_2 script:

var sel = Dropdown_2.getSelectedKey();
//variable sel will store the Dropdown_2 selected value
var PL1 = Dropdown_1.getSelectedKey();
//PL1 is the script variable created which will store the Dropdown_1 selected value
if (sel === "All" ) {
	if ( PL1 === "All") {
//logic when Dropdown_1 and Dropdown_2 both have ALL value selected.
Table_1.getDataSource().removeDimensionFilter("Product_3e315003an");}
else {
Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 1); 
var x = Table_1.getDataSource().getMembers("Product_3e315003an");
for ( var z=0 ; z<x.length ; z++)
{ if  ( PL1 === x[z].description) 
{ var p = x[z].id ; 
 Table_1.getDataSource().removeDimensionFilter("Product_3e315003an");
Table_1.getDataSource().setDimensionFilter("Product_3e315003an",p);	
}}}}
else {
//logic when Dropdown_2 will have any hierarchy value selected.
console.log(sel);
Table_1.getDataSource().removeDimensionFilter("Product_3e315003an");
Table_1.getDataSource().setHierarchyLevel("Product_3e315003an", 2); 
//logic to derive ID from description so the Table_1 data gets restricted to the selected value by filtering it on correct ID
 x = Table_1.getDataSource().getMembers("Product_3e315003an");
for (  z=0 ; z<x.length ; z++)
{ if  ( sel === x[z].description) 
{  p = x[z].id ; 
 Table_1.getDataSource().removeDimensionFilter("Product_3e315003an");
Table_1.getDataSource().setDimensionFilter("Product_3e315003an",p);	
}}}

I have added comments in the code script for reference to help understand the impact of script on data.

Summary: Blog post gives details on sorting, on description of the LOV values for dropdown input control for analytic designer report.

Incase of any questions, please reach out to me via the comments section and I will respond to them .If you think this post has helped you in any way please like and share