Turning zeroes to blanks/empty cell when Zero
Symptoms Sometimes with a pivot table full of many columns with zero and only a few non-zero values, you want the zeroes to display as empty-string. That way the nonzero values really stand out. (This is the opposite of this: Solved: nulls to 0 in pivot - Sisense Community ) Below is a widget-level script suggested by tech support which worked for me. Solution widget.transformPivot( { type: ['value'] }, function setCellBackground(metadata, cell) { if(cell.content.trim() == 0) cell.content = ' ' } )Converting an Existing Sisense Widget to a Dynamic ComposeSDK Widget Component
Using either the widget Sisense API's, or the Sisense widget JS API object, it is possible to determine all components and parameters of a widget required to create an equivalent dynamic ComposeSDK widget component. This approach allows the extraction of all components and parameters of a widget, enabling the creation of an equivalent dynamic ComposeSDK widget component without directly referencing or relying on a specific widget or dashboard ID. The metadata of an existing widget contains all the information needed to create a dynamic version of an existing Sisense widget. It is also possible to use an existing widget ID and dashboard ID to render an existing widget in ComposeSDK, but this does not take full advantage of the capabilities of ComposeSDK to generate new widgets directly.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.
