Sisense Community

Community Feedback
Chapters
Events
Forums
Blogs
KB Docs
Support
Learning
Use Case Gallery

All Posts Discussions Blogs Ideas Questions

Categories

Pivot

Widget & Dashboard Scripts
How to Hide a Column in Pivot 2.0

This article shows a solution for hiding a column in Pivot 2.0 . There are several ways to hide a column in a Sisense Pivot, and you can find a few of them here in the Sisense Community. This article presents another approach that can have advantages and limitations. Before getting started, it's important to know that Sisense has two different versions of the Pivot widget. This only affects users running Windows or Linux versions earlier than L8.2.1 . The newer version of the Pivot introduces additional capabilities, which also changes the way columns can be hidden. If you're looking for a solution that works with Windows/Pivot 1.0 , try this approach instead. The transformPivot method runs between the data fetching and DOM rendering phases. Instead of directly modifying the CSS or the DOM, it changes the Pivot properties so the widget renders differently. Another important detail is that this method works on individual cells, as shown in the following script: // SETUP: Add this script to a Pivot widget via Widget Script (Edit Widget > Script tab). // It hides specific measure columns from the pivot table without removing them from the data. widget.transformPivot({}, function (metadata, cell) { // SETUP: List the exact column titles you want to hide. // These must match the measure titles shown in the widget header (case-sensitive). // Example: ['Total Revenue', 'Cost', 'Margin %'] const columnsToHide = ["Cost"]; const columnTitle = (metadata.measure && metadata.measure.title) || (metadata.column && metadata.column.title) || null; if (columnTitle && columnsToHide.includes(columnTitle)) { cell.style.width = "0px"; cell.style.minWidth = "0px"; cell.style.maxWidth = "0px"; cell.style.padding = "0px"; cell.style.margin = "0px"; cell.style.border = "none"; cell.style.color = "transparent"; cell.style.fontSize = "0px"; cell.content = ""; } }); The script walks through each cell using transformPivot and retrieves the title of the measure or column. This makes it possible to provide a simple configuration that identifies the specific column title to hide. Since Pivot in general works with individual cells, every cell that belongs to the target column must be hidden. The style properties applied in the script work together to fully hide the column. While some of these properties alone can hide content with CSS, using all of them ensures the entire column is hidden correctly. Why not use display: none ? The Pivot is a smart widget that automatically calculates the layout sizes based on the cells size. When a cell is resized, the Pivot recalculates the layout to keep it responsive. Using display: none removes the element from the layout, which interferes with these calculations. As a result, columns may no longer be positioned correctly, especially when working with nested columns. For this reason, reducing the cell dimensions (such as width) is preferred over removing the element from the layout.

Example of Pivot using display none to hide

Using jQuerry You may have seen solutions that manipulate the Pivot using CSS programmatically. While those approaches can work, they are generally not the best practice. // This script hide the first column programmatically widget.on("ready", () => { $(".table-grid__cell--col-0", element).width(0); $(".table-grid__cell--col-0", element).hide(); // Equivalent to display: none }); As explained earlier, the Pivot is a smart widget. Although it is ultimately built with HTML and CSS, it is rendered based on internal properties. Hiding a cell directly in the DOM without updating those properties can lead to unexpected sizing issues because the Pivot may still treat the hidden cell as part of the layout.

Example of a Pivot using DOM hide option

Using transformPivot avoids this problem because the changes are applied before the DOM is rendered rather than after. Another potential issue with CSS-based approaches is relying on CSS classes. Class names within the application can change over time, causing the script to break. Whenever possible, avoid depending on CSS class selectors if a more reliable approach is available. References/Related Content Sisense: Pivot 2.0 API Documentation Previous version of Script Pivot 2.0: Manipulating a Pivot Chart Customizing a Pivot 1.0 Widget (Legacy) Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.
Micael SantanaPosted 2 months ago
0
Discussion
Help and How-To

Tim von Ahsen
Pivot2.0 JavaScript Column Width

My PivotTable has 3 columns. I want javascript to set the width of Column2 to 10px. I've already changed the contents of Column2 to all empty strings. I tried DOM-manipulation. The Pivot DOM structure is complicated; even if I succeed, the resulting code will be hard to maintain and vulnerable to future changes by Sisense. Has someone done this before, either using DOM-manipulation or a better way?
6 months agolast reply 5 months ago
1
Question
Help and How-To
How to pivot my data and get counts based on the min and max date value
Luke Flett
Posted 11 months ago • Last reply 11 months ago
1
Widget & Dashboard Scripts
Pivot 2.0 - Manipulating a Pivot Chart

