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:

¹ 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:

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:

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:

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:

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'
    }
}