Skip to Content
Technical Articles
Author's profile photo Prathamesh Acharya

Implement cascading filter effect using DropDown widget

Hi All,

In analytics applications, it is a common requirement to have cascading dropdown filters.

This blog post uses a generic example (Region <-> Country <-> State <-> City) to demonstrate this functionality.

Users can choose dimension members that appear in the Region, Country, State or City dropdown list and pass them as filter criteria to other widgets that are used in the application.

Cascading effect will be visible when user navigates from one dropdown to another.

For e.g. If Region = Asia is selected by user then Country dropdown will populate only Asian countries, State dropdown will populate the states from Asian countries and so on.

So, lets begin with a step by step approach.

Step 1) Create an Analytics data model

For this demo, an Excel file with Region, Country, State and City columns with up to 100 records is used. Analytic data model is built on top of this file. See sample of data in the screen picture shown below:

Sample%20of%20data%20used%20to%20build%20the%20Analytic%20Data%20Model%20for%20this%20demo

Sample of data used to build the Analytic Data Model for this demo

 

Step 2)  Insert required widgets on your canvas, see the image given below

      •               2 panels
      •               4 dropdowns
      •               4 text boxes, and
      •               a table

 

Layout

Layout

 

Step 3)  Define all Script Objects / Variables that will be used in Script Functions and OnSelect() event of dropdown widgets

a) city, state, country and region variables will be Array and Type = DataContext

            DataContext%20Type%20Variables%20to%20store%20ID%20and%20Description%20of%20selected%20value

DataContext Type Variables to store ID and Description of selected member value

 

b) cityColumnName, stateColumnName, countryColumnName and regionColumnName will have Type = String and they are not arrays

String%20variables%20to%20store%20Column%20Names%20for%20repeated%20use

String variables to store Column Names for repeated use

 

c) selectedCity, selectedState, selectedCountry and selectedRegion will have Type = String and they are not arrays

String%20Variables%20to%20store%20dropdown%20value%20selections

String Variables to store dropdown value selections

 

Step 4) Define Script Functions that will be used to write the scripting logic for achieving the cascading filter effect between dropdown widgets and filter results in the table

 

a)     User_Script.initializeData()

 

Definition%20of%20InitializeData%28%29%20Function

Definition of initializeData() Function

 

// create column name variables as they will be reused many times

regionColumnName = "Region";
countryColumnName = "Country";
stateColumnName = "State";
cityColumnName = "City";

// initialize the variables that will hold selected dropdown values at runtime
selectedRegion = "";
selectedCountry = "";
selectedState= "";
selectedCity = "";

// remove all filters that may be applied on the table when application is run for the first time / page load / application initialization
MainTable.getDataSource().removeDimensionFilter(regionColumnName);
MainTable.getDataSource().removeDimensionFilter(countryColumnName);
MainTable.getDataSource().removeDimensionFilter(stateColumnName);
MainTable.getDataSource().removeDimensionFilter(cityColumnName);


//populate FullResultSet variable with full dataset that means ALL data in ALL columns and ALL Rows
var FullResultSet = MainTable.getDataSource().getResultSet();

// define DataContext container / object for each dropdown variable to hold the ID & Description of the selected member
region = ArrayUtils.create(Type.DataContext);
country = ArrayUtils.create(Type.DataContext);
state = ArrayUtils.create(Type.DataContext);
city = ArrayUtils.create(Type.DataContext);

// iterate the FullResultSet to populate the dropdown menu with values of the region, country, state and city columns
for(var i=0;i<FullResultSet.length;i++)
{
	region.push(FullResultSet[i][regionColumnName]);
	country.push(FullResultSet[i][countryColumnName]);
	state.push(FullResultSet[i][stateColumnName]);
	city.push(FullResultSet[i][cityColumnName]);
		
}

// populate each of the dropdowns lists with corresponding values from the columns and append ALL as one of the value

Drop_Down.fillMasterValues(dd_Region,region,"ALL");

Drop_Down.fillMasterValues(dd_Country,country,"ALL");

Drop_Down.fillMasterValues(dd_State,state,"ALL");

Drop_Down.fillMasterValues(dd_City,city,"ALL");

 

b)     User_Script.findApplicableData()

 

Definition%20of%20findApplicableData%28%29%20function

Definition of findApplicableData() function

 

// this function will always hold the filtered member values that will be used to pass filtered values at run-time to dropdowns and table
var applicableData = ArrayUtils.create(Type.DataContext);
var FullResultSet = MainTable.getDataSource().getResultSet();

for(var i=0;i<FullResultSet.length;i++){
	var filter = FullResultSet[i][filterName];
	
	if(applicableFilter===filter.id){
		applicableData.push(FullResultSet[i][columnName]);
	}
}
return applicableData;

 

c)     Drop_Down.fillMasterValues()

 

Definition%20of%20fillMasterValues%28%29%20function

Definition of fillMasterValues() function

 

// this function will populate the dropdowns with all possible values of region, country, state and city columns, also this function will be called during application initialization

dropdownVar.removeAllItems();
dropdownVar.addItem("ALL","ALL");

