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

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 thats performes 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

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

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!

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

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