Pivot 2.0 - Manipulating a Pivot Chart

[Ophir_Buchman]

Pivot 2.0 - Manipulating a Pivot Chart

Introduction

The following article discusses how to manipulate the data and styling of your Pivot 2.0 widget.
Please refer to the following article first:  https://sisense.dev/guides/customJs/jsApiRef/widgetClass/pivot2.html.

Cell Identification

To manipulate a pivot cell, we'll have to learn the different identifiers of each cell in the table.

Cell Types

Each cell is linked with a 'type' that represents the data it contains:

• A member cell refers to a Column/Row header
• A value cell refers to a table value cell
• A subtotal cell refers to a subtitle row (title + values)
• A grandtotal cell refers to a grand-total rows & columns (titles + values)

A cell may have more than one 'type':

• A cell that has a subtotal and a member type represents the subtitle row title
• A cell that has a subtotal and a value type represents the subtitle row values (including values + column grand total values)
• A cell that has a grandtotal' and a member type represents the grand-total row and column titles
• A cell that has a grandtotal' and a value type represents the grand-total values (including row & column grand total values)
• A cell that has a grandtotal, a subtotal, and a value type represents the grand total values in the subtitle rows

See the following pivot table and the corresponding cell types

Original Table	Cell Types

Manipulating a Cell (Based on its Type)

Here are two examples of how to manipulate a cell based on its type:

widget.transformPivot(
  {
    type: ['value']
  },
  function (metadata, cell) {
    // Manuipulation code
  }
);

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.type.includes('value')) {
      // Manuipulation code
    }
  }
);

H2 - Cell Indexes

Each cell is represented by three indexes:

• Metadata Index - Representing the logical column ID in the table (aligns with the selected rows/values/columns)
• Column Index - Representing the column number in the table
• Row Index - Representing the row number in the table

Metadata Index

See the following pivot table, the pivot configuration pane, and the corresponding metadata indexes:

Original Table	Metadata Index

Here is an example of how to manipulate a cell based on its metadata index:

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.index == 1) {
      // Manuipulation code
    }
  }
);

Column/Row Index

See the following pivot table and the corresponding row/column indexes

Original Table	Row/Column Indexes

Here is an example of how to manipulate a cell based on its columns/row index:

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.colIndex == 3 && metadata.rowIndex == 2) {
      // Manuipulation code
    }
  }
);

Cell Row/Column/Measure Name

Each cell may be affiliated with three metadata values:

• Measure - The measure calculated in this cell (name & formula)
• Column(s) - The column(s) this cell is under (field, title, & value)
• Row(s) - The rows(s) this cell belongs to (field, title, & value)

Manipulating a Cell (Based on their Measure)

Here is an example of how to manipulate a value cell based on the measure's name:

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.measure.title === 'SUM') {
      // Manuipulation code
    }
  }
);

Manipulating a Cell (Based on Their Row)

Here is an example of how to manipulate a value cell based on the row's value:

widget.transformPivot(
  {},
  function (metadata, cell) {
    // Format based on the value of the a row's name and value
    metadata.rows.forEach(function(row) {
      if (row.title === 'Year' && row.member === '2012-01-01T00:00:00.000') {
        // Manuipulation code
      }
    })
  }
);

Manipulating a Cell (Based on Their Column)

Here is an example of how to manipulate a value cell based on the column's value:

widget.transformPivot(
  {},
  function (metadata, cell) {
    // Format based on the value of the a row's name and value
    metadata.columns.forEach(function(column) {
      if (column.title === 'Online' && column.member === 'False') {
        // Manuipulation code
      }
    })
  }
);

Cell Manipulation

The possible manipulation options of a cell include:

• value - Raw value of the cell from query response (manipulating this value is useless)
• content - The HTML contents of this cell
• style - The cell formatting

Here is an example of how to manipulate value cells' style:

widget.transformPivot(
  {},
  function (metadata, cell) {
    cell.style = {
      backgroundColor : 'lightgray',
      fontSize : 14,
      fontWeight : 'bold',
      fontStyle : 'italic',
      textAlign : 'center',
      color : 'black',
      borderColor : 'black',
      borderWidth : '3px',
      minWidth : '150px',
      maxWidth : '200px'
    };
  }
);

Here is an example of how to manipulate value cells' value:

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (cell.content == '') cell.content = '---'
  }
);

Check out this related content