# Pivot 2.0 API

The Pivot 2 widget type provides a Javascript API that allows targeted manipulation of the look and behavior of the Pivot widget. This API can be used in widget scripts, dashboard scripts and Add-ons.

When a Pivot 2 type widget is accessed via a widget script or event, the widget object will contain the methods listed below.

Feature Availability

  • This API is available in Sisense version L8.2.1 or later.
  • This API is available only for Pivot 2.0 type widgets.
  • The Pivot 2.0 widget and its API are currently only available on Linux versions of Sisense.

# Methods

# transformPivot

widget.transformPivot(target, handler, options)

Allows transforming individual cells of a Pivot 2 widget. The method accepts a target object defining which cells should be affected, and a handler function that performs the transformation itself.

This method will only be present on the widget object when the widget is of the Pivot 2 type. For all other widgets, widget.transformPivot will be null.

The method may be called several times on the same Pivot 2 widget in order to register multiple transformations on the same pivot table.

Arguments

Name Type Required Description
target TransformationTarget Yes An object describing which cells should be transformed
handler Handler Function Yes A function that transforms each targeted cell
options object No Additional options
options.pluginKey string No Unique handler key for use with anonymous function 1

Notes:

1 options.pluginKey is a unique string that should be provided when handler is an anonymous function, in order to ensure it is executed correctly.

Returns

N/A

Example

// Give all value cells a background color - example with a named function
widget.transformPivot(
    {
        type: ['value']
    },
    function setCellBackground(metadata, cell) {
        cell.style = cell.style || {};
        cell.style.backgroundColor = '#9A94BC';
    }
);

// Give all member cells a background color - example with an anonymous function
widget.transformPivot(
    {
        type: ['value']
    },
    function (metadata, cell) {
        cell.style = cell.style || {};
        cell.style.backgroundColor = '#9A94BC';
    },
    {
        pluginKey: 'set-cell-background-1'
    }
);

# configurePivot

widget.configurePivot(configuration)

Defines a Pivot 2 widget's configuration, which will be applied to the entire pivot table and not to specific cells.

Arguments

Name Type Required Description
configuration PivotConfiguration Yes An object defining configuration for the widget

Returns

N/A

Example

// Disable drill-down
widget.configurePivot({
    disableDrill: true,
});

# sortPivot L2021.10

widget.sortPivot(sortObjects, persist)

Defines the sorting logic for the pivot, to change how the data is shown.

Multiple SortObject can be provided, to sort based on muiltiple columns.

Note that each SortObject must be distinct, and the same measure/value/column must not be targeted by more than one SortObject.

Arguments

Name Type Required Description
sortObjects Array<SortObject> Yes Array of sort objects
persist boolean No Should sorting applied by this function be persisted

Returns

N/A

Example

// Define a sorting rule
const sortConfig = {
    target: {
        type: 'row',
        title: 'Source'
    },
    direction: 'desc'
};
// Apply sorting without persisting
widget.sortPivot([sortConfig], false);

Note

Sometimes, a Pivot table can behave like a regular (flat) table - that is, each top-level element in the row (parent) has only one direct child, also called a "single branch tree" pivot.

This happens when the order of fields used for the Pivot's rows panel is such that no grouping happens. For example:

  • If the rows panel contains Years and then Months, each "year" element contains multiple "months" element, and the months are grouped by year, yielding regular pivot behavior.
  • If the rows panel is reversed (Months and then Years), each row will represent a single specific month resulting in a flat table or a "single branch tree" layout.

These pivots look like this:

Single branch tree

In Pivot widgets with this flat or "single branch tree" structure, only one sorting configuration can be used.

This is because if multiple configurations are applied to the different fields in the rows panel, they will overlap (conflict with) each other.

Please keep this in mind in order to avoid unexpected pivot sorting behavior.

# Types

# TransformationTarget

Defines which cells should be affected by the transformPivot method.

The targeting object MUST contain 1 or more of the supported filtering properties listed below.

