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
