cancel
Showing results for 
Search instead for 
Did you mean: 

Pivot 2.0 - Manipulating a Pivot Chart

Ophir_Buchman
Sisense Team Member
Sisense Team Member

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
Ophir_Buchman_0-1655292033643.png Ophir_Buchman_1-1655292049836.png

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
Ophir_Buchman_3-1655292257999.png Ophir_Buchman_4-1655292270997.png

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
Ophir_Buchman_3-1655292257999.png Ophir_Buchman_5-1655292356839.png

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 = '---'
}
);
0 REPLIES 0