In analytics applications, there is usually the requirement to filter the dataset with 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.

Following is a step by step approach to implement the same:

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 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

Step 3)  Define script objects and variables to be used in script functions and OnSelect() event of dropdown widgets

a) city, state, country and region variables are an Array and Type = DataContext

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

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

String variables to store Column Names for repeated use

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

String Variables to store dropdown value selections

Step 4) Define script functions to write the logic for achieving the cascading filter effect between dropdown widgets and filter dataset in the table

a)     User_Script.initializeData()

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 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 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 dropdown widgets

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