cancel
Showing results for 
Search instead for 
Did you mean: 
TriAnthony
Sisense Team Member
Sisense Team Member

Dashboard Script for Automatic Filter Cascading

This dashboard script below can be used to synchronize the selections of a set of filters. For example, if the first filter is set to ABC, all subsequent filters listed will automatically be updated to ABC as well. The script also replicates the disabled state from the first filter to the other filters. look below for an example of Elasticube and dashboard. Please note: Our community website does not currently support .dash (dashboard) and .smodel (Elasticube) files. Please change the extensions of the files before importing them into your environment. Note that once imported, the cube still needs to be built.

Example Use Case: When dealing with multiple date fields in a fact table, this article provides two options, both of which require duplicating all rows in the fact table as many times as the number of date fields. For example, if you have three date fields that you need to analyze your KPIs by, you'll have to have three copies of the fact table. While these approaches are easy to implement, oftentimes they are not feasible and scalable solutions due to the size of the data. A more scalable alternative is to duplicate the date dimension table, which is substantially smaller than a fact table (e.g. a date dimension table containing 10 years of data only has either 3,652 or 3,653 rows), then connect each date field from the fact to the corresponding date dimension table. See the picture below for an example.

 

TriAnthony_0-1702322218860.png


The next step is to add each date field as a filter to the dashboard. Keep only the first filter editable and lock the other filters. The subsequent filters will be updated automatically as users set the first filter.

 

TriAnthony_0-1702325930661.png


Ensure that the filters are correctly toggled on/off in each widget's setting. In the example above, the first widget shows the number of hospital admissions by the admission date. Therefore, only the first (Admission Date) filter should be turned on.

 

TriAnthony_2-1702323035685.png


The last step is to add the dashboard script that automatically cascades the selection of the first filter to the subsequent filters. In the filterNames variable, specify the names of the filters, starting from the first filter, from which the selections will be replicated to other filters. This is the only part of the code that requires your input.

 

 

/*************************** This script is used to synchronize the selections of a set of filters ***********************/
/** E.g. if the first filter is set to ABC, all subsequent filters listed will automatically be updated to ABC as well ***/
/***************** The script also replicates the disabled state from the first filter to the other filters **************/

//Specify the names of the filters, starting from the first filter, from which the selections will be replicated to other filters
//This is the only part of the code that requires your input
var filterNames = ['Admission Date', 'Discharge Date', 'Last Visit Date']

//Every time a filter is changed, this code is executed
dashboard.on('filterschanged',function(d) {

	//Find the first filter by name
	var filter1FilterObject = dashboard.filters.$$items.find((item) => {
		if (item.jaql && item.jaql.title.indexOf(filterNames[0]) !== -1) {
			return true;
		}
	});

	//Get the JAQL filter selection of the first filter
	var filter1FilterObjectJAQL = filter1FilterObject.jaql;

	//Get the JAQL filter disabled state of the first filter
	var filter1FilterObjectDisabled = filter1FilterObject.disabled;

	//Define an array for the subsequent filters' objects
	var filterObjects = new Array();

	//Find each of the subsequent filters by name
	for(i=1 ; i<filterNames.length ; i++) {

		filterObjects[i-1] = dashboard.filters.$$items.find((item) => {
			if (item.jaql && item.jaql.title.indexOf(filterNames[i]) !== -1) {
				return true;
			}
		});

	}

	//Update the properties of the subsequent filters to match the first filter
	for(i=0 ; i<filterObjects.length ; i++) {

		if(typeof filterObjects[i] != 'undefined') {
			filterObjects[i].jaql.filter = filter1FilterObjectJAQL.filter;
			filterObjects[i].disabled = filter1FilterObjectDisabled;
		}
		
		if(filter1FilterObjectJAQL.datatype == 'datetime') {
			filterObjects[i].jaql.level = filter1FilterObjectJAQL.level;
		}

	}

	//Refresh the dashboard
	dashboard.refresh();

});

 

Rate this article:
Comments
dougnewton
10 - ETL
10 - ETL

Yes!  I was just running into this problem the other day.  (Synchronizing dimension filters.)  Thank you for sharing it.

Sisense really should make this a feature of the platform -- not every environment is comfortable with writing/applying code to solve a problem.

Version history
Last update:
‎12-12-2023 09:52 AM
Updated by:
Contributors