Description

The Pivot2 Styler add-on enables you to hide columns and customize the style of Pivot2 widgets. Hiding Pivot2 widget columns can be used for advanced sorting.

The Pivot2 Styler add-on is available for Sisense for Linux version L2021.3.1.

Installing the Pivot2 Styler Add-on

To install the Pivot2 add-on:

Download the add-on.
Extract the ZIP file to the plugins folder. If the folder doesn't exist, create it:
- Linux: /opt/sisense/storage/plugins
- Windows: C:/Program Files/Sisense/app/plugins

Note: For multi-node deployments, install the Pivot2 Styler add-on on each build node.

Configuring the Pivot2 Styler Add-on

Configure this add-on using:

The plugin configuration file (config.6.js). This file is located inside the Pivot2Styler plugin folder.
Widget script configurations, which are merged with the config file and will only affect the relevant widget

Note: Customizations defined in the widget script take priority over config file customizations.

Custom Style Object

Pivot2 Style configurations are applied as a hierarchy. That is, the more specific styles override their parent definitions:

Default general style (general): This is the default style for cells with no panel or index style applied
Default style per panel (rows/columns/values): This is the default style for all fields in the panel with no index style
Index field style (for example, rows.byIndex.0): This is the default style per field by the field index

Custom Style Object Structure

{

    "global": {

        "headers": {

            "backgroundColor": '#ff9999'

        },

        "cells": {

            "backgroundColor": '#9999ff'

        },
      "grandTotal": {
       "headers": {
           "backgroundColor": '#8a99ff'
        },
        "cell": {
          "backgroundColor": '#9ed222'
    }
      }
    },

    "rows": {

        "headers": {

            "backgroundColor": '#ff99aa'

        },

        "cells": {

            "backgroundColor": '#ffff99'

        },

        "byIndex": {

            "0": {

                "headers": {

                    "backgroundColor": '#ff99ff'

                },
"cells": {

                 "backgroundColor": '#404040'

             }

            }

        }

    },

    "columns": {...},

    "values": {...},

    "hiddenRowsByIndex": [0, 1],

    "hiddenValuesByIndex": [2, 3]

}

Object Properties

global: Affects pivot cells that do not have any other specific customizations:
headers: Style object**
cells: Style object**
grantTotal
headers Style object**
cells Style Object**
rows: Customize the row fields style using the Cell Groups object*
columns: Customize column field styles using the Cell Groups object*. There is no meaning for comumn cells
values: Customize value field styles using the Cell Groups object*
hiddenRowsByIndex: An array of numbers that indexes the row fields to hide
hiddenValuesByIndex: An array of numbers that indexes the value fields to hide

* Cell Groups object

headers: Style object** that affects header cells
cells: Style object** that affect cells that are not headers
grantTotal object can be applied to only rows and columns.
title string used as the new title of the grand total.
headers Style Object**
cells Style Object**
byIndex object: The keys are field indexes (the position in the widget metadata panel) and the values are Style objects**. This option has the highest priority and overrides the properties from both the global and rows/column/values. Field numbering is based on the panel it's related to, where the first item is index 0. Disabled items are taken into account. For example: A widget has three items in the Rows panel: Brand, Country (disabled), and Category. The index of the Category field is 2.

**Style object: is an object, key value pairs of camelcase CSS rules and their values.

In addition to the CSS style option, you can add the key:
“isURL”: true/false
When set to true, the cell content is converted to URL.
The field content in the cube can be in one of the following formats:

myDomain.com URL without the protocol
A “https://www.” prefix is added to the text so the href is “https://www.myDomain.com”.
protocol://myDomain.com
URL with the protocol
<a href=”protocol://myDomain.com”>My Domain</a> HTML “a” tag

In case no HTML is specified, the target attribute is set to _black so the link is opened in a new tab, see Example #4.

Make sure to:

Specify valid CSS rule names
Convert CSS rule names to camelcase format. For example, border-right-color converts to borderRightColor
Specifgy valid values for CSS rules
Update the configurations if the metadata field order has changed

Apply Custom Style Using a Widget Script

To apply a custom style using a widget script:

open /plugins/pivot2Styler/config.6.js
Update the options object: const options = customStyleObject;

Example 1

{

     "global": {

        "headers": {

           "backgroundColor": '#ffc0cb'

        },

        "cells":{

           "backgroundColor": '#9dd1fe'

        }

     }

}

Expected Result:

The header cells of row, column, and value fields have a background colored in pink #ffc0cb
The value cells of the row and value fields have a background colored in light blue #9dd1fe

Example 2

