on - edited on by
DRay
Introduction
The following document provides code snippets for customizing a Pivot 1.0 widget
Table of Contents
- Introduction
- Table of Contents
- How to Use This Page?
- Widget Customizations
- Layout #1 - Single Row, Single Value, No Columns
- Layout #2 - Single Row, Multiple Values, No Columns
- Layout #3 - Multiple Rows, Single Value, No Columns
- Layout #4 - Multiple Rows, Multiple Values, No Columns
- Layout #5 - Single Row, Single Value, Single Column
- Layout #6 - Multiple Rows, Single Value, Single Column
- Layout #7 - Single Row, Multiple Values, Single Column
- Layout #8 - Multiple Rows, Multiple Values, Single Column
- Layout #9 - Single Row, Single Value, Multiple Columns
- Layout #10 - Single Row, Multiple Values, Multiple Columns
- Layout #11 - Multiple Rows, Single Value, Multiple Columns
- Manipulating the Table Structure
- Table Alignment
- Manipulating the Header Cells
- Aligning Text
- Changing the Caption of the “Grand Total” Header
- Manipulating the Data Cells
- Aligning Text
- Replacing the Contents of (All) Empty Cells
- Modifying the Contents of Cells From Specific Columns
- Color Based on "Top-Level Header" Cells
- Alter Text Based on "Bottom-Level Header" Cells
How to Use This Page?
To implement the customizations on this page you’ll have to be familiar with the following:
- Basic JavaScript and JQuery
- Widget Lifecycle
- How to add a widget script
Widget Customizations
The easy way to reference cells is by referencing their class type or ID via JQuery:
- All headers are identified using unique class names
- The table contents (data cells) are referenced by a table object (identified using a special ID)
The following section introduces the different table structures and classes used for pivot charts
Layout #1 - Single Row, Single Value, No Columns
This layout includes the following configuration:
- The user has defined a single row (Months)
- The user defines a single value (Sum Revenue)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cell (“Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Value Label Column Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month names) |
A class of a <td> object
td.p-dim-member |
| “Grand Total Row Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #2 - Single Row, Multiple Values, No Columns
This layout includes the following configuration:
- The user has defined a single row (Months)
- The user defines two values (Sum Revenue & Sum Quantity)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cell (“Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Value Label Column Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month names) |
A class of a <td> object
td.p-dim-member |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #3 - Multiple Rows, Single Value, No Columns
This layout includes the following configuration:
- The user has defined multiple rows (Quarters & Months)
- The user defines a value (Sum Revenue)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Value Label Column Header” cell (“Total Revenue“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month and quarter names) |
A class of a <td> object
td.p-dim-member |
| “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) |
A class of a <td> object
td.p-total-row-head |
| “Grand Total Row Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #4 - Multiple Rows, Multiple Values, No Columns
This layout includes the following configuration:
- The user has defined multiple rows (Quarters & Months)
- The user defines two values (Sum Revenue & Sum Quantity)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Value Label Column Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month and quarter names) |
A class of a <td> object
td.p-dim-member |
| “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) |
A class of a <td> object
td.p-total-row-head |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #5 - Single Row, Single Value, Single Column
This layout includes the following configuration:
- The user has defined a single row (Months)
- The user defines a single value (Sum Revenue)
- The user defines a single column (Age Range)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) |
A class of a <td> object
td.p-fake-dim-head |
| “Row Label Column Header” cell (“Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Fake Value Label Column Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-fake-measure-head |
| “Column Label Column Header” cell (“19-24” & “25-34“) |
A class of a <td> object
td.p-measure-head |
| “Grand Total Column Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month names) |
A class of a <td> object
td.p-dim-member |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #6 - Multiple Rows, Single Value, Single Column
This layout includes the following configuration:
- The user has defined multiple rows (Quarters & Months)
- The user defines a single value (Sum Revenue)
- The user defines a single column (Age Range)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) |
A class of a <td> object
td.p-fake-dim-head |
| “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Fake Value Label Column Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-fake-measure-head |
| “Column Label Column Header” cells (“19-24” & “25-34“) |
A class of a <td> object
td.p-measure-head |
| “Grand Total Column Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month and quarter names) |
A class of a <td> object
td.p-dim-member |
| “Sub-Total Row Header” cell (“2009 Q4 Total”, “2010 Q1 Total”, etc.) |
A class of a <td> object
td.p-total-row-head |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #7 - Single Row, Multiple Values, Single Column
This layout includes the following configuration:
- The user has defined a single row (Months)
- The user defines multiple values (Sum Revenue & Sum Quantity)
- The user defines a single column (Age Range)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cell (“Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Column Label Column Header” cells (“19-24” & “25-34“) |
A class of a <td> object
td.p-dim-member-head |
| Individual “Column Value Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-measure-head |
| Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month and quarter names) |
A class of a <td> object
td.p-dim-member |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #8 - Multiple Rows, Multiple Values, Single Column
This layout includes the following configuration:
- The user has defined multiple rows (Quarters & Months)
- The user defines multiple values (Sum Revenue & Sum Quantity)
- The user defines a single column (Age Range)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Column Label Column Header” cells (“19-24” & “25-34“) |
A class of a <td> object
td.p-dim-member-head |
| Individual “Column Value Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-measure-head |
| Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month and quarter names) |
A class of a <td> object
td.p-dim-member |
| “Sub-Total Row Header” cells (“2009 Q4 Total”, “2010 Q1 Total”, etc.) |
A class of a <td> object
td.p-total-row-head |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #9 - Single Row, Single Value, Multiple Columns
This layout includes the following configuration:
- The user has defined a single row (Months)
- The user defines a single value (Sum Revenue)
- The user defines multiple columns (Age Range & Brand ID)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) |
A class of a <td> object
td.p-fake-dim-head |
| “Row Label Column Header” cell (“Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Fake Value Label Column Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-fake-measure-head |
| “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) |
A class of a <td> object
td.p-dim-member-head |
| Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) |
A class of a <td> object
td.p-total-head |
| Global “Grand Total Column Header” cell (“Grand Total“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month names) |
A class of a <td> object
td.p-dim-member |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #10 - Single Row, Multiple Values, Multiple Columns
This layout includes the following configuration:
- The user has defined a single row (Months)
- The user defines multiple values (Sum Revenue & Sum Quantity)
- The user defines multiple columns (Age Range & Brand ID)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Row Label Column Header” cell (“Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) |
A class of a <td> object
td.p-dim-member-head |
| “Value Label Column Header” cell (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-measure-head |
| Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) & (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-total-head |
| Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cell (month names) |
A class of a <td> object
td.p-dim-member |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Layout #11 - Multiple Rows, Single Value, Multiple Columns
This layout includes the following configuration:
- The user has defined multiple rows (Quarters & Months)
- The user defines a single value (Sum Revenue)
- The user defines multiple columns (Age Range & Brand ID)
Participating classes are as follows:
| Cell Category | Object Reference |
| “Fake Row Label Column Header” cell (Empty cell above “Months in Date”) |
A class of a <td> object
td.p-fake-dim-head |
| “Row Label Column Header” cells (“Quarters in Date” & “Months in Date”) |
A class of a <td> object
td.p-dim-head |
| “Fake Value Label Column Header” cell (“Total Revenue”) |
A class of a <td> object
td.p-fake-measure-head |
| “Column Label Column Header” cells (“19-24” & “25-34“) and (“2” & “3”) |
A class of a <td> object
td.p-dim-member-head |
| Individual “Grand Total Column Header” cell (“19-24 Total“ & “25-34 Total”) |
A class of a <td> object
td.p-total-head |
| Global “Grand Total Column Header” cells (“Total Revenue” & “Total Quantity“) |
A class of a <td> object
td.p-grand-total-head |
| “Phantom Column Header” cell (fake header column at the right-side) |
A class of a <td> object
td.phantom |
| “Row Label Row Headers” cells (quarter and month names) |
A class of a <td> object
td.p-dim-member |
| “Sub-Total Row Header” cells (“2009 Q4 Total”, “2010 Q1 Total”, etc.) |
A class of a <td> object
td.p-total-row-head |
| “Grand Total Row Header” cell (“Grand Total”) |
A class of a <td> object
td.p-grand-total-row-head |
| Data cells (holding the revenue values) |
An object with an ID
#pivot_ |
Manipulating the Table Structure
Table Alignment
The table is aligned to the left-hand side of the screen by default.
To align the table to the right-hand or the left-hand sides we’ll first have to get rid of the “fake” phantom column.
| Before | After |
 |
 |
To align the table location use the following snippet:
// This script aligns the table to the center of the widget
function getWidgetDocumentObject(widget) {
// This function determines the document object context (different when in a widget-view or a dashboard-view mode)
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]');
else
return document;
}
widget.on('domready', function(widget) {
// Remove the phantom column and cells
$('td.phantom',element).remove();
// Center the table
doc = getWidgetDocumentObject(widget);
doc.querySelector('.p-container').style.display='block';
doc.querySelector('.p-container').style.marginLeft='auto';
doc.querySelector('.p-container').style.marginRight='auto';
doc.querySelector('.p-container').style.width='fit-content';
});Manipulating the Header Cells
Aligning Text
To align headers' cell text alignment use the following snippet:
(note - Change the JQuery element name to align with the correct header you wish to format)
// This script modifies the “Row Label Column Header” cells' alignment
widget.on('domready', function(widget) {
$('td.p-dim-head',element).css('text-align','center'); // Horizontal
$('td.p-dim-head',element).css('vertical-align','middle'); // Vertical
});Changing the Caption of the “Grand Total” Header
To replace the default text of “Grand Total” use the following snippet:
// This script replaces the header of the "Grand Total" row header to "Total"
function getWidgetDocumentObject(widget) {
// This function determines the document object context (different when in a widget-view or a dashboard-view mode)
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]');
else
return document;
}
widget.on('domready', function(widget) {
doc.querySelector('td.p-grand-total-row-head').querySelector('div.p-head-content').innerText = "Total"
});Manipulating the Data Cells
Aligning Text
To align all data cells' text alignment use the following snippet:
// This script modifies the data cells' alignment
widget.on('domready', function(widget) {
$('td.p-value',element).css('text-align','center'); // Horizontal
$('td.p-value',element).css('vertical-align','middle'); // Vertical
});Replacing the Contents of (All) Empty Cells
To replace the blank space in the empty cells using the following snippet:
// This script modifies the contents of blank data cells to '-'
// Note - A blank cell contents are the character '\xa0'
function getWidgetDocumentObject(widget) {
// This function determines the document object context (different when in a widget-view or a dashboard-view mode)
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]');
else
return document;
}
widget.on('domready', function(widget) {
// Replace blank cells
doc.querySelectorAll('div.p-value').forEach((element) => {
if (typeof element === "object" && element.innerText == '\xa0')
element.innerText = '-'
});
});Modifying the Contents of Cells From Specific Columns
Color Based on "Top-Level Header" Cells
The term “Top-Level Header Cells” refers to one of the following:
- If the table has no columns - “Value” headers
- If the table has one column - “Column” headers
- If the table has multiple columns - Not covered
The following snippet colors the cells under a Top-Level Header named 'No':
// This script colors the cells under a Top-Level Header called 'No'
function getWidgetDocumentObject(widget) {
// This function determines the document object context (different when in a widget-view or a dashboard-view mode)
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]');
else
return document;
}
widget.on('domready', function(widget) {
// Define the column title
columnToColorValue = 'No'
widgetDoc = getWidgetDocumentObject(widget);
// Calculate the amount of columns in the table
dimColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-head').length;
// Calculate the amount of "Values" in every "Column"
measureColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-measure-head').length;
// Scan all columns in the header row
for (let i = 0; i < widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head').length; i++)
{
// Retrieve the <i>'th column in the table
col = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head')[i];
// Check if column should be colored red
shouldColorRed = (col.innerText === columnToColorValue);
// Check the column's width (as it might contain multiple sub-columns (values) underneath it
columnWidth = col.getAttribute('colspan');
if (columnWidth === null) columnWidth = 1; // If it contains a single column there's no colspan attribute
// For every value column
for (let j = 0; j < columnWidth; j++)
{
// Calculate offset from end of table (this value changes in different rows)
colOffset = measureColumns - ((i * columnWidth) + j);
// Extract the table structure
rows = getWidgetDocumentObject(widget).querySelector('#pivot_').rows;
// Iterate through all data cell rows
for(let r = 1; r < rows.length; r++)
{
cells = rows[r].cells;
rowCellCount = Object.values(cells).length;
cellToHandle = rowCellCount - colOffset;
if (cells[cellToHandle].getElementsByClassName('p-value').length > 0)
{
cellToManipulate = cells[cellToHandle].querySelector('.p-value');
if (shouldColorRed) cellToManipulate.style.color = 'red'
}
}
}
}
});Alter Text Based on "Bottom-Level Header" Cells
The term “Bottom-Level Header Cells” refers to one of the following:
- If the table has one column - “Value” headers (applies to the “Value” column in each “Column”)
- If the table has multiple columns - Not covered
The following snippet alters the text of cells under a Bottom-Level Header named 'No':
This script alters the text of cells under a Top-Level Header called 'Average Cost' (changes empty cells to '---'):
// This script alters the text of cells under a Bottom-Level Header called 'Average Cost'
// Note - A blank cell contents are the character '\xa0'
function getWidgetDocumentObject(widget) {
// This function determines the document object context (different when in a widget-view or a dashboard-view mode)
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]');
else
return document;
}
widget.on('domready', function(widget) {
// Define the column title, old text, and new text
columnToFixText = 'Average Cost'
originalText = '\xa0'
newText = '---'
widgetDoc = getWidgetDocumentObject(widget);
// Calculate the amount of columns in the table
dimColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-head').length;
// Calculate the amount of "Values" in every "Column"
measureColumns = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-measure-head').length;
// Scan all columns in the header row
for (let i = 0; i < widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head').length; i++)
{
// Retrieve the <i>'th column in the table
col = widgetDoc.querySelector('div.p-fixed-corner').getElementsByClassName('p-dim-member-head')[i];
// Check the column's width (as it might contain multiple sub-columns (values) underneath it
columnWidth = col.getAttribute('colspan');
if (columnWidth === null) columnWidth = 1; // If it contains a single column there's no colspan attribute
// console.log('found column ' + i + ' width = ' + columnWidth + ' that fits the condition');
// For every "Value"
for (let j = 0; j < columnWidth; j++)
{
// Calculate offset from end of table (this value changes in different rows)
colOffset = measureColumns - ((i * columnWidth) + j);
// Extract the table structure
rows = widgetDoc.querySelector('#pivot_').rows;
// Iterate through all rows
shouldFixText = false;
for(let r = 1; r < rows.length; r++)
{
cells = rows[r].cells;
rowCellCount = Object.values(cells).length;
cellToHandle = rowCellCount - colOffset;
if (cells[cellToHandle].getElementsByClassName('p-head-content').length > 0 &&
cells[cellToHandle].querySelector('.p-head-content').innerText === columnToFixText)
shouldFixText = true;
if (cells[cellToHandle].getElementsByClassName('p-value').length > 0)
{
cellToManipulate = cells[cellToHandle].querySelector('.p-value');
if (shouldMakeBold) cellToManipulate.style.fontWeight = 'bold';
if (shouldFixText && cellToManipulate.innerText === originalText) cellToManipulate.innerText = newText;
}
}
}
}
});
