# 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)

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

Returns

N/A

Example

// Give all value cells a background color
widget.transformPivot({ type: ['value'] }, function(metadata, cellData) {
    cellData.style.backgroundColor = '#9A94BC';
});

# 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,
});

# 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

# 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 "Rows" dimension cells

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

# Transforming cell content

This example applies a background color to cells with values larger than 0, and wraps 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) {
    if(cell.value > 0) {
        cell.style.background = 'green';
    } 

    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 and uses the store object to pass required properties to it.

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

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

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

# 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';
});