Pivot 2.0 - Manipulating a Pivot Chart
Summary: Ophir_Buchman provides an in-depth guide on manipulating data and styling in Pivot 2.0 widgets. The discussion outlines various cell types such as member, value, subtotal, and grandtotal cells, along with methods for manipulating each based on type, indexes, and metadata values. The article includes code examples for different manipulation techniques like altering styles and content of cells. Subsequent responses from Jake Raz and Tri Anthony contain no additional content.
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 TableCell 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 TableMetadata 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 TableRow/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
Tri Anthony
·1 year agoHi Jake_Raz, the scripts in this article are templates with no actual manipulation examples. The placeholder for the manipulation part is marked with the comment line "// Manipulation code". These are some the most common manipulation use cases to use the Pivot 2.0 API for:
The Pivot 2.0 API page on our Developer documentation site includes a few real manipulation examples.
Let me know if there's any particular manipulation use case that you're looking to implement, and we can explore possible solutions.
Jake Raz
·1 year agoWould it be possible to add "after" images to this article? There's several screenshots showing the "original table" but not what the table will look like after manipulating the elements in question.