cancel
Showing results for 
Search instead for 
Did you mean: 
Ophir_Buchman
Sisense Team Member
Sisense Team Member

Introduction

The following document provides code snippets for customizing a Pivot 1.0 widget

Table of Contents

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)

343efa6e-cbb4-4f8b-a00f-2e14afc820f9.png

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)

f6146f7f-14fb-489b-a56d-2dff68792cd8.png

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)

2173460c-0eaf-4166-a680-4691b4af82c4.png

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)

7c928253-4473-4da6-8e0f-b85b0404ce68.png

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)

c8b69740-8750-4bec-98e6-ccd45668bcdc.png

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)

305155fb-fc22-443b-af62-667b818e1c5f.png

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)

2295e55b-2050-45b5-97a8-0af7aec9fb7b.png

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)

2639a46f-aa4d-4d99-b517-042ca3e543c2.png

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)

1ba79187-b5ec-4d65-bfb8-3493fd165f3e.png

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)

60f951c8-8ac8-4581-8677-fe3837a64bfe.png

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)

161d632e-e8a8-441a-8be7-18367f7deda2.png

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
46231d6d-ef9e-4e33-95d9-224a124e576b.png 7de9300b-e0f4-465a-ab71-9d5e622a21a3.png

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;
				}
			}
		}	
	}
});
Comments
TwieN
8 - Cloud Apps
8 - Cloud Apps

, thank you so much for a very good reference!

Wonder if we have a way to customize Grand Total column cells (the most right column) - for example for Layout 6? (Or I missed it) 

Version history
Last update:
‎01-12-2022 11:12 PM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request