Pivot 2.0 - Manipulating a Pivot Chart
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
Limiting Date Range Filters in Sisense Dashboards
Wide date ranges in Sisense dashboards can lead to performance issues, especially when using live models or querying large datasets. For live data models, large queries increase costs as more data is pulled from the data warehouse. For Elasticubes, this can cause performance bottlenecks. To avoid these issues, here is a quick solution to "limit" the date range users can select, ensuring both cost-efficiency and smooth performance. Read more to find out how!Advanced Pivot Widget Scripting - Combining Custom JAQL and the Pivot 2.0 API
While the Pivot Table Widget Type is a highly customizable and flexible Sisense widget for representing data in tabular form, certain use cases may be best achieved through custom code and scripting. The Pivot 2.0 JavaScript API facilitates the modification of existing pivot table cells, including updating cell values and adding data to cells not present in the initial results.