Introduction The following article discusses how to manipulate the data and styling of your Pivot 2.0 widget. Please refer to the following article first: https://sisense.dev/guides/customJs/jsApiRef/widgetClass/pivot2.html . Cell Identification To manipulate a pivot cell, we'll have to learn the different identifiers of each cell in the table. Cell Types Each cell is linked with a 'type' that represents the data it contains: A member cell refers to a Column/Row header A value cell refers to a table value cell A subtotal cell refers to a subtitle row (title + values) A grandtotal cell refers to a grand-total rows & columns (titles + values) A cell may have more than one 'type': A cell that has a subtotal and a member type represents the subtitle row title A cell that has a subtotal and a value type represents the subtitle row values (including values + column grand total values) A cell that has a grandtotal ' and a member type represents the grand-total row and column titles A cell that has a grandtotal ' and a value type represents the grand-total values (including row & column grand total values) A cell that has a grandtotal , a subtotal , and a value type represents the grand total values in the subtitle rows See the following pivot table and the corresponding cell types Original TableCell Types Manipulating a Cell (Based on its Type) Here are two examples of how to manipulate a cell based on its type: widget.transformPivot( { type: ['value'] }, function (metadata, cell) { // Manuipulation code } ); widget.transformPivot( {}, function (metadata, cell) { if (metadata.type.includes('value')) { // Manuipulation code } } ); H2 - Cell Indexes Each cell is represented by three indexes: Metadata Index - Representing the logical column ID in the table (aligns with the selected rows/values/columns) Column Index - Representing the column number in the table Row Index - Representing the row number in the table Metadata Index See the following pivot table, the pivot configuration pane, and the corresponding metadata indexes: Original TableMetadata Index Here is an example of how to manipulate a cell based on its metadata index: widget.transformPivot( {}, function (metadata, cell) { if (metadata.index == 1) { // Manuipulation code } } ); Column/Row Index See the following pivot table and the corresponding row/column indexes Original TableRow/Column Indexes Here is an example of how to manipulate a cell based on its columns/row index: widget.transformPivot( {}, function (metadata, cell) { if (metadata.colIndex == 3 && metadata.rowIndex == 2) { // Manuipulation code } } ); Cell Row/Column/Measure Name Each cell may be affiliated with three metadata values: Measure - The measure calculated in this cell (name & formula) Column(s) - The column(s) this cell is under (field, title, & value) Row(s) - The rows(s) this cell belongs to (field, title, & value) Manipulating a Cell (Based on their Measure) Here is an example of how to manipulate a value cell based on the measure's name: widget.transformPivot( {}, function (metadata, cell) { if (metadata.measure.title === 'SUM') { // Manuipulation code } } ); Manipulating a Cell (Based on Their Row) Here is an example of how to manipulate a value cell based on the row's value: widget.transformPivot( {}, function (metadata, cell) { // Format based on the value of the a row's name and value metadata.rows.forEach(function(row) { if (row.title === 'Year' && row.member === '2012-01-01T00:00:00.000') { // Manuipulation code } }) } ); Manipulating a Cell (Based on Their Column) Here is an example of how to manipulate a value cell based on the column's value: widget.transformPivot( {}, function (metadata, cell) { // Format based on the value of the a row's name and value metadata.columns.forEach(function(column) { if (column.title === 'Online' && column.member === 'False') { // Manuipulation code } }) } ); Cell Manipulation The possible manipulation options of a cell include: value - Raw value of the cell from query response (manipulating this value is useless) content - The HTML contents of this cell style - The cell formatting Here is an example of how to manipulate value cells' style: widget.transformPivot( {}, function (metadata, cell) { cell.style = { backgroundColor : 'lightgray', fontSize : 14, fontWeight : 'bold', fontStyle : 'italic', textAlign : 'center', color : 'black', borderColor : 'black', borderWidth : '3px', minWidth : '150px', maxWidth : '200px' }; } ); Here is an example of how to manipulate value cells' value: widget.transformPivot( {}, function (metadata, cell) { if (cell.content == '') cell.content = '---' } ); Check out this related content
Ophir_BuchmanPosted 2 years ago • Last reply 1 year ago
2
Widget & Dashboard Scripts
Change the background color of the Pivot cell on value Windows

