Dynamic Pivot Table's Column Header Based on Dashboard Filter

Summary: Tri Anthony describes a script designed to dynamically change a pivot table's column header based on a dashboard filter selection. The script supports both direct use of filter values and custom label mapping through a dictionary, with configurations for fallback scenarios when filters are disabled or not matching. The detailed instructions explain how to implement and configure the script, highlighting its limitations and offering a disclaimer regarding its applicability across different environments or software versions.

Overview

This widget script changes a specific column header in a pivot table in response to a dashboard filter selection, making that column more descriptive and context-aware. For example, a "Sales" column header can automatically become "Sales for U.S." when the Country filter is set to United States.

The script supports:

Using the filter's selected value directly as part of the column header (append mode)
Mapping the filter's selected value to a custom label via a lookup dictionary (lookup mode)
A configurable fallback header for when the filter is disabled, set to Include All, or has multiple values selected
A configurable fallback for filter values that have no entry in the lookup dictionary

This can be useful for:

Making a pivot table's column header reflect exactly what's being measured under the current filter selection, instead of a generic static label
Aligning displayed column headers with business-friendly terminology instead of raw filter values

Implementation

The script reads the current value of a specified dashboard filter and applies a corresponding header to a specific column in a pivot table, on every query.

Script

/*** Enter your inputs for the nine variables below ***/

// 1. Specify the name of the dashboard filter based on which the column title will be renamed
var dashboardFilterName = "Country";

// 2. Specify the panel index where the column to be dynamically renamed is in, i.e. Rows panel = 0, Values panel = 1
var panelIndex = 1;

// 3. Specify the index of the column to be dynamically renamed in the specified panel, index starts at 0
var columnIndex = 0;

// 4. Specify the static column title for when the dashboard filter is Include All, disabled, or not valid for the script (e.g. multiple values are selected)
var fallbackColumnTitle = 'Sales';

// 5. (Optional) Specify the static prefix for the column title
var columnTitlePrefix = 'Sales for ';

// 6. Choose how the column title is derived from the filter value:
// 'append' -> use the filter value directly (e.g. "United States")
// 'lookup' -> map the filter value through titleMap (e.g. "United States" -> "US")
var titleMode = 'lookup';

// 7. When using titleMode = 'lookup', specify the dictionary of the values
var titleMap = {
	'United States': 'U.S.',
	'United Kingdom': 'Great Britain',
	'Canada': 'CA',
	'Mexico': 'MEX'
	// add more mappings as needed
};

// 8. Choose how the column title is derived if there's no entry in the titleMap dictionary for the selected filter value:
// 'raw' -> use the raw filter value (e.g. "Australia" if the filter is set to Australia and there is no entry for Australia in the titleMap)
// 'static' -> use a static placeholder value (e.g. "Other")
var titleNotFoundMode = 'static';

// 9. When using titleNotFoundMode = 'static', specify the static column title
var titleNotFoundModeColumnTitle = 'Other';


/*** The script below applies the dynamic column titles ***/

widget.on('beforequery', function(w, e) {
	
	// find the filter object
	var filterItem = dashboard.filters.$$items.find(function(item) {
		return item && item.jaql && item.jaql.title === dashboardFilterName;
	});
	if (!filterItem) return;
	
	// determine the filter value to be displayed
	var filterValue = (!filterItem.disabled && filterItem.jaql.filter.members && filterItem.jaql.filter.members.length === 1)
	? filterItem.jaql.filter.members[0]
	: fallbackColumnTitle;

	// determine the column title dynamic suffix based on the selected mode
	var suffix;
	if (titleMode === 'lookup') {
		if (titleMap.hasOwnProperty(filterValue)) {
			suffix = titleMap[filterValue];
		} else {
			suffix = (titleNotFoundMode === 'static') ? titleNotFoundModeColumnTitle : filterValue;
		}
	} else {
		suffix = filterValue;
	}
	
	// determine the final column title
	var newTitle;
	if (filterValue === fallbackColumnTitle) {
		newTitle = filterValue
	} else {
		newTitle = columnTitlePrefix + suffix;
	}
	
	// find the filter object's dim
	var targetDim = widget.metadata.panels[panelIndex].items[columnIndex].jaql.dim;
	
	// apply the dynamic column title
	
	// 1) Update the persisted metadata
	/** Uncomment the two lines below if you want to store the updated title permanently into the widget metadata. This is most likely not necessary. **/
	// widget.metadata.panels[panelIndex].items[columnIndex].jaql.title = newTitle;
	//widget.changesMade('widgetscript', ['metadata']);

	// 2) Update only the matching item in the live outgoing query
	e.query.metadata.forEach(function(item) {
		if (item.jaql && item.jaql.dim === targetDim) {
			item.jaql.title = newTitle;
		}
	});
});

Configuration

The script behavior is controlled by nine configurable variables at the top of the code, covering which dashboard filter to monitor, which column to target, and how the resulting header text is constructed:

dashboardFilterName : Name of the dashboard filter used to determine the column header
panelIndex : Index of the widget's panel containing the target column (e.g., Rows panel = 0, Values panel = 1)
columnIndex : Index of the column within the specified panel (index starts at 0)
fallbackColumnTitle : Static header used when the filter is set to Include All, disabled, or has multiple values selected
columnTitlePrefix : Static text prepended to the filter-derived value
titleMode :
- 'append' to use the filter value directly, or
- 'lookup' to map it through titleMap
titleMap : Dictionary mapping filter values to custom header values (used when titleMode is set to 'lookup')
titleNotFoundMode :
- 'raw' to fall back to the raw filter value, or
- 'static' to use a static value, when a filter value has no entry in titleMap
titleNotFoundModeColumnTitle : Static header used when titleNotFoundMode is set to 'static'

Steps

Copy the script above.
Open the widget's script editor from the widget menu.
Paste the script into the editor.
Update the nine configuration variables at the top of the script to match your filter name, panel/column indices, and desired header behavior.
Save the script.
Refresh the widget and test by changing the dashboard filter's selected value.

Limitations

Applies to a specific column in a specific pivot table panel. It does not dynamically detect the column.
Only applicable to pivot tables.
Requires a dashboard filter to be specified by name. If the filter isn't found, no header change is applied.
Dynamic column headers are applied to the outgoing query only and are not persisted by default (see the commented-out metadata update lines in the script if persistence is desired).
When multiple filter values are selected, the script falls back to fallbackColumnTitle rather than combining values.
Lookup-based headers require mappings to be manually maintained in titleMap. Values without a mapping fall back per titleNotFoundMode.

Disclaimer

This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.

Tri Anthony

Posted Sep 17, 2026 at 9:02 PM

0 comments