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; } } } } });Quickly Add Unique Identifier to Blox Action Buttons Without Modifying Blox Template
A customer recently had the unusual request for a method to select and distinguish, via a CSS selector used within a custom Javascript action, for a specific Blox buttons in a existing Blox widget that contained multiple identical buttons, identical in inner text and all other parameters, without adding any new lines to the Blox template to add unique identifiers.
REST API - Adding Data Level Security
When building code to automate the process of adding users (or groups), it may be beneficial to add security around those users. Follow the steps below to learn how to add data level security through the REST API: Step 1 From your SiSense home page, navigate to the Manage tab and choose the option for REST API. From here, click on the link to the REST API Reference interface. Step 2 From here, choose the 0.9 version and expand the Elasticube section and scroll down to the POST /elasticubes/datasecurity section. Step 3 The sample code below shows a valid JSON object to use as part of the REST API request. Starting from the sample code, replace the value for party with a user identifier that already exists in your system. Then paste the code into the REST API interface and click run. You should see a response of 200, which indicates a successful operation. [{ "server": "LocalHost", "elasticube": "Sample Lead Generation", "table": "Lead Generation", "column": "Country", "datatype": "text", "shares": [{ "party": "5c0f85690ca2f66cc242e266", "type": "user" }], "members": [ "United States", "England" ] }] Notes The server name value is case sensitive, so make sure it matches with your system. The sample provided is specific to one of the sample databases that comes with SiSense. To modify this to a different system, just update necessary fields to include your specifications. The sample provided is an array that consists of a single element. This could be modified to contain several entries all sent at once.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>Migrating Blox Custom Actions
When relocating Sisense resources from one server to another, the Sisense Rest API is a possible method for transferring Sisense components and objects like dashboards, users, groups, and datasources. Similarly, Blox Custom Actions can be migrated through Sisense Rest API endpoints.