If you want to change the background color for categories on value, like in the picture below You can use such a script: var columns = [2]; //select the date columns that should be transformed widget.on('ready', function (se, ev) { $.each(columns, function (index, value) { var num = $("tbody tr:first", element).children().length - value; $("tbody tr", element).not('.wrapper, .p-head-content').each(function () { var cell = $(this).children().last(); for (var a = 0; a < num; a++) { cell = cell.prev(); } var cell_value = cell.text() // change the values and color below if (cell_value.includes('Male')) { $(cell).css("background-color", 'red'); } else if (cell_value.includes('Female')) { $(cell).css("background-color", 'yellow'); }else if (cell_value.includes('Unspecified')) { $(cell).css("background-color", 'white'); } }); }); }); For your Pivot widget, press the Edit button in the upper right corner of the widget (pencil icon), press the three dots in the upper right corner, select Edit Script from the menu, and paste the code. Then press the Save button. Have a good day! Disclaimer: This post outlines a potential custom workaround for specific use cases. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. The content is provided "as-is" without any warranty, including security or fitness for a particular purpose. Custom coding is involved, which falls outside Sisense's warranty and support. Additional Resources: Sisense Academy: https://academy.sisense.com/master-class-advanced-dashboards-with-plug-ins-and-scripts Sisense Docs: https://docs.sisense.com/main/SisenseLinux/customizing-sisense-using-code.htm Sisense Community: https://community.sisense.com/forum/widget-dashboard-scripts-40/
OleksandrBPosted 1 year ago
0
How-Tos & FAQs
Show in the Pivot Top 7 Values, Starting From the 3rd

Show in the Pivot Top 7 Values, Starting From the 3rd We have a task to show from the top 10 values just from 3 through 10. Based on Sample Ecommerce elasticube, we have in rows - " Brand " and in values " Total revenue " To see just the Top 10 we will add a filter for Brand ranked by Total Revenue We add one more value on the widget - rank(sum([Revenue])) to rank all rows. After this, we will create a filter based on this value, "Between 3 and 10" As a result, we can see in the Pivot Top 7 Brands, from 3 to 10. If you need to hide this third column, please check this article - Hide a column - Linux Pivot .
OleksandrBPosted 2 years ago
0
Widget & Dashboard Scripts
How to represent negative numbers with leading and trailing parentheses

