Creating dynamic hyperlinks on Pivot2 tables without updating the data model

These two articles provide options to create hyperlinks (clickable texts) on pivot tables:

Both of these options require creating a custom column in the data model to create the HTML element (e.g. '<a href="' + [Link] + '" target="_blank">' + [Column_Name] + '</a>'), which will then be rendered as hyperlinks on the dashboard. The main drawback with this approach is that when you export the pivot table into a CSV or Excel file, these lines of code will show up as the values, instead of the hyperlinked texts (see screenshots below). Despite being rendered as HTML content on the dashboard, CSV/Excel exports will always display the underlying data, which, in this case, includes the full HTML element.

Data on the widget:

Data on Excel export:

The only way to avoid this issue is to generate the HTML elements on the fly (at query time) rather than storing them in the Elasticube/live model. This can be achieved using one of the widget scripts below.

1. If the text to display is the same as the text that needs to be appended to the URL (e.g. hyperlink is added to the SalesOrderID column, and the value passed to the URL is also from SalesOrderID), use the below script.

Be sure to specify the colIndexToTransform and URLToAppend values.

/***** Add Hyperlinks to Cell Values*****/

//Specify the index of the column to add hyperlinks to, index starts at 0
var colIndexToTransform = 0;

//Specify the URL to append to the cell content which will make up the full web address
var URLToAppend = 'https://www.google.com/search?q=';

widget.transformPivot({
}, handler);

function handler(metadata, cell) {
	
	//If you have more than one level of header, modify the condition (metadata.rowIndex > 0) below to however many level of header you have minus one
	//e.g. if you have three levels of header, change the condition to metadata.rowIndex > 2. This is to make sure hyperlinks are not created for header values
	if(metadata.colIndex == colIndexToTransform && metadata.rowIndex > 0 && !metadata.type.includes('grandtotal') && !metadata.type.includes('subtotal')) {
		cell.contentType = 'html';
		cell.content = `<a href="${URLToAppend}${cell.content}" target="_blank" rel="noopener noreferrer">${cell.content}</a>`;
	}
}

/***** End of Script *****/

In this example, URLs are added to the SalesOrderID column. Each URL has the SalesOrder value appended to it (e.g. https://www.google.com/search?q=43701).

Since the underlying data do not contain HTML elements, only the actual values are displayed when the table is exported to CSV/Excel.

2. If the text to display is NOT the same as the text that needs to be appended to the URL (e.g. hyperlink is added to the CategoryName column, but the value passed to the URL is from CategoryID), use the below script.

Be sure to specify the colIndexToTransform, colIndexForURL, and URLToAppend values.

/***** Add Hyperlinks to Cell Values*****/

//Specify the index of the column to add hyperlinks to, index starts at 0
var colIndexToTransform = 1;

//Specify the index of the column whose value will be appended to the URL, index starts at 0
var colIndexForURL = 0;

//Specify the URL to append to the cell content which will make up the full web address
var URLToAppend = 'https://www.google.com/search?q=';

widget.transformPivot({
}, handler);

var dashboard_id = [];
var dashboard_id_index = 0;

function handler(metadata, cell) {

	if(metadata.colIndex == colIndexForURL && metadata.rowIndex > 0 && !metadata.type.includes('grandtotal') && !metadata.type.includes('subtotal')) {
		dashboard_id[dashboard_id_index] = cell.content;
		dashboard_id_index = dashboard_id_index + 1;
	}

	//If you have more than one level of header, modify the condition (metadata.rowIndex > 0) below to however many level of header you have minus one
	//e.g. if you have three levels of header, change the condition to metadata.rowIndex > 2. This is to make sure hyperlinks are not created for header values
	if(metadata.colIndex == colIndexToTransform && metadata.rowIndex > 0 && !metadata.type.includes('grandtotal') && !metadata.type.includes('subtotal')) {
		cell.contentType = 'html';
		cell.content = `<a href="${URLToAppend}${dashboard_id[dashboard_id_index-1]}" target="_blank" rel="noopener noreferrer">${cell.content}</a>`;
	}
}

/***** End of Script *****/

In this example, URLs are added to the CategoryName column. However, each URL has the CategoryID value appended to it (e.g. https://www.google.com/search?q=3), not the category name.