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.

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

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

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

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

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!

// 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

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

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

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

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

Example

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

Target By Aggregation

Target cells corresponding to a specific aggregation

Properties

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

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

PivotCellMetadata

* only for simple aggregations** only for formulas

PivotCellData

CellStyle

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

SortObject

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

It has the following structure:

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.

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:

Events

cellClick

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

Handler Arguments

Example

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

cellEnter

Triggers after mouse pointer enters a cell.

Handler Arguments

Example

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

cellLeave

Triggers after mouse pointer leaves a cell.

Handler Arguments

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