Enable/Disable Pivot columns based on selected filter

Sometimes we need to hide or show few columns in pivot table based on a filter selected. Here is a script to achieve this.

Reference: https://www.binextlevel.com/post/enable-disable-pivot-columns-based-on-selected-filter

Steps:

1. Create a Pivot table

2. Add panels to enable/disable based on selected filter and disable them. It will enable dynamically based on selection.

3. Add below script to widget

4. Update the variable 'ItemList' with mapping of filter item to name of panels to be enabled. Also update the variable 'filterName' with name of dashboard filter based on which the columns enable/disable

5. Save the script and refresh dashboard.

 

 

 

let filterName = 'Region'

let ItemList = { 
	'South':['County','Gender', 'City'], 
	'Northeast':['Gender', 'State'],
	'Default':['City', 'State']
} 

widget.on("buildquery", function (se, ev) { 
	let filter = se.dashboard.filters.$$items.find(el=>el.jaql.title == filterName) 
	let filterItem = filter.jaql.filter.members[0] 
	let columnList = ItemList[filterItem] 
	
	if(columnList == null || columnList == undefined)
		columnList = ItemList['Default']
	
	$.each(ev.widget.metadata.panels[0].items, function(index, value){ 
		if(value.disabled == true && columnList.includes(value.jaql.title) ) 
		{ 
			var newJaql = { 
				jaql  : JSON.parse(JSON.stringify(value.jaql)) 
			} 
			ev.query.metadata.push(newJaql) 
			lastIndex = ev.query.metadata.length - 1 
			ev.query.metadata[lastIndex].disabled = false	 
			ev.query.metadata[lastIndex].panel = 'rows'
		} 
	}) 
	$.each(ev.widget.metadata.panels[1].items, function(index, value){ 
		if(value.disabled == true && columnList.includes(value.jaql.title) ) 
		{ 
			var newJaql = { 
				jaql  : JSON.parse(JSON.stringify(value.jaql)) 
			} 
			ev.query.metadata.push(newJaql) 
			lastIndex = ev.query.metadata.length - 1 
			ev.query.metadata[lastIndex].disabled = false	 
			ev.query.metadata[lastIndex].panel = 'measures' 
		} 
	}) 

})

 

 

-Hari