Implement cascading filter effect using DropDown widget

[PrathameshA]

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 

[jrodrig4]

Dear paacharya,

thanks for this amazing method to achieve the cascade filters. I have created something similar using this blog and it is working properly.

 

I have a question though: cityColumnName, stateColumnName, countryColumnName and regionColumnName have Type = String and they are not arrays. You mention in the picture caption: String variables to store Column Names for repeated use

Not sure if I understand properly about "repeated use". What do you mean?

 

Thanks and congrats for this blog.

[PrathameshA]

Hello jrodrig4

It is good to know that the content helped you in some way.

The columnName variables are used many times in the OnSelect() event of the four drop-down widgets. Hence I referred to them as ‘repeated use’.

Hope that answers your question.

Thanks,

Prathamesh

 

[jrodrig4]

thanks a lot again Prathamesh. Your answer is clear.

 

Regards,

Javier