In this blog post, I will demonstrate how we can have cascaded effect in filters in an analytical application using dropdowns. Each dropdown is designed such that it affects the all next ones in hierarchy, along with charts and tables.

For this, I will use, as an example, the geographical hierarchy (or location hierarchy) of India to create 3 dropdowns and a chart, as shown below:

To begin with, first we have add the members of Zone, State and Sub-regions in the 3 dropdowns in the Application.onInitialization script area.

Once that is done, we have to configure the script for Dropdown_Zone – onSelect, where we have to add the members for Dropdown_State and Dropdown_Sub_Region as per the “Zone” selected by the user. The script for Dropdown_Zone – onSelect is provided below:

var zone_select = Dropdown_Zone.getSelectedKey();
var state_array = ArrayUtils.create(Type.DataContext); var k1=0;//creating array to store states as per zone selection
var subregion_array = ArrayUtils.create(Type.DataContext); var k2=0;//creating array to store subregions as per zone selection

if(Dropdown_Zone.getSelectedText()!=="All")
{	Chart_Main.getDataSource().setDimensionFilter("Zone",zone_select);

	var zone_filtered_data = Chart_Main.getDataSource().getResultSet();//table data after filtering user selected Zone
	console.log(zone_filtered_data);
	// Adjusting the dropdown data for State and Sub-region now
	Dropdown_State.removeAllItems();
	Dropdown_Sub_Region.removeAllItems();
	
 	if(zone_filtered_data.length>0)
		{
			for(var count1=0; count1<=zone_filtered_data.length-1 ; count1++)
				{
					for(var myState in zone_filtered_data[count1])
						{
							if(myState==="State")
								{
									state_array[k1] = zone_filtered_data[count1][myState];//capturing the states under selected zone
									k1++;
								}
						}
				}
			
			for(var count2=0; count2<=zone_filtered_data.length-1 ; count2++)
				{
					for(var mySubr in zone_filtered_data[count2])
						{
							if(mySubr==="Subregion")
								{
									subregion_array[k2] = zone_filtered_data[count2][mySubr];//capturing the subregions under selected zone
									k2++;
								}
						}
				}
			
		}
 	for(var j=0; j<=state_array.length-1 ; j++)
	{
		Dropdown_State.addItem(state_array[j].id,state_array[j].description);
	}
 	for(var k=0; k<=subregion_array.length-1 ; k++)
	{
		Dropdown_Sub_Region.addItem(subregion_array[k].id,subregion_array[k].description);
	}
 
}
else
	{
		Chart_Main.getDataSource().removeDimensionFilter("Zone");
		Dropdown_State.removeAllItems();
		Dropdown_Sub_Region.removeAllItems();
		Dropdown_State.addItem("All");
		for(var m=0; m=states.length-1 ; m++)
		{
		Dropdown_State.addItem(states[m].id,states[m].description);
		}
		Dropdown_State.setSelectedKey("All");

		Dropdown_Sub_Region.addItem("All");
		for(var n=0; n<=sub_regions.length-1 ; n++)
		{
		Dropdown_Sub_Region.addItem(sub_regions[n].id,sub_regions[n].description);
		}
		Dropdown_Sub_Region.setSelectedKey("All");
		Dropdown_State.removeItem("#");
		Dropdown_Sub_Region.removeItem("#");
		
	}

Now, if user selects a Zone( let’s say Central), the subsequent dropdowns will have only those states and sub-regions that fall under Central Zone.

Next, we have to repeat the same script above for Dropdown_State as well, so that if user selects a State, the next dropdown, i.e, Dropdown_Sub_Region will have the sub-regions for that State only.

The final analytical application looks as shown below :

Cascading effect in dropdowns

SUMMARY – This way, we can achieve cascaded filters for any number of dropdowns/ radio-buttons/ checkboxes in analytics designer, and each filter will individually affect the application widgets as well as the subsequent filters in the hierarchy.

REFERENCES –

• https://help.sap.com/doc/958d4c11261f42e992e8d01a4c0dde25/2021.20/en-US/index.html#Dropdown

Hope this article helps you.

Thanks for reading.