How to Change the Pivot Header Row Style with TransformPivot
How to Change the Pivot Header Row Style with TransformPivot This solution is based on Pivot 2.0 API supported in Sisense version L8.2.1 or later (not supported in Sisense for Windows). For this solution, we shall use cellStyle from Pivot 2.0 API. The following options are available to adjust the cell style: Name Type Description fontSize number or string Text size fontWeight number or string Text weight fontStyle string Text style lineHeight string Text line height textAlign string Text alignment: 'left', 'right' or 'center' color string Text color backgroundColor string Cell background color padding number or string Cell padding borderWidth number Cell border width borderColor string Cell border color (with CSS fallback) Below I provide a widget script that identifies the first row in the pivot widget and utilizes transformPivot to replace the existing cell style with the new backgroundColor, color, and textAlign. Other cellStyle parameters can be set as in the example script. widget.transformPivot({}, (metadata, cell) => { if (metadata.rowIndex === 0) { cell.style = cell.style || {}; cell.style.backgroundColor = '#0057B7'; //sets cell background color cell.style.color = '#FFDD00'; //sets cell text color cell.style.textAlign = 'right'; //sets cell text align }; } ); To utilize the script, open the pivot widget in edit mode, use the three dots menu, and choose "Edit Script". Paste the script, save the changes, and refresh the dashboard. Enjoy the result! Disclaimer: Please note, that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their own environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you “as-is” and without warranty of any kind, express, implied or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding which is outside the Sisense product development environment and is therefore not covered by not covered by Sisense warranty and support services.Using HTML for Style and Formatting in Pivots
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: The Notes (and any subsequent columns) are duplicated, once for each Status 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.
