Custom Widget/Dashboard script to return full digit numbers
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 own 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 not covered by Sisense warranty and support services. This article does cover the use case when you would like to display full size of number instead of the default logic approach we use when numbers are shorted, example: After the solution would be applied: Here is the widget script that you could use to effect the specific widget Chart: // Function to convert shortened numbers to full numbers function convertToFullNumber(value) { if (typeof value !== 'string') { throw new Error("Input must be a string."); } let multiplier = 1; let number = parseFloat(value); if (isNaN(number)) { throw new Error("Invalid numeric value."); } const lowerValue = value.toLowerCase(); if (lowerValue.includes('m')) { multiplier = 1e6; // Million } else if (lowerValue.includes('k')) { multiplier = 1e3; // Thousand } else if (lowerValue.includes('b')) { multiplier = 1e9; // Billion } else if (!/^\d+(\.\d+)?$/.test(lowerValue)) { throw new Error("Invalid format. Only numbers with 'k', 'm', or 'b' are allowed."); } return Math.round(number * multiplier); } widget.on('beforedatapointtooltip', (widget, query) => { let value = query.context.points[0].value; try { const convertedValue = convertToFullNumber(value); query.context.points[0].value = convertedValue; } catch (error) { console.error("Conversion error: " + error.message); query.context.points[0].value = value; // Keep the original value if conversion fails } }); Dashboard script which effects many chart widgets: // Function to convert shortened numbers to full numbers function convertToFullNumber(value) { if (typeof value !== 'string') { throw new Error("Input must be a string."); } let multiplier = 1; let number = parseFloat(value); if (isNaN(number)) { throw new Error("Invalid numeric value."); } const lowerValue = value.toLowerCase(); if (lowerValue.includes('m')) { multiplier = 1e6; // Million } else if (lowerValue.includes('k')) { multiplier = 1e3; // Thousand } else if (lowerValue.includes('b')) { multiplier = 1e9; // Billion } else if (!/^\d+(\.\d+)?$/.test(lowerValue)) { throw new Error("Invalid format. Only numbers with 'k', 'm', or 'b' are allowed."); } return Math.round(number * multiplier); } // Widget event handler example prism.on("dashboardloaded", function (e, args) { args.dashboard.on('widgetready', (dashboard, args) => { var array = []; dashboard.widgets.toArray().forEach(widget => { if (widget.type === 'chart/bar') { widget.on('beforedatapointtooltip', (widget, query) => { let value = query.context.points[0].value; try { const convertedValue = convertToFullNumber(value); query.context.points[0].value = convertedValue; } catch (error) { console.error("Conversion error: " + error.message); // Optionally handle the error or show a fallback value query.context.points[0].value = value; // Keep the original value if conversion fails } }) } }); }); });How to target a specific cell/column to change the column in a pivot table?
I have a pivot table where I am trying to turn the "Activity date for billable time" light green (can be any color) in my widget edit script, along with its corresponding value from another column, 2025-08-18, in this example. I have tried a couple of different script but nothing seems to work: (function () { // === settings === var VALS = 'Activity date for billable time'; // sub-header text to match var COLOR = '#e6f4ea'; // light green var MAX_TRIES = 40, RETRY_MS = 150; // --- tiny helpers (no backticks) --- function getRoot(w){ return (w && ((w.$$container && w.$$container[0]) || (w.$container && w.$container[0]) || w.container)) || document; } function qAll(root, sel){ return Array.prototype.slice.call(root.querySelectorAll(sel)); } // Find runtime column index by reading row-1 sub-header text function getColByRow1Label(root, vals){ var tds = qAll(root, '.table-grid.table-grid--top td.table-grid__cell--row-1'); for (var i = 0; i < tds.length; i++){ var td = tds[i]; var textEl = td.querySelector('.table-grid__content div'); var txt = (textEl && (textEl.innerText || '').trim()) || ''; if (txt === vals){ var classes = td.className.split(/\s+/); for (var c = 0; c < classes.length; c++){ if (classes[c].indexOf('table-grid__cell--col-') === 0){ return parseInt(classes[c].split('--col-')[1], 10); } } } } return null; } // Inject a CSS rule so it survives re-renders/virtualization function injectCSS(root, col){ // remove any prior rule from this script var old = root.querySelector('style[data-col-color]'); if (old && old.parentNode) old.parentNode.removeChild(old); var css = '' + '.table-grid.table-grid--top td.table-grid__cell--row-1.table-grid__cell--col-' + col + ' .table-grid__content{' + 'background-color:' + COLOR + ' !important;' + 'border-radius:4px !important;' + '}\n' + '.table-grid:not(.table-grid--top) td.table-grid__cell--col-' + col + ' .table-grid__content{' + 'background-color:' + COLOR + ' !important;' + 'border-radius:4px !important;' + '}'; var style = document.createElement('style'); style.type = 'text/css'; style.setAttribute('data-col-color', 'col-' + col); style.appendChild(document.createTextNode(css)); (root || document.body).appendChild(style); } function runOnce(w){ var root = getRoot(w); if (!root || !root.querySelector('.table-grid')) return false; // DOM not ready yet var col = getColByRow1Label(root, VALS); if (col == null) return false; injectCSS(root, col); return true; } function schedule(w){ var tries = 0; (function tick(){ if (runOnce(w)) return; if (++tries < MAX_TRIES) setTimeout(tick, RETRY_MS); })(); } // hook into widget lifecycle widget.on('domready', function(w){ schedule(w); }); widget.on('refreshed', function(w){ schedule(w); }); // try immediately too schedule(widget); })(); : (function () { var COLOR = '#e6f4ea'; // light green // 1) Color the VALUE cells for the target column member widget.transformPivot( { type: ['value'], // only data cells columns: [{ // << CHANGE THIS to the exact dim used in the Columns panel >> dim: '[Program_Assessment_Response_Question.Program_Assessment_Question_Version_Name]', // << keep this as the visible member text in your header >> members: ['Activity date for billable time'] }] }, function (_metadata, cellEl) { // Paint value cells without touching alignment cellEl.style.setProperty('background-color', COLOR, 'important'); cellEl.style.setProperty('box-shadow', 'inset 0 0 0 9999px ' + COLOR, 'important'); cellEl.style.setProperty('border-radius', '4px', 'important'); } ); // 2) (Optional) Also color the COLUMN HEADER cell for that member // If your Sisense version supports targeting column headers: widget.transformPivot( { type: ['columnHeader'], // header cells along the columns axis columns: [{ dim: '[Program_Assessment_Response_Question.Program_Assessment_Question_Version_Name]', // << same dim as above members: ['Activity date for billable time'] }] }, function (_metadata, cellEl) { // Header DOM is the TD; center style already handled elsewhere, so only background var content = cellEl.querySelector('.table-grid__content') || cellEl; content.style.setProperty('background-color', COLOR, 'important'); content.style.setProperty('border-radius', '4px', 'important'); } ); })(); DRay any suggestions or anyone from your team have an idea?Flowiseai + Sisense = RAG from a dashboard (all widgets chart data value of a dashboard)
In the previous post, I showed how to use the Sisense Rest-API to get the data values of a specific widget and RAG them through the LLM. (ref https://community.sisense.com/t5/help-and-how-to/flowiseai-sisense-rag-from-widget-data-values/m-p/21591#M254) This time, I will introduce how to get the data values of all contained widget's chart for a dashboard that contains multiple widgets via Rest-API and implement RAG via LLM. When we implement a dashboard with Sisense, it contains multiple widgets that are needed to analyze one topic or theme, but it takes a lot of learning for customers to get comprehensive insights from various widget charts, and different users sometimes have different interpretations of the same data. Therefore, this post aims to overcome these problems by using LLM RAG to consolidate data from multiple widget charts in a dashboard to create a comprehensive and consistent insight or report. The basic code routine is similar to the previous post on RAG via LLM like GPT, where you enter the metadata and data values of one widget chart as the Context. The difference is that we use the REST-API to get all the widgetID(oid) values that the dashboard has through the dashboard ID, and then get the metadata of the widget chart through the dashboardID/widgetID. After obtaining the widget's datasource and jaql, run the url( your.sisnese.domian /api/datasources/${parsedJaqlQuery.datasource.title}/jaql) to return the widget chart's data, and repeat the process for all widgetIDs, storing them in memory for building the context for LLM. The next step is to input the contents of that memory (even if it's in json format) into the LLM as context, but the problem is that the LLM has a limited number of input tokens. If you have ten widget charts in a dashboard, you will exceed the maximum number of input tokens for gpt-4o and you will not get the desired answer from GPT. Therefore, we used a method to reduce the number of tokens in the Context that is passed to the final LLM by summarizing the chart data of each widget through LLM in the middle of the code. The prompt we used to summarize each widget's chart data is “const prompt = `Summarize the following widget data qualitatively and quantitatively:\n\n${JSON.stringify(widgetData)}`;”. In this process, we were able to successfully implement RAG by writing all widgets' chart contents to LLM as Context with only the dashboard id. Our target dashboard Prompt exemple for LLM RAG : Aggregate and analyze all widget chart data value from Sisense dashboard id : your-dahsboard-id using Flowiseai Custom tool for LLM(ex. GPT) function call node.js fetch api code start------ const fetch = require('node-fetch'); const apikey = 'your-sisense-api-key'; const gptApiKey = 'your-gpt-api-key'; const baseUrl = 'https://your-sisense-url'; const gptApiUrl = 'https://api.openai.com/v1/chat/completions'; const gptmodel = 'gpt-4o' const dashboardId = $dashboard_id; const getDashboardWidgets = async () => { const url = `${baseUrl}/api/v1/dashboards/${dashboardId}/widgets`; const options = { method: 'GET', headers: { 'Authorization': `Bearer ${apikey}`, 'Content-Type': 'application/json' } }; try { const response = await fetch(url, options); if (!response.ok) { throw new Error(`Error fetching dashboard widgets: ${response.statusText}`); } const widgets = await response.json(); return widgets.map(widget => widget.oid); } catch (error) { console.error(`Error in getDashboardWidgets: ${error.message}`); return []; } }; const getWidgetMetadata = async (widgetId) => { const url = `${baseUrl}/api/v1/dashboards/${dashboardId}/widgets/${widgetId}`; const options = { method: 'GET', headers: { 'Authorization': `Bearer ${apikey}`, 'Content-Type': 'application/json' } }; try { const response = await fetch(url, options); if (!response.ok) { throw new Error(`Error fetching widget metadata: ${response.statusText}`); } const metadata = await response.json(); return metadata; } catch (error) { console.error(`Error in getWidgetMetadata: ${error.message}`); return null; } }; const getJaql = (widgetStructure) => { const result = []; for (const panel of widgetStructure.metadata.panels) { for (const item of panel.items) { if (item && item.jaql) { result.push(item.jaql); } } } const payload = { datasource: widgetStructure.datasource, metadata: result }; return JSON.stringify(payload); }; const getWidgetChartData = async (jaqlQuery) => { try { const parsedJaqlQuery = JSON.parse(jaqlQuery); if (!parsedJaqlQuery.datasource || !parsedJaqlQuery.datasource.title) { throw new Error('Datasource title is missing in JAQL query'); } const jaqlUrl = `${baseUrl}/api/datasources/${parsedJaqlQuery.datasource.title}/jaql`; const jaqlOptions = { method: 'POST', headers: { 'Authorization': `Bearer ${apikey}`, 'Content-Type': 'application/json' }, body: jaqlQuery }; const response = await fetch(jaqlUrl, jaqlOptions); if (!response.ok) { throw new Error(`Error fetching widget chart data: ${response.statusText}`); } const chartData = await response.json(); return chartData; } catch (error) { console.error(`Error in getWidgetChartData: ${error.message}`); return null; } }; const summarizeWidgetData = async (widgetData) => { const prompt = `Summarize the following widget data qualitatively and quantitatively:\n\n${JSON.stringify(widgetData)}`; try { const response = await fetch(gptApiUrl, { method: 'POST', headers: { 'Authorization': `Bearer ${gptApiKey}`, 'Content-Type': 'application/json' }, body: JSON.stringify({ model: gptmodel, messages: [{role: "system", content: "You are a best data analyst."}, {role: "user", content: prompt}] }) }); if (!response.ok) { throw new Error(`Error summarizing widget data: ${response.statusText}`); } const summary = await response.json(); return summary.choices[0].message.content.trim(); } catch (error) { console.error(`Error in summarizeWidgetData: ${error.message}`); return null; } }; const delay = ms => new Promise(resolve => setTimeout(resolve, ms)); const fetchDashboardWidgetsData = async () => { try { const widgetIds = await getDashboardWidgets(); if (widgetIds.length === 0) { return `Error in getDashboardWidgets`; } const allSummaries = []; for (const widgetId of widgetIds) { const widgetDefinition = await getWidgetMetadata(widgetId); if (!widgetDefinition) { allSummaries.push({ widgetId, error: `Error fetching widget metadata for widget ${widgetId}` }); continue; } const jaqlQuery = getJaql(widgetDefinition); const widgetChartData = await getWidgetChartData(jaqlQuery); if (!widgetChartData) { allSummaries.push({ widgetId, error: `Error fetching chart data for widget ${widgetId}` }); continue; } const summary = await summarizeWidgetData(widgetChartData); if (!summary) { allSummaries.push({ widgetId, error: `Error summarizing data for widget ${widgetId}` }); continue; } allSummaries.push({ widgetId, summary }); // Add delay to avoid hitting rate limits await delay(1000); // Adjust the delay as needed } return JSON.stringify(allSummaries); } catch (error) { console.error(`Error in fetchDashboardWidgetsData: ${error.message}`); return `Error in fetchDashboardWidgetsData: ${error.message}`; } }; try { return await fetchDashboardWidgetsData(); } catch (error) { console.error(`Error in fetchDashboardWidgetsData: ${error.message}`); return `Error in fetchDashboardWidgetsData: ${error.message}`; } -------node.js fetch api code end RAG RESULT Screenshot generated raw text : Sisense Dashboard Widget Data Analysis Data Summary Weekly Reviews and Differences Data includes the number of reviews and differences from the previous week spanning from 1970 to 2024. Review counts show significant volatility with extreme increases or decreases in specific weeks. There were notable spikes in review counts in June 2020 and December 2020 (17,932 and 32,442 returns, respectively). The average number of reviews and standard deviations measure the variability in review counts. Review Star Rating Distribution Data includes 1,921 items categorized by weekly reviews with star ratings from 1 to 5. 5-star reviews are the most common, especially during Black Friday and Christmas seasons. The data consistently shows more positive reviews than negative ones. Number of Reviews by Keyword "Fast delivery" is the most mentioned keyword with 356,062 reviews. Keywords like "good quality" and "sensitive skin" indicate positive customer experiences. Major keywords emphasize customer loyalty and repeat purchase behavior. Weekly Reviews by Star Rating Comprising a total of 2,409 rows, with 5-star reviews being the most frequent at 844,372 mentions. The number of reviews increases over time, especially in recent years. Patterns of sudden increases or decreases in review counts in certain weeks and periods are observed. 2022 Review Statistics In 2022, there are a total of 373,173 reviews, with 254,374 including comments. Data includes reviews written in 53 languages across 46 countries worldwide. There are 10,880 instances of the sales code. Country-wise 2024 Review Data Comprising columns "country_name," "2024 Reviews," "EVOL(%)," and "Total reviews," indicating the number of reviews expected or recorded in 2024 by country. "EVOL(%)" shows the year-over-year growth rate in the number of reviews. Reviews by Language Categorizes reviews by "review_language_name" indicating the languages in which reviews were written. Includes the number of text reviews expected in 2024 and the growth rate from the previous year. Product Review Keywords Includes the number of reviews for keywords such as "acne scars," "bubble wrap," and "combination skin." Provides the number of reviews reflecting major aspects of feedback and related keywords. Product-wise Review Data Includes the unique number of reviews per product name and the growth rate from the previous year. Review counts are calculated by aggregating the number of unique review IDs. Mall Review Data Includes mall names and the number of reviews in 2024 and total reviews. "EVOL(%)" indicates the annual growth rate in the number of reviews. Country-wise User Review Data The total number of unique review IDs is very high, with Indonesia, the United States, Vietnam, Japan, and the Philippines being significant contributors. The lowest contributing countries are Myanmar, Mauritius, Azerbaijan, etc. Summary Review Trend and Volatility: The number of reviews fluctuates sharply during specific weeks, potentially due to marketing campaigns, product launches, or seasonal factors. Star Ratings and Customer Satisfaction: Positive reviews outnumber negative ones, indicating high customer satisfaction. Keywords and Customer Loyalty: Keywords highlight positive feedback on product quality and delivery service, leading to increased repeat purchase behavior. Country and Mall Analysis: The number of reviews in specific countries or malls reflects consumer engagement and market share, useful for strategic marketing planning. Data Completeness: Though some data may lack specific values, the overall structure and metrics are clearly defined. This analysis helps in understanding customer behavior and product performance, aiding in strategic marketing planning. * You can customize several prompts in the code.Flowiseai + Sisense = RAG from a widget data values
flowiseai : https://flowiseai.com/ flowiseai git : https://github.com/FlowiseAI/Flowise Leveraging flowiseai's LLM workflow, I created a custom tool and node.js-based fetch api code to enable augmented analysis and easy querying of data values for Sisense specific widgets with OpenAi GPT4o (You can use any llm including llama3, Anthropic, Huggingface,.... with same workflow). It took about 5 minutes to integrate and implement. code sample start : const fetch = require('node-fetch'); const apikey = 'your-api-key'; const baseUrl = 'https://your-sisense-url'; const dashboardId = $dashboard_id; const widgetId = $widget_id; const getWidgetMetadata = async () => { const url = `${baseUrl}/api/v1/dashboards/${dashboardId}/widgets/${widgetId}`; const options = { method: 'GET', headers: { 'Authorization': `Bearer ${apikey}`, 'Content-Type': 'application/json' } }; try { const response = await fetch(url, options); if (!response.ok) { throw new Error(`Error fetching widget metadata: ${response.statusText}`); } const metadata = await response.json(); console.log('Widget Metadata:', JSON.stringify(metadata, null, 2)); return metadata; } catch (error) { return `Error in getWidgetMetadata: ${error.message}`; } }; const getJaql = (widgetStructure) => { const result = []; for (const panel of widgetStructure.metadata.panels) { for (const item of panel.items) { if (item && item.jaql) { result.push(item.jaql); } } } const payload = { datasource: widgetStructure.datasource, metadata: result }; console.log('Generated JAQL Payload:', JSON.stringify(payload, null, 2)); return JSON.stringify(payload); }; const getWidgetChartData = async (jaqlQuery) => { try { const parsedJaqlQuery = JSON.parse(jaqlQuery); if (!parsedJaqlQuery.datasource || !parsedJaqlQuery.datasource.title) { throw new Error('Datasource title is missing in JAQL query'); } const jaqlUrl = `${baseUrl}/api/datasources/${parsedJaqlQuery.datasource.title}/jaql`; console.log(`JAQL URL: ${jaqlUrl}`); console.log(`JAQL Query: ${JSON.stringify(parsedJaqlQuery, null, 2)}`); const jaqlOptions = { method: 'POST', headers: { 'Authorization': `Bearer ${apikey}`, 'Content-Type': 'application/json' }, body: jaqlQuery }; const response = await fetch(jaqlUrl, jaqlOptions); if (!response.ok) { throw new Error(`Error fetching widget chart data: ${response.statusText}`); } const chartData = await response.json(); console.log('Widget Chart Data:', JSON.stringify(chartData, null, 2)); return chartData; } catch (error) { return `Error in getWidgetChartData: ${error.message}`; } }; const fetchWidgetData = async () => { try { const widgetDefinition = await getWidgetMetadata(); if (typeof widgetDefinition === 'string' && widgetDefinition.startsWith('Error')) { return widgetDefinition; } : code sample end dashboards/61bfddcd53ccb90035ce1626/widgets/61bfddcd53ccb90035ce1631 is basic prompt style : dashboards/61bfddcd53ccb90035ce1626/widgets/61bfddcd53ccb90035ce1631 Analyze widget data value gpt-4o response : GPT 4o generated text through RAG from sisense rest api: The analysis of the widget data values from the dashboard 61bfddcd53ccb90035ce1626 and widget 61bfddcd53ccb90035ce1631 is as follows: Headers: Mall Region - Represents the regions or countries where the malls are located. Product Selling Codes - Represents the number of product selling codes in each region. Top 10 Regions by Product Selling Codes: Indonesia: 15,317 product selling codes Viet Nam: 7,241 product selling codes Philippines: 6,494 product selling codes Malaysia: 4,369 product selling codes Singapore: 3,151 product selling codes South Korea: 2,447 product selling codes Thailand: 1,683 product selling codes Russia: 1,647 product selling codes Japan: 1,608 product selling codes United States: 1,048 product selling codes Insights: Indonesia leads with the highest number of product selling codes at 15,317. Viet Nam and Philippines follow with 7,241 and 6,494 product selling codes, respectively. Malaysia and Singapore also have a significant number of product selling codes, with 4,369 and 3,151, respectively. South Korea, Thailand, Russia, Japan, and United States round out the top 10 with over 1,000 product selling codes each. Summary: The data indicates a high concentration of product selling codes in Southeast Asia, especially in Indonesia, Viet Nam, and the Philippines, suggesting robust market activity in those regions. The presence of significant numbers in countries like Japan, Russia, and the United States also highlights strong market participation there. Data Table: Mall Region Product Selling Codes Croatia 5 Myanmar 10 Azerbaijan 15 Hungary 15 Italy 20 Romania 21 Nepal 21 Chile 22 Slovakia 23 Pakistan 24 Estonia 25 Mauritius 26 Lebanon 37 Nigeria 38 Lithuania 41 Bangladesh 42 Kenya 42 France 49 Spain 50 United Arab Emirates 54 South Africa 57 Switzerland 68 Mexico 80 Denmark 86 Finland 107 Canada 117 Saudi Arabia 132 Hong Kong China 148 China 180 Sweden 190 Germany 199 Norway 224 Australia 242 Ukraine 269 Poland 296 United Kingdom 298 Turkey 307 India 542 Taiwan China 583 United States 1048 Japan 1608 Russia 1647 Thailand 1683 South Korea 2447 Singapore 3151 Malaysia 4369 Philippines 6494 Viet Nam 7241 Indonesia 15317 This data can help identify key regions for potential business expansion and investment opportunities in different markets. *next work will be RAG with Sisense Dashboard including multi widegts