Note: The terms "rows", "columns" and "values" refer to the widget's metadata panels and not to the physical rows and columns of the resulting pivot!

Combining targets

When multiple targets are combined, the relationship between them will be AND:

// Apply transformation to any cell that is of "value" type AND where the Country dimension value equals USA or Canada
{
    type: ['value'],
    rows: [
        {
            dim: '[Customer.Country]',
            members: ['USA', 'Canada']
        }
    ]
}

Properties

Name Type Description Supported Values
type string[] Cell type(s) to target member, value, subtotal, grandtotal
rows DimensionalTarget[] Rows to target See DimensionalTarget
columns DimensionalTarget[] Columns to target See DimensionalTarget
values ValueTarget[] Values to target See ValueTarget

Example

// Full possible target model with example content
const target = {
    type: ['value', 'member', 'subtotal', 'grandtotal'],
    rows: [
        {
            title: 'Country',
        }
    ],
    columns: [
        {
            index: [1, 2]
        }
    ],
    values: [
        {
            dim: '[Sales.Revenue]',
            agg: 'sum'
        }
    ]
}

# DimensionalTarget

The object used in TransformationTarget's rows and columns properties.

The object corresponds to the Rows or Columns metadata panels in the Pivot.

Allows specifying which rows or columns should be targeted for transformation. The object corresponds to the Rows or Columns metadata panels in the Pivot.

There are 3 types of row/column targeting objects:

  • Index target
  • Member target
  • Title target

# Target By Index

Target a zero-based index of the metadata collection.

I.e. if the pivot has 3 metadata items in the "Rows" panel, an index 1 will target all cells that are related to the middle metadata item.

Properties

Name Type Description
index integer[] List of row (column) indices to target

Example

// Target rows (or columns) 1,2,3 of the pivot
{
    index: [1, 2, 3]
}

# Target By Member

Target cells corresponding to a specific dimension value (member)

Properties

Name Type Description
dim string Dimension name
members *[] Array of Members to apply to

Example

// Target rows (or columns) where the "Country" dimension is "USA" or "Canada"
{
    dim: '[Customer.Country]',
    members: ['USA', 'Canada']
}

# Target By Title

Target cells corresponding to a metadata item with the given title

Properties

Name Type Description
title string Metadata item title - should match the title in the JAQL, before translation

Example

// Target all cells that belong to a metadata "rows" or "columns" item with the title 'Country'
{
    title: 'Country'
}

# ValueTarget

The object used in TransformationTarget's values property.

The object corresponds to the Values metadata panel in the Pivot.

Allows specifying which values should be targeted for transformation. The object corresponds to the Values metadata panel in the Pivot.

There are 3 types of value targeting objects:

  • Target By Index
  • Target By Aggregation
  • Target By Title

# Target By Index

Target a zero-based index of the metadata collection.

I.e. if the pivot has 3 aggregation metadata items in the "Values" panel, an index 1 will target all cells that are related to the middle metadata item.

Properties

Name Type Description
index integer[] List of agg indices to target

Example

// Target aggregations 1,2,3 of the pivot
{
    index: [1, 2, 3]
}

# Target By Aggregation

Target cells corresponding to a specific aggregation

Properties

Name Type Description
dim string Dimension name
agg string Aggregation type

Example

// Target value cells where the "Revenue" dimension is aggregated using "sum"
{
    dim: '[Sales.Revenue]',
    agg: 'sum'
}

# Target By Title

Target cells corresponding to a metadata item with the given title

Properties

Name Type Description
title string Metadata item title - should match the title in the JAQL, before translation

Example

// Target all value cells that belong to a metadata "values" item with the title 'Total Sales'
{
    title: 'Total Sales'
}

# Handler Function

function(metadata, cell)

The handler function passed to transformPivot will be evaluated for each cell that matches the targeting object.

It is provided with metadata about the cell, and by-reference access to the cell itself, as arguments.

The cell object allows access to cell value and contents, as well as modification of them, to customize the cell contents.

