Customizing a Pivot 1.0 Widget
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) 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; } } } } });
Custom Dashboard URL in an Email Report
When sending a dashboard as an email report, by default the email will link to the dashboard on Sisense web. In some cases, customers want to have the link point to a different location. For instance, customers using SSO would want the link to point to their own portal rather than Sisense web. Follow the steps below to link your dashboard to a desired location: On your server, go to: C:\Program Files\Sisense\PrismWeb\vnext\src\features\emails\templates\dashboard_report Locate the html.ejs file Backup your original html.js file Open the file and replace the href value with the desired URL. Here are 2 different examples: Link the dashboard to a known page: <a name='dashboard' href='www.yahoo.com' target='_blank'> <img src='cid:<%= images[i] %>' id="<%= images[i] %>" name="<%= images[i] %>" /> </a> If you don’t want the dashboard link to any page, leave the href value blank: <a name='dashboard' href='' target='_blank'> <img src='cid:<%= images[i] %>' id="<%= images[i] %>" name="<%= images[i] %>" /> </a>
