Sisense Community

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

Categories

Pivot API

Widget & Dashboard Scripts
Copy a Pivot Cell Value to the Clipboard on Click [Linux]

Overview The Pivot 2.0 API exposes a cellClick event that fires whenever a user clicks a cell, handing to script the full cell context. This short guide uses that event to add a small convenience: click any cell and its value is copied straight to the clipboard, with a brief toast to confirm. The implementation is intentionally minimal. One event handler plus two small helpers. Availability: The Pivot 2.0 API (including cellClick) is available in Sisense L8.2.1 and later , on Linux versions, and only for Pivot 2.0 widgets.

Step-by-step guide Open your Pivot 2.0 widget and click Edit Script (the pencil / script icon in the widget menu). Paste the script below into the editor. Click Apply , save the dashboard, and refresh. Clicking any cell now copies its value and shows a confirmation toast. widget.on("cellClick", function (widget, eventData) { const value = eventData?.metadata?.cellData?.content; if (value === undefined || value === null || value === "") { return; } if (typeof value !== "string") { console.error( "[pivot2-copy] Expected cell content to be a string, got " + typeof value + ":", value ); return; } copyToClipboard(value).then(function (ok) { if (ok) { showToast("Copied: " + value); } else { console.error("[pivot2-copy] Failed to copy cell value to clipboard"); } }); }); // --- helpers --------------------------------------------------------------- // Resolves to true only when the text actually reached the clipboard. // Prefer the async Clipboard API (requires HTTPS and, in iframe embeds, // allow="clipboard-write"); fall back to a hidden textarea otherwise. function copyToClipboard(text) { if (navigator.clipboard && navigator.clipboard.writeText) { return navigator.clipboard.writeText(text).then( function () { return true; }, function () { return legacyCopy(text); } ); } return Promise.resolve(legacyCopy(text)); } function legacyCopy(text) { const ta = document.createElement("textarea"); ta.value = text; ta.style.position = "fixed"; ta.style.opacity = "0"; document.body.appendChild(ta); ta.select(); let ok = false; try { ok = document.execCommand("copy"); } catch (e) { ok = false; } document.body.removeChild(ta); return ok; } function showToast(msg) { const id = "pivot2-copy-toast"; const existing = document.getElementById(id); if (existing) { existing.parentNode.removeChild(existing); } const el = document.createElement("div"); el.id = id; el.textContent = msg; el.style.cssText = [ "position:fixed", "bottom:24px", "left:50%", "transform:translateX(-50%)", "background:#2b2b2b", "color:#fff", "padding:8px 14px", "border-radius:6px", "font:13px/1.3 Arial, sans-serif", "box-shadow:0 2px 8px rgba(0,0,0,.3)", "z-index:99999", "opacity:0", "transition:opacity .15s ease", ].join(";"); document.body.appendChild(el); requestAnimationFrame(function () { el.style.opacity = "1"; }); setTimeout(function () { el.style.opacity = "0"; setTimeout(function () { if (el.parentNode) { el.parentNode.removeChild(el); } }, 200); }, 1400); } How it works Widget.on("cellClick", ...) fires on every cell click and passes eventData. The displayed text lives at eventData.metadata.cellData.content — that's what we copy, so it works for both value cells (the number) and member cells (the label). Empty cells (blank grand-total corners, etc.) are ignored, and a quick type check makes sure cell content is a string, as it should be. Toast is a self-removing element fixed to the bottom of the screen confirms the copy, then fades out after ~1.4s (can be adjusted). Re-clicking replaces any existing toast so they don't stack up. A note on the Clipboard API The copy uses the modern asynchronous Clipboard API (navigator.clipboard.writeText), which is the reliable path, but it has two requirements : Secure context (HTTPS) - on plain HTTP the Clipboard API is unavailable. Embedded dashboards (iframes) need allow="clipboard-write" on the iframe, or the write is blocked by permissions policy. When the Clipboard API is missing or blocked, the script falls back to the legacy approach — a hidden <textarea> plus document.execCommand("copy"). This works in more contexts (including some older embedded browsers) but is deprecated and can fail silently in others. Because both paths return their real success/failure, the toast only shows "Copied:" when the value genuinely reached the clipboard. If both paths fail, nothing is copied and a message is logged to the browser console ([pivot2-copy] Failed to copy...) rather than misleading the user with a false confirmation. Notes and limitations Pivot 2.0 / Linux / L8.2.1+ only, as noted above. The field path metadata.cellData.content reflects the current event payload; if a future version changes the shape, it should be adjusted. This copies the displayed content (formatted text), not the underlying raw numeric value. To copy the raw value target eventData.metadata.cellData.value Resources Pivot 2.0 API — cellClick event and the cell context payload: https://developer.sisense.com/guides/customJs/jsApiRef/widgetClass/pivot2.html#cellclick Widget class: https://developer.sisense.com/guides/customJs/jsApiRef/widgetClass/#widget-class
Ivan AmoshyiPosted 4 days ago
0
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 weeks 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 KislitsynaPosted 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 DemianykPosted 3 years ago
0