How to represent negative numbers with leading and trailing parentheses In Accounting, it is common to represent negative numbers with leading and trailing parentheses. For example, negative two hundred is displayed as "(200)" Also, It incorporates a thousand comma separator. In the event that decimal values are absent, it appends ",00"; if present, it retains them. Furthermore, if there is only one decimal place after the comma, an additional zero is added for a more aesthetically pleasing format. <span>widget.transformPivot({ type: ['value'] }, function(metadata, cell) {</span><br/><br/><span> if (cell.value >= 0 || !cell.value) {</span><br/><br/><span> cell.content = '$' + formatNumber(cell.value);</span><br/><br/><span> cell.contentType = 'html';</span><br/><br/><span> } else {</span><br/><br/><span> cell.content = '$(' + formatNumber(-1 * cell.value) + ')';</span><br/><br/><span> cell.contentType = 'html';</span><br/><br/><span> }</span><br/><br/><span>});</span><br/><br/><br/><br/><br/><span>function formatNumber(value) {</span><br/><br/><span> if (value === null || value === undefined) {</span><br/><br/><span> return '';</span><br/><br/><span> }</span><br/><br/><br/><br/><br/><span> const formattedValue = value.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');</span><br/><br/><br/><br/><br/><span> if (formattedValue.includes('.')) {</span><br/><br/><span> const decimalPart = formattedValue.split('.')[1];</span><br/><br/><span> if (decimalPart.length === 1) {</span><br/><br/><span> return formattedValue + '0';</span><br/><br/><span> } else {</span><br/><br/><span> return formattedValue;</span><br/><br/><span> }</span><br/><br/><span> } else {</span><br/><br/><span> return formattedValue + '.00';</span><br/><br/><span> }</span><br/><br/><span>}</span> Before: After: Leave a comment if this was helpful! Disclaimer : This blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment before deploying them to ensure that the solutions proffered function as desired. To avoid doubt, the content of this blog post is provided to you "as-is" and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.
Ihor BuriakPosted 2 years ago
0
Widget & Dashboard Scripts
How to use a widget.transformPivot function to change the background color of the entire row

This article provides an example JavaScript code snippet on how to use a widget.transformPivot function to change the background color of the entire row based on a value within that row. Please refer to the Sisense developer's documentation to learn more about Pivot 2.0 methods: https://sisense.dev/guides/customJs/jsApiRef/widgetClass/pivot2.html#methods In the example below transformPivot is being executed for a cell context so that we can't access all row cells from it, however, we can use it in order to generate an array of required rows’ indexes in order to change their background color later on a ready event. let rowsToChange = [] let changedBuffer = [] let contentToRecolor = '0' widget.transformPivot({}, (metadata, cell) => { if (cell.content === contentToRecolor && !rowsToChange.includes(metadata.rowIndex)) { rowsToChange.push(metadata.rowIndex) } }) widget.on('ready', () => { let widgetMain = $(`widget[widgetid=${widget.oid}]`).length ? $(`widget[widgetid=${widget.oid}]`) : $(`#prism-mainview`) if (changedBuffer.length) { changedBuffer.forEach(i => { widgetMain.find('.table-grid__row-' + i).css({ 'background-color': 'inherit' }) widgetMain.find('.table-grid__row-' + i).children().each(function() { $(this).css({ 'background-color': 'inherit' }) }) }) } rowsToChange.forEach(i => { widgetMain.find('.table-grid__row-' + i).css({ 'background-color': 'pink' }) widgetMain.find('.table-grid__row-' + i).children().each(function() { $(this).css({ 'background-color': 'pink' }) }) }) changedBuffer = rowsToChange rowsToChange = [] }) This script checks if any cell has '0' value and changes all respective rows' color to pink. Feel free to change the contentToRecolor variable to be the value you want to use for changing a background color and let us know if this script works for you. Disclaimer : Please note that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you “as-is” and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.
Liliia KislitsynaAdminPosted 2 years ago
0
Widget & Dashboard Scripts
Usage PivotAPI to Beautify Data On Pivot

Usage PivotAPI to Beautify Data On Pivot In this article, we will review the capabilities of PivotAPI to customize cells. Additionally, we will review important properties, as well as some tricks, we can use to visualize data in a more beautiful way. NOTE: Usage of these technics requires minimal knowledge of JavaScript We will review data formatting for two use cases: Changing styles of the cell depending on the value in the cell; Converting seconds to HH:MM:SS format. Changing styles of the cell depending on the value in the cell Code: const myTarget = { type: ['value'], values: [ { title: 'formulaTitle' // put here desired column } ] }; widget.transformPivot(myTarget, (metadata, cell) => { //Exclude 0 and empty rows const isPositive = cell.value > 0; const isNegative = cell.value < 0; cell.style = cell.style || {}; let additionalStyle; if (isPositive) { cell.content = `${cell.content} ↑`; additionalStyle = { color: 'green', fontWeight: 'bold' }; } else if (isNegative) { cell.content = `(${cell.content.replace('-', '')}) ↓`; additionalStyle = { color: 'red', fontSize: '18px' }; } if (additionalStyle) { cell.style = Object.assign(cell.style, additionalStyle); } }); Explanation: We need to define columns where the script will be applied. It's done in the variable [myTarget]. This variable has two properties: type - refers to the type of cells, which will be modified by the script; values - an array of the values, which will be modified by the script. So, our script is targeted to update cells, which shows values computed by the formula 'formulaTitle'. Once we have defined the target of the script, we will need to initiate widget.transformPivot(). As the first argument we are passing the target and as the second argument we send a callback function, that will be executed for the cells. The callback function receives information about the cell as the second argument. It contains several important properties: value - this is a numeric value before formatting (for formulas only); content - this is formatted value, which is shown in a frame; style - additional styles of the cell. In terms of our script, we did the following: Check the value in the cell to understand its sign; After this, depending on the sign we update the styles of the cell and change content by adding an arrow up or arrow down. Before After Converting seconds to HH:MM:SS format. Code: const formulaTitle = 'Formatted'; //Name of the formula, which store integer; const time_separator = ":"; //Delimiter const hourSign = ''; //Symbol for hours if needed const minuteSign = ''; //Symbol for minutes if needed const secondSign = ''; //Symbol for seconds if needed widget.transformPivot( { type: ['value'] //We are going to process values (formulas) only }, processCell ); function processCell(metadata, cell) { if (metadata.measure.title === formulaTitle) { //Find formula with the name defined in a variable [formulaTitle] try { cell.content = computeContent(cell); //Convert value into desired format } catch(err) { console.warn('Unable to process cell'); } }; } function computeContent(cell) { if (!cell.value || isNaN(parseInt(cell.value))) { return cell.content; } else { const value = parseInt(cell.value); const sign = value < 0 ? "-" : ""; const hours = parseInt(value / 3600); const minutes = parseInt((value - (hours * 3600)) / 60); const seconds = value - (hours * 3600) - (minutes * 60); const hoursText = `${hours < 10 ? "0" + hours : hours}${hourSign}`; const minutesText = `${minutes < 10 ? "0" + minutes : minutes}${minuteSign}`; const secondsText = `${seconds < 10 ? "0" + seconds : seconds}${secondSign}`; return `${sign}${hoursText}${time_separator}${minutesText}${time_separator}${secondsText}`; } } Explanation: This script processes all the cells, which are produced by the formulas. If you have multiple formulas in your pivot, then the time of execution can be quite long. If you want to process some particular cells, then check the first example - it shows how to limit columns, which are processed by the script. We are going to process only values defined by the formula with the title 'Formatted', so we add this condition to the function callback. If this condition is not met (another formula computed the value), we will not execute the further logic. The further logic converts an integer from the cell to the format HH:MM:SS . The computed value is returned and set as [ cell.content ]. Result of execution (original value is stored in the column [Original], the formatted one in the column [Formatted]): I hope you find this article useful and leverage the knowledge shared about PivotAPI capabilities and their usage. Please share your experience in the comments!
Oleksii DemianykAdminPosted 3 years ago
0