{

    "global": {

       "headers": {

          "backgroundColor": "#ffc0cb"

       },

       "cells":{

          "backgroundColor": "#9dd1fe"

       }

    },

    "rows":{

       "headers": {

          "backgroundColor": "#ffffce",

          "fontWeight": "bold"

       }

    },

    "values":{

       "cells": {

          "fontSize": "18px",

          "backgroundColor": "#97f6d7",

          "color": "#614ae1"

       }

    },

    "hiddenRowsByIndex": [0],

    "hiddenValuesByIndex": [1]

};

Expected Result:

The rows field header cells are in bold with a yellow #ffffce background (affected by the rows.headers and overriding the global.headers background color
The rows field value cells have a light blue #9dd1fe background (affected by global.cells)
The columns field header cells have a pink #ffc0cb background (affected by global.headers)
The value field header cells have a pink #ffc0cb background (affected by global.headers)
The value field value cells have 18px font size with a light green #97f6d7 background (overriding global.cells) and a blue #614ae1 font color
The Country column is hidden
Each Total Revenue column from a subset of Age Range values is hidden

Example 3

{

    "global": {

        "headers": {

            "fontWeight": "bold",

            "fontSize": "20px",

            "borderColor": "black",

            "borderWidth": "1px"

        },

        "cells": {

            "fontSize": "18px",

            "borderColor": "black",

            "borderWidth": "1px"

        }

    },

    "rows": {

        "headers": {

            "backgroundColor": "#ffffce",

            "fontWeight": "normal"

        },

        "cells": {

             "backgroundColor": "#9dd1fe"

        }

    },

    "columns": {

        "headers": {

            "backgroundColor": "#ffc0cb"

        },

        "byIndex": {

            "1": {

                "headers": {

                    "backgroundColor": "#fff170"

                }

            }

        }

    },

    "values": {

        "cells": {

            "fontSize": "20px",

            "backgroundColor": "#97f6d7",

            "color": "#999967"

        },

        "byIndex": {

            "0": {

                "headers": {

                    "backgroundColor": "#b37dee",

                    "fontSize": "24px"

                },

                "cells": {

                    "backgroundColor": "#59b9b9",

                    "color": "#ededa1",

                    "fontWeight": "bold",

                    "borderColor": "#b185ff",

                    "borderWidth": "2px"

                }

            }

        }

    }

};

The rows field header cells have a normal font weight (overriding global.headers.fontWeight), a yellow #ffffce background, and a black, 1px thick border (affected by the global.headers style)
The row field value cells have a light blue #9dd1fe background with a black, 1px thick border and 18px font size
The columns field header cells have a pink #ffc0cb background with a bold, 20px size font. The border is black and is 1px thick
The column field with index 1 has a header cell with a yellow #fff170 background with bold, 18px size. The border is black and is 1px thick
The values field header cells are affected by the global.headers style
The values field value cells have a light green #97f6d7 background, a dark yellow #ffffce font color, and 20px font size (overrides global.cells.fontSize)
For the value field with index 0:
Header style: The background color is light purple #b37dee and the font size is 24px (overrides global.headers.fontSize)
Value style: The background color is dark green #59b9b9, 20px bold font (affected by values.cells.fontSize), and a yellow #ededa1 font color. The border is light purple #b185ff and is 1px thick

Example 4

{
    "rows": {
        "byIndex": {
            "0": {
                "isUrl": true
            }        
   }
    }
}

Expected outcome

The row with index 0 will be presented as a link.
For example, if the cell value is “google.com”, the href attribute will change to https://www.google.com, and the target attribute will be “ _blank”.
The page is opened in a new tab with the presented text as “google.com”.

Implementing the Pivot2 Styler Add-on

Custom styles are applied using one of the two following methods:

Pivot2 API Cell styles: Affects specific pivot cells (based on the configuration) using the Pivot2 APIs, customization applied before the widget is rendered in the Sisense UI
Dom manipulations: Manipulating Pivot2 dom elements to apply CSS styles directly to a specific cell. Changes are applied after the widget is rendered

Limitations

Subtotal and Grand Total cell customizations are not supported
Some CSS rules can cause unpredictable UI changes or behavior changes to the Pivot2 widget
After the widget script is updated, you need to refresh the page to apply changes
Pivot2 widgets that have exactly 1 Values item and one or more Column items are not fully supported. Row cells are customized properly, but Values and Column cells are not
Exporting the Pivot2 widget to CSV, Excel, or an image is not supported
Highlighting and coloring fields are overridden by the add-on
If there are hidden fields, disabling and enabling items in the Pivot2 widget edit mode is not supported
After duplicating a Pivot2 widget, you need to refresh the page to apply all style changes
No support for search and customization of cells with Subtotals.
Grand Total for Columns panel support
No support for a widget with a column grand total enabled that has more than one value item.
Grand Total styling
The bottom right cell is affected by the columns.grandTotal.cells style. In case that property is not defined, the bottom right cell is affected by the rows.grandTotal.cells style.
Widget full screen mode and widget edit mode will not reflect style changes upon panel item updates, like disable/enable items.