while(dataContext.length > 0)
{
	var dataContextObj = dataContext.pop();
	dropdownVar.addItem(dataContextObj.id,dataContextObj.description);
	
	if(selectedValue==="")
	{
		dropdownVar.setSelectedKey("ALL");
	}
	else
	{
		dropdownVar.setSelectedKey(selectedValue);
	}
}

return dropdownVar;

 

Step 5) Finally, write script on each of the OnSelect() events of the dropdown widgets to pass the filtered values to table and other dropdowns and achieve the cascading effect

 

a) OnSelect() event of dd_Region

selectedRegion= dd_Region.getSelectedKey();

if(selectedRegion !== "ALL")
{
	MainTable.getDataSource().setDimensionFilter(regionColumnName,selectedRegion);
	
	var applicableDataContextArr = User_Script.findApplicableData(selectedRegion,regionColumnName,countryColumnName);
	dd_Country.removeAllItems();
	Drop_Down.fillMasterValues(dd_Country,applicableDataContextArr,selectedCountry);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedRegion,regionColumnName,stateColumnName);
	dd_State.removeAllItems();
	Drop_Down.fillMasterValues(dd_State,applicableDataContextArr,selectedState);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedRegion,regionColumnName,cityColumnName);
	dd_City.removeAllItems();
	Drop_Down.fillMasterValues(dd_City,applicableDataContextArr,selectedCity);
		
} else {
	
	User_Script.initializeData();
}

 

b) OnSelect() event of dd_Country

selectedCountry= dd_Country.getSelectedKey();

if(selectedCountry !== "ALL")
{
	dd_Country.setSelectedKey(selectedCountry);
	
	MainTable.getDataSource().setDimensionFilter(countryColumnName,selectedCountry);
	
	var applicableDataContextArr = User_Script.findApplicableData(selectedCountry,countryColumnName,regionColumnName);
	dd_Region.removeAllItems();
	Drop_Down.fillMasterValues(dd_Region,applicableDataContextArr,selectedRegion);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedCountry,countryColumnName,stateColumnName);
	dd_State.removeAllItems();
	Drop_Down.fillMasterValues(dd_State,applicableDataContextArr,selectedState);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedCountry,countryColumnName,cityColumnName);
	dd_City.removeAllItems();
	Drop_Down.fillMasterValues(dd_City,applicableDataContextArr,selectedCity);
		
} else {
	
	User_Script.initializeData();
}

 

c) OnSelect() event of dd_State

 

selectedState= dd_State.getSelectedKey();

if(selectedState !== "ALL")
{
	
	MainTable.getDataSource().setDimensionFilter(stateColumnName,selectedState);
	
	var applicableDataContextArr = User_Script.findApplicableData(selectedState,stateColumnName,regionColumnName);
	dd_Region.removeAllItems();
	Drop_Down.fillMasterValues(dd_Region,applicableDataContextArr,selectedRegion);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedState,stateColumnName,countryColumnName);
	dd_Country.removeAllItems();
	Drop_Down.fillMasterValues(dd_Country,applicableDataContextArr,selectedCountry);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedState,stateColumnName,cityColumnName);
	dd_City.removeAllItems();
	Drop_Down.fillMasterValues(dd_City,applicableDataContextArr,selectedCity);
		
} else {
	
	User_Script.initializeData();
}

 

d) OnSelect() event of dd_City

 

selectedCity= dd_City.getSelectedKey();

if(selectedCity !== "ALL")
{
	MainTable.getDataSource().setDimensionFilter(cityColumnName,selectedCity);
	
	var applicableDataContextArr = User_Script.findApplicableData(selectedCity,cityColumnName,regionColumnName);
	dd_Region.removeAllItems();
	Drop_Down.fillMasterValues(dd_Region,applicableDataContextArr,selectedRegion);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedCity,cityColumnName,countryColumnName);
	dd_Country.removeAllItems();
	Drop_Down.fillMasterValues(dd_Country,applicableDataContextArr,selectedCountry);
	
	applicableDataContextArr = User_Script.findApplicableData(selectedCity,cityColumnName,stateColumnName);
	dd_State.removeAllItems();
	Drop_Down.fillMasterValues(dd_State,applicableDataContextArr,selectedState);
		
} else {
	
	User_Script.initializeData();
}

 

Step 6) Call User_Script.initializeData()  function in onInitialization event of the application 

 

Call User_Script.initializeData() in onInitialization event

 

 

Here’s is a video of the working demo of this application for your reference:

 

If all the Steps (1 through 6) are followed correctly, then you will have a perfectly working analytics application that will enable cascading dropdown filters to filter the table widget in your application.

This solution will give you a good starting point and the functionality can be extended further based on your requirements.

It would be interesting to hear from you, if there are any questions, comments etc. A simple Like would be great if this helped in any manner, will surely feel motivated to share more solutions.

 

Note:

1) This solution can be enhanced by including a search box functionality in each of the dropdown filters, see a good reference here

2) If you need to build a sample data file for yourself then you can refer the database of Country>State>City that is available here 

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.