Pivot 2.0 - Manipulating a Pivot Chart

[Ophir_Buchman]

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/reference/js/widget/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 = '---'
  }
);

[drydendi]

Is there a way to have the row values repeat in the Pivot widget rather than be merged?  Our user base likes to export to Excel and having all the row values merged is not ideal.  Thank you in advance for any help provided!

[Liran_Elnekave · Accepted solution]

You can choose 'table with aggregation' or 'table' widget types 

Liran

[drydendi]

Hi Liran and thank you for the reply!  We are aware we can use those widgets but we prefer the pivot widget.  So, is there a way to "unmerge" the row values in the pivot widget so each row as all the values when exporting?

[Benji_PaldiTeam]

Hi @drydendi ,

A pleasure to e-meet you, I'm Benji from Paldi Solutions

We have developed an Expandable Pivot that gives you much more capabilities than the out-of-the-box pivot table including what you are looking for.

Let me know if you want to test it as we provide immediate free trials.

Also, feel free to reach out if you have further questions, we're always happy to help (: 

[email protected] 

Paldi Solutions - Number #1 Sisense Plugins Developer