The metadata and cell information can be used to further filter the cells, beyond the capabilities of the targeting object, by using if conditions and stopping execution (with return) when in a cell that should not be transformed.
This is less efficient than the targeting object, but combining this approach with some preliminary filtering using the other methods will result in less iterations of this function, and more efficient code.

Arguments

Name Type Description
metadata PivotCellMetadata Metadata about the current cell and it's location
cell PivotCellData Access to the cell itself

# PivotCellMetadata

Name Type Description
rowIndex number Zero-based index of current row, including headers
columnIndex number Zero-based index of current column, including categories/row titles
rows object[] Metadata of the current row dimensions
rows[n].title string Title of the metadata item as appears in the JAQL query/response
rows[n].name string Dimension name
rows[n].member * Current member of the dimension
columns object[] Metadata of the current column dimensions
columns[n].title string Title of the metadata item as appears in the JAQL query/response
columns[n].name string Dimension name
columns[n].member * Current member of the dimension
measure object Metadata of the current aggregation
measure.title string Title of the metadata item as appears in the JAQL query/response
measure.dim string Dimension being aggregated *
measure.agg string Aggregation name *
measure.formula string Formula string **
measure.context object Formula context object **

* only for simple aggregations ** only for formulas

# PivotCellData

Name Type Description
value * Raw value of the cell from query response
content string/React.Component<EmbedComponentProps> HTML contents of the cell (text) or React component class
contentType string Type of content in the cell: text, html, component
style CellStyle Cell styles to apply
store object Properties to be passed in React component class

# CellStyle

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)

# React component props

type EmbedComponentProps = {
    width: number | undefined,
    domReadyDefer: { promise: Promise, resolve: Function, reject: function } | undefined,
    [key: string]: any,
}

# PivotConfiguration

Note: to return a configuration option to its default state, set it to undefined.

Properties

Name Type Description Default value
disableDrill boolean Hide drill down menu options for pivot cells false
disableSelect boolean Hide select menu options for pivot cells false
globalStyles CellStyle Defines styles for all cells N/A

# SortObject

One Sorting Object represents sorting configuration for single row/measure.

It has the following structure:

Name Type Description Required Supported Values
target SortingTarget Target object representing metadata item to sort. Yes See SortingTarget
direction String Direction of sorting. Yes 'asc' or 'desc'
sortBy SortingTarget Optional parameter applicable only to sorting by subtotals for row items. No See note below
# The sortBy property

The sortBy property is used only when you want to apply sorting by subtotals for a row item. It has the same type as the target property (See SortingTarget).

The sortBy property can be only used for row type sorting objects.

For example if you want to sort the Country field by subtotals of a "Total cost" measure within a specific column, the sorting rule will have the sortBy property containing a SortingTarget that includes a SortingMeasurePath:

{
    target: {
        type: 'row',
        title: 'Country'
    },
    direction: 'asc',
    sortBy: {
        type: 'measure',
        measurePath: {
           0: '1/1/2013, 12:00 AM', 
           1: 'No'
        },
        measureTitle: 'Total Cost'
    }    
}

In this case Country row item will be sorted by subtotals of Total Cost measure placed inside the No column of the 2013 column.

# SortingTarget

Target object that represents selector which selects pivot row/measure to sort.

Name Type Description Required Supported Values
type String Target to sort type. Yes 'row', 'measure', 'grandtotal'
measurePath SortingMeasurePath Configuration object with indexes and non-formatted parent values of columns of measure to be sorted. Used only for 'measure' sorting target type. No See SortingMeasurePath
measureTitle String Title of header measure cell to be sorted. Used only for 'measure' sorting target type. No Any string title of measure header cell
title String Title of row or grand total cell to be sorted. Used only for 'row' and 'grandtotal' sorting target type. No Any string title of row or grand total cell
# SortingMeasurePath

This is a key-value object that represents the zero based index of column panel elements starting from parent column down to the measure (aggregation) used.

The key represents that numerical index, while the value represents the specific member (value) from that field to target.

