Using HTML for Style and Formatting in Pivots

[akaplan]

Using HTML for Style and Formatting in Pivots

Introduction

A common use case within Pivot and Table widgets is to display a clickable hyperlink (see: this community post).  However, the ability to render HTML within these widgets can be used for more than just links.  Any HTML can be rendered, which enables a variety of styling and formatting options.

In this post, we'll walk through an example project management use case in Sisense, which includes a Pivot containing project status information.  In this example, each project has three health statuses: Customer, Technical, and Timeline.  Each one exists as a separate column, with a widget script to color-code the statuses:

Three Separate Status Columns

 

widget.transformPivot({},
    function(metadata, cell) {
        if (cell.value == 'Red') {
            cell.style = {
                color: 'red'
            }
        } else if (cell.value == 'Green') {
            cell.style = {
                color: 'green'
            }
        } else if (cell.value == 'Yellow') {
            cell.style = {
                color: 'yellow'
            }
        };
    }
);

 

Problem

The product team has requested we combine the three statuses into a single column.

Solution 1

One option is to create a dimension table in our data model that normalizes the three statuses into key/value pairs of StatusType and StatusValue, then create a concatenation of {StatusType}: {StatusValue}.  The Pivot output might look something like this:

Concatenated Key/Value Status Column

You can see that there are two drawbacks:

1. The Notes (and any subsequent columns) are duplicated, once for each Status
2. We’ve lost the color coding and would need to elevate the complexity of our widget script to account for this.

Solution 2

A second option, which is the subject of this article, is to use a combination of SQL and HTML to produce the combined Status column, which eliminates the duplication issue above (#1) and arguably makes our styling implementation much simpler (#2).

Rendered HTML Status Column

Here is an example of the SQL/HTML within the data model's custom column, which produces the HTML that is rendered in the Pivot.

'<b><p style="font-size:12px">Customer: ' + 
	CASE 
		WHEN CustomerStatus LIKE 'Red%' THEN '<font color="red">' 
		WHEN CustomerStatus LIKE 'Yellow%' THEN '<font color="yellow">' 
		WHEN CustomerStatus LIKE 'Green%' THEN '<font color="green">' 
	ELSE '<font color = #black' END + CustomerStatus + '</font><br>' +

'Technical: ' + 
	CASE 
		WHEN TechnicalStatus LIKE 'Red%' THEN '<font color="red">' 
		WHEN TechnicalStatus LIKE 'Yellow%' THEN '<font color="yellow">' 
		WHEN TechnicalStatus LIKE 'Green%' THEN '<font color="green">' 
		ELSE '<font color = black' END + TechnicalStatus + '</font><br>' +

'Timeline: ' + 
	CASE 
		WHEN TimelineStatus LIKE 'Red%' THEN '<font color="red">' 
		WHEN TimelineStatus LIKE 'Yellow%' THEN '<font color="yellow">' 
		WHEN TimelineStatus LIKE 'Green%' THEN '<font color="green">' 
	ELSE '<font color = black' END + TimelineStatus + '</font></p></b>'

Reminder: The configuration option Allows rendering Pivot Table content as HTML must be enabled within the Admin menu.  The link at the beginning of this post describes the process in more detail.

Keep the size of the table and text field in mind, to avoid negatively impacting build or query performance.

This is just one small example of employing HTML for a stylistic use case, as opposed to a clickable hyperlink.