For example given the following pivot table:

headers

There are 2 column fields ("Years" and "Discontinued") and then 2 measures calculated per each value of each column ("Total Cost" and "Total Visits").

If we would like to sort by the 2013 "Total Visits" of products that were not discontinued, the measurePath object would look like:

{
    0: '1/1/2013, 12:00 AM',
    1: 'No'
}

As our first (0) column item is "Years" and we pick the value 2013, and our second (1) column item is "Discontinued" and we pick the value No.

It will target columns as follows:

2013 No target

This measurePath combined with measureTitle: 'Total # Visits', will specify which measure should be chosen in scope of target column(s), resulting in the following target:

Total Visits target

Note

For pivots that do not use the columns panel, where columns are directly derived from the measures used, measurePath should always be an empty object.

# Events

Using Pivot Events

Pivot 2 events work the same way as other widget events, via the widget.on(eventName, handler) method. For all events, the parameters passed to the event handler function will be listed below.

# cellClick

Triggers after right/left click or touch (mobile) on cell element.

Handler Arguments

Name Type Description
widget Widget Widget instance object
eventData object Event attributes
eventData.domEvent object Event object
eventData.disableDrill boolean Drill default behaviour
eventData.disableSelect boolean Select default behaviour
eventData.metadata PivotCellMetadata Cell Metadata info
eventData.cell object Cell content object
eventData.cell.value * Raw value of the cell from query response
eventData.cell.content string/React.Component<EmbedComponentProps> HTML contents of the cell (text) or React component class

Example

widget.on('cellClick', function(widget, eventData) {
    console.log(eventData.cell.value);
});

# cellEnter

Triggers after mouse pointer enters a cell.

Handler Arguments

Name Type Description
widget Widget Widget instance object
eventData object Event attributes
eventData.domEvent object Event object
eventData.metadata PivotCellMetadata Cell Metadata info
eventData.cell Object Cell content object
eventData.cell.value * Raw value of the cell from query response
eventData.cell.content string/React.Component<EmbedComponentProps> HTML contents of the cell (text) or React component class

Example

widget.on('cellEnter', function(widget, eventData) {
    console.log(eventData.cell.value);
});

# cellLeave

Triggers after mouse pointer leaves a cell.

Handler Arguments

Name Type Description
widget Widget Widget instance object
eventData object Event attributes
eventData.domEvent object Event object
eventData.metadata PivotCellMetadata Cell Metadata info
eventData.cell Object Cell content object
eventData.cell.value * Raw value of the cell from query response
eventData.cell.content string/React.Component<EmbedComponentProps> HTML contents of the cell (text) or React component class

Example

widget.on('cellLeave', function(widget, eventData) {
    console.log(eventData.cell.value);
});

# Examples

# Measure cell tooltip

This example uses the cellEnter and cellLeave events to show and hide a tooltip

var $dom = prism.$injector.get('ux-controls.services.$dom');
var tip = null;

widget.on('cellEnter', function (widget, event) {
   var measure  = event.metadata.measure;
   if (tip) { tip.deactivate(); }
   if (!measure) { return; }
   var scope = prism.$ngscope.$new();
   scope.text = event.cell.content + ' - ' + measure.dim;

   tip = $dom.tip({
       template: '<span>{{text}}</span>',
       scope: scope,
   }, {
       placement: { place: 'l', anchor: 't' },
       radial: false
   });
  
   tip.activate({x: event.domEvent.clientX, y: event.domEvent.clientY, space: 5});
});

widget.on('cellLeave', function (widget, event) {
   if (tip) {
       tip.deactivate();
   }
});

# Styling a cell

This example uses the transformPivot method to change the text color for dimension "member" cells

widget.transformPivot({
    type: ['member']
}, function setCellColor(metadata, cell) {
    cell.style = cell.style || {};
    cell.style.color = 'red';
});

# Transforming cell content

This example wraps value cell content with an HTML <b> tag with a title attribute in order to show a tooltip.

widget.transformPivot({
   type: ['value']
}, handler);

function handler(metadata, cell) {
    cell.contentType = 'html';
    cell.content = `<b title="${cell.content}">${cell.content}</b>`;
}

# Custom React component

This example replaces Measures cells default text content with a custom React component containing a button.
It demonstrates the use of the store object to pass required properties to the React component.

var React = prism.$injector.get('pivot2.value.React');

function MyComponent(props) {
   return React.createElement(
       "div",
       { style: { display: 'inline-block' } },
       [
           React.createElement(
              'button',
              {
                  onClick: function() {
                       console.log(props.text)
                  }
              }, 
              props.text
           ),
       ]
   );
}

widget.transformPivot({
   type: ['value'],
   values: [],
}, function transformCellContent(metadata, cell) {
   cell.store = cell.store || {};
   cell.store.text = cell.content;
   cell.content = MyComponent;
   cell.contentType = 'component';
});

# Targeting a specific column

This example targets the transformation on value cells that belong to a specific column based on the desired member (new) from the dimension which is used to break the data into columns (Condition):

const target = {
    type: ['value'],
	columns: [
		{
    		dim: '[Commerce.Condition]',
    		members: ['New']
		}	
	]
};

function handler(metadata, cell) {
    cell.style.color = 'red';
}

widget.transformPivot(target, handler);

# Targeting a specific row

This example targets the transformation on value cells that belong to a specific row based on the desired member (Monitors) from the dimension which is used to break the data into rows (Category):

const target = {
    type: ['value'],
	rows: [
		{
    		dim: '[Category.Category]',
    		members: ['Monitors']
		}	
	]
};

function handler(metadata, cell) {
    cell.style.color = 'red';
}

widget.transformPivot(target, handler);

# Postpone 'domready' event

This example replaces default pivot cell text content with a custom React component showing an image.

As image loading make take some time, the optional domReadyDefer property is used to notify the widget when the image is done loading.

var React = prism.$injector.get('pivot2.value.React');

function CustomImage(props) {
   return React.createElement(
       "img",
       { src: props.imageUrl, onLoad: propsdomReadyDefer.resolve }
   );
}

widget.transformPivot({}, (metadata, cell) => {
    cell.content = CustomImage;
    cell.store = cell.store || {};
    const domReadyDefer = {};
    domReadyDefer.promise = new Promise((resolve, reject) => {
        domReadyDefer.resolve = resolve;
        domReadyDefer.reject = reject;
    });
    cell.store.domReadyDefer = domReadyDefer;
    cell.store.imageUrl = '/image/url.png';
}, { pluginKey: 'createCellImage' });

# Sorting Examples

Assume the following Pivot:

  • 3 fields are used as rows: Source, Country, City
  • 2 fields are used as columns: Years in Date, Marketing qualifier
  • 2 measures are used: Total Visits, Total Cost

# Sorting by a row item

To sort by Source in 'desc' direction, the sort object will be:

{
    target: {
        type: 'row',
        title: 'Source'
    },
    direction: 'desc'
}

# Sorting by a measure

To sort by the Total Visits measure inside 2014 year and in No Qualifier in 'desc' direction, the sort object will be:

{
    target: {
        type: 'measure',
        measurePath: {
            0: '1/1/2014, 12:00 AM',
            1: 'No'
        },
        measureTitle: 'Total Visits'
    },
    direction: 'desc'
}

# Sorting by a grand total

To sort by Total Visits measure's grand total in 'desc' direction, sort object will be:

{
    target: {
        type: 'grandtotal',
        title: 'Total Visits'
    },
    direction: 'desc'
}

# Sorting by subtotals

To sort the Country row by subtotals of the Total Cost measure under the 2013 column in 'desc' order, the sort object will be:

{
    target: {
        type: 'row',
        title: 'Country'
    },
    direction: 'desc',
    sortBy: {
        type: 'measure',
        measurePath: {
            0: '1/1/2013, 12:00 AM'
        },
        measureTitle: 'Total Cost'
    }
}