Converting an Existing Sisense Widget to a Dynamic ComposeSDK Widget Component
Using either the widget Sisense API's, or the Sisense widget JS API object, it is possible to determine all components and parameters of a widget required to create an equivalent dynamic ComposeSDK widget component. This approach allows the extraction of all components and parameters of a widget, enabling the creation of an equivalent dynamic ComposeSDK widget component without directly referencing or relying on a specific widget or dashboard ID. The metadata of an existing widget contains all the information needed to create a dynamic version of an existing Sisense widget. It is also possible to use an existing widget ID and dashboard ID to render an existing widget in ComposeSDK, but this does not take full advantage of the capabilities of ComposeSDK to generate new widgets directly.
How To Order A Stacked Column Chart Using A BreakBy
Introduction When adding a break by field to an ordered column chart, the order does not exist anymore. We have created this post in order to workaround this problem. Steps In order to use the script, you need to follow the next steps: 1. Edit the widget 2. In the widget Editor, click on the widget menu button and choose Edit Script (this will open a new page for the script editing) 3. In the widget Editor window paste the following script: var categories= ["value desc"]; //Manual order\"value asc"\"Value desc" var breakBy = [ ]; // ---------- DO NOT EDIT BELOW THIS LINE ---------- var findIndex = function(val, list) { if (list=='breakby') { return breakBy.indexOf(val) } else if (list=='categories') { return categories.indexOf(val) } }; widget.on('processresult',function(se,ev){ //If categories array contains "value asc" or "value desc", sort categories by their total values. //The sorted categories will be placed in the category array used by the manual sorting script if (categories.length>0&&categories[0] === "value desc"){ categories = sortColumnsByValue(ev,"desc"); } else if (categories.length>0&&categories[0] === "value asc"){ categories = sortColumnsByValue(ev,"asc"); }; //determine if we should sort categories or breakbys categoryStatus=false; breakStatus=false; for (p=0; p<ev.widget.metadata.panels.length; p++){ if (ev.widget.metadata.panels[p].name=="categories" && ev.widget.metadata.panels[p].items.length>0 && categories.length>0){ categoryStatus=true; } else if (ev.widget.metadata.panels[p].name=="break by" && ev.widget.metadata.panels[p].items.length>0 && breakBy.length>0){ breakStatus=true; } }; if (categoryStatus){ ev.result.series.forEach( function(bb) { newData= new Array(categories.length); var blankcat; bb.data.forEach( function(cat) { if (typeof cat.selectionData !== 'undefined'){ newData[findIndex(cat.selectionData[0].trim(), 'categories')]=cat; } else{ blankcat=cat; } }) for (i=0; i<newData.length; i++) { if (typeof newData[i] === 'undefined') { newData[i]=blankcat; } } bb.data=newData; }); ev.result.xAxis.categories=categories; }; if (breakStatus) { ev.result.series.sort(function(a,b){ if (findIndex(a.name, 'breakby') < findIndex(b.name,'breakby')) { return -1 } else if (findIndex(a.name,'breakby') > findIndex(b.name,'breakby')) { return 1 } return 0; }); }; }); //Sort categories by total value: functions //Sort ascending \ descending function sortCatsByValueAsc(a,b){ var aValue = a.value; var bValue = b.value; return ((aValue < bValue) ? -1 : ((aValue > bValue) ? 1 : 0)); } function sortCatsByValueDesc(a,b){ var aValue = a.value; var bValue = b.value; return ((aValue < bValue) ? 1 : ((aValue > bValue) ? -1 : 0)); } //replace "category" object with the category name function sortedCatsNames(arr){ for (var i=0 ; i<arr.length ; i++){ arr[i] = arr[i].name; } } //Main function function sortColumnsByValue (arg, sorting){ //Create an array to contain all categories' original index, name and value var cats = []; for (var c=0 ; c<arg.result.xAxis.categories.length ; c++){ cats.push({index:c ,name: arg.result.xAxis.categories[c],value:0}); } for (var i=0; i<arg.result.series.length; i++){ for (var j=0 ; j<arg.result.series[i].data.length;j++){ cats[j].value+=arg.result.series[i].data[j].y; } } //Sort Array by category value var sortCats; if (sorting==="desc"){ sortCats = cats.sort(sortCatsByValueDesc); } else { sortCats = cats.sort(sortCatsByValueAsc); } //Replace objects with the category name sortedCatsNames(sortCats); return sortCats; } ***All script rights reserved to Adi Lewenstein Script Modifications You can change the order type between Ascending (value asc) and Descending (value desc) in the first row of the script var categories= ["value desc"]; //Manual order\"value asc"\"Value desc" 4. click on Save and close the window 5. Go back to the widget Editor page (Should still be open) and refresh the browser ( The script should already affect the widget ) 6. Click apply ***Sisense orders the values in an alphabetical order, If you wish to order the values in a different order, create a new column and add a number at the beginning of each value according to the order you want them to appear.Embedding Custom Visualizations Within A Widget
Introduction This document outlines the process for overriding an existing chart with a Highcharts Donut Pie chart. For a more robust solution, it is recommended to create a custom plugin as that would allow non-technical users to create charts without editing scripts. However, if this is a one-off solution, an existing widget’s script can be overridden to allow for integration with 3rd party charting solutions. Steps Here we will explain how to do it in detailed steps. STEP 1 - CREATE THE SISENSE WIDGET Try to be consistent with the image size along the post. STEP 2 - COPY IN THE CUSTOM JAVASCRIPT Click on the Edit Script button for the widget to pull up the script editor. Open the attached JS file, then copy and paste the code into the script editor. The only thing that might need to be changes is to change the Highcharts div container ID. If you want more than 1 of these charts on the same dashboard, they need to have unique IDs. So just change the value of the JavaScript variable ChartDivName from “MyHighcharts” to “MyHighcharts2” or any other unique ID. Click the save button, and reload the dashboard. This code requires there to be a dimension specified as a Category, a second dimension specified under Break By, and a metric specified as a value. The screenshot below shows the setup used for the example. Overview of custom JavaScript code The code attached consists of the following 4 tasks: 1. RETRIEVE DATA FROM THE WIDGET The results of the ElastiCube query are stored in the widget object, which is accessible through the sender variable. The dimensions/value names and data points are contained within the sender.rawQueryResult property. This data set will need to be transformed to fit the Highcharts data format. 2. FORMAT THE DATA The data comes back from the query as an array consisting of data points in the following format: [CategoryName, DataPointName, DataPointValue]. This will need to be modified to match the format required by the desired 3rd party visualization tool. 3. REPLACE SISENSE CHART W/ NEW VISUALIZATION The stacked column chart specified in this example gets rendered by the SiSense engine as a SVG element. This block of code finds the newly rendered stacked bar chart and replaces the svg tag with a div container for the Highcharts.. 4. CREATE THE NEW VISUALIZATION At this point, the data is formatted appropriately and the SiSense chart has been replaced with an empty container for the 3rd party visualization. This code block uses the Highcharts API to create a new donut pie chart within the specified div container. Customizing the code Following steps one and two will create a donut pie chart based on the given data set, but what if the required 3rd party chart was slightly different? The JavaScript would need to be customized to meet the requirements of the 3rd party chart. When pulling data from SiSense, the key object to look at is the sender.rawQueryResult variable. This object contains an array headers, which contains information about the dimensions and metrics specified by the widget. It also includes a variable values, which contains an array of all data points. These two objects can be looped through to build a data set compatible with the desired 3rd party visualization tool. See also Here we will put links to Highcharts Pie Donut Chart - http://www.highcharts.com/demo/pie-donut SiSense Widget API - https://sisense.dev/guides/customJs/jsApiRef/ The code snippet: widget.on('ready', function(sender, ev){ console.log('starting custom code'); /************************************************** *** Retrieve data from the widget *** *** *** *************************************************/ var ChartTitle = sender.title; var ChartDivName = "MyHighchart"; //this ID must be unique for the whole HTML page, //so if you use multiple custom charts in the same dashboard then this will need to be changed //fetch data from widget var QueryResult = sender.rawQueryResult; var ChartMetric = QueryResult.headers[2]; var ChartLevel1 = QueryResult.headers[0]; var ChartLevel2 = QueryResult.headers[1]; /************************************************** *** Format the data for Highcharts *** *** *** *************************************************/ var ChartDataSet = QueryResult.values; var ChartCategories = []; var ChartValues = []; var ChartColors = Highcharts.getOptions().colors; //loop through data to build top level $.each( ChartDataSet, function() { //get category and value var MyCategoryName = this[0].text; var MyDataPointName = this[1].text; var MyDataPointValue = this[2].data; var MyDataPointColor; //look for existing category w/ matching name var ExistingCategory = $.grep( ChartCategories, function(el) { return (el.name == MyCategoryName ); }); //does it exist already? if ( ExistingCategory.length == 0 ) { //category doesn't exist, so add it MyDataPointColor = ChartColors[ ChartCategories.length ]; var NewCategory = { name: MyCategoryName, y: MyDataPointValue, color: MyDataPointColor }; ChartCategories.push( NewCategory ); } else { //category exists, so just add value to the total ExistingCategory[0].y += MyDataPointValue; MyDataPointColor = ExistingCategory[0].color; } //create the data point var brightness = (ExistingCategory.length * 0.1) + 0.1; var NewDataPoint = { name: MyDataPointName, y: MyDataPointValue, color: Highcharts.Color(MyDataPointColor).brighten(brightness).get() }; ChartValues.push( NewDataPoint ); }); /************************************************** *** Replace SiSense chart with Highchart *** *** container *** *************************************************/ //get object id of current indicator var ObjectID = sender.oid; //get the current widget var MyWidget = $('widget[widgetid=' + ObjectID + ']'); if (MyWidget.length == 0) { MyWidget = $('.widget-body'); } // get the height and width var MyChart = MyWidget.find("svg"); var MyChartWidth = MyChart.attr("width"); var MyChartHeight = MyChart.attr("height"); //set the widget's div id and clear all children var ChartDiv=document.createElement('div'); ChartDiv.setAttribute("id",ChartDivName); ChartDiv.setAttribute("style","width: " + MyChartWidth + "px; height: " + MyChartHeight + "px; margin: 0 auto"); MyChart.after(ChartDiv); MyChart = $("#" + ChartDivName); MyWidget.find("svg").remove(); /************************************************** *** Create the Highchart within the *** *** Highcharts div container *** *************************************************/ // Create the chart $('#' + ChartDivName).highcharts({ chart: { type: 'pie' }, title: { text: ChartTitle }, yAxis: { title: { text: 'title' } }, plotOptions: { pie: { shadow: false, center: ['50%', '50%'] } }, tooltip: { valueDecimals: 0, valuePrefix: '$' }, series: [{ name: ChartLevel1, data: ChartCategories, size: '60%', dataLabels: { formatter: function () { return this.y > 5 ? this.point.name : null; }, color: 'white', distance: -30 } }, { name: ChartLevel2, data: ChartValues, size: '80%', innerSize: '60%', dataLabels: { formatter: function () { // display only if larger than 1 return this.y > 1 ? '<b>' + this.point.name + ':</b> $' + Highcharts.numberFormat(this.point.y,0) : null; } } }] }); console.log('custom code finished.'); })How to Calculate YTD by Month in a pivot table?
Question How to Calculate YTD by Month in a Pivot Table? Answer If you want to show YTD by Month next to your monthly amount you can utilize the "Running Sum" Quick Calculation. If you want to show this next to the monthly amount, just bring in that same value again. If you just want to show total for the time period you can use the "Grand Total" on columns Custom function: =RPSUM([Total BUDGET],12) This formula takes the monthly budget for the year (as filtered by on my dashboard filter) and cumulatively adds it to the previous month's budget and cumulates from there. =RPSUM([Total BUDGET]) was working, but only for 2 months at a time (Jan + Feb, reset for March + April, etc). By adding the 12 it knows to repeat after 12 periods, so it takes care of the entire year. Apply the RPSUM with 12 periods to my Budget, Revenue, and Variance (Revenue - Budget) columns.Adding additional dimensions to the Scatter Map widget tooltip
Adding additional dimensions to the Scatter Map widget tooltip Additional dimensions can be added to the hover tooltip of the Scatter Map widget type, beyond the default limit of three, to include more information from other dimensions about a location in a Scatter Map widget. This can be accomplished by using a combination of the widget's before query event to add additional dimension data for the hover tooltips and the before datapoint tooltip event to incorporate these dimensions into the tooltip. This method of modifying the query using the "beforequery" event can also be applied to all other widget types // Add extra parameters to be used in tooltips by modifying query widget.on('beforequery', function (se, ev) { // Initial number of widget metadata panels excluding filter panel widget.initialPanelSizeExcludingFilterPanel = ev.query.metadata.filter((panel) => { return panel.panel !== "scope" }).length; // Extra dimensions to show in tooltip, should return a single result, include as many as needed, just add to array // Jaql Objects can be copied from other widgets from the prism.activeWidget.metadata.panels via the browser console // Modify JAQL as needed, title of JAQL is used in tooltip and can be modified to any string widget.extraDimensionJAQL = [ { "jaql": { "table": "Category", "column": "Category ID", "dim": "[Category.Category ID]", "datatype": "numeric", "merged": true, "agg": "count", "title": "Unique Category ID" } }, { "jaql": { "table": "Country", "column": "Country ID", "dim": "[Country.Country ID]", "datatype": "numeric", "merged": true, "agg": "count", "title": "Unique Country ID" } }, ] // Add to default widget query the extra dimensions to be used in tooltips ev.query.metadata = ev.query.metadata.concat(widget.extraDimensionJAQL) }); // Add extra dimensions added with beforequery object to ScatterMap tooltip widget.on("beforedatapointtooltip", (event, params) => { // Convert query results to include only the additional dimensions, and formatted for tooltip template var onlyAdditionalDimensions = widget.queryResult.$$rows.map((withoutDefaultDimensionOnlyAdditional) => { // Remove the default dimensions, first part of row result array var withoutDefaultDimensionOnlyAdditional = withoutDefaultDimensionOnlyAdditional.slice(widget.initialPanelSizeExcludingFilterPanel) // Format for tooltip template, include title from JAQL var extraDimensionObj = withoutDefaultDimensionOnlyAdditional.map((extraDimensionValue, index) => { // Use extraDimensionJAQL for label in tooltip return { "text": extraDimensionValue.text, "title": widget.extraDimensionJAQL[index].jaql.title } }) return extraDimensionObj }); // Object to store extra dimensions params.context.marker.extraDimension = {}; // Use matching queryIndex for tooltip of additional dimensions params.context.marker.extraDimension.arr = onlyAdditionalDimensions[params.context.marker.queryIndex]; // Template for tooltip, modify as needed params.template = ` <div class='geo-text'>{{ model.marker.name }}</div> <div class='measure-holder' data-ng-if='model.measuresMetadata.sizeTitle'> <div class='measure-title slf-text-secondary'>{{ model.measuresMetadata.sizeTitle }}:</div> <div class='measure-value'>{{ model.marker.sizeObj.text }}</div> </div> <div class='measure-holder' data-ng-if='model.measuresMetadata.colorTitle'> <div class='measure-title slf-text-secondary'>{{ model.measuresMetadata.colorTitle }}:</div> <div class='measure-value'>{{ model.marker.colorObj.text }}</div> </div> <div class='measure-holder details-measure-holder' data-ng-if='model.measuresMetadata.detailsTitle'> <div class='measure-title slf-text-secondary'>{{ model.measuresMetadata.detailsTitle }}:</div> <div class='measure-value' data-ng-if="!model.marker.detailsObj.arr">{{ model.marker.detailsObj.text }}</div> <div class="details-wait" data-ng-if="model.marker.detailsObj.pendingDetails"></div> <div data-ng-if="model.marker.detailsObj.arr"> <div class="details-value" data-ng-repeat="a in model.marker.detailsObj.arr">{{a.text}}</div> <div class="details-counter" data-ng-if="model.marker.detailsObj.hasMore"> <div class="details-counter">...</div> <div class="details-counter"> {{'smap.ttip.firstres'|translate:(args={count:model.marker.detailsObj.arr.length})}}</div> </div> </div> </div> <div data-ng-if="model.marker.extraDimension.arr"> <div data-ng-if='model.measuresMetadata.colorTitle'> <div class='measure-holder' data-ng-repeat="a in model.marker.extraDimension.arr"> <div class='measure-title slf-text-secondary'>{{a.title}}:</div> <div class="measure-value extra-dimension-value">{{a.text}}</div> </div> </div> </div>`; }); The JAQL can be changed to any valid JAQL object, and the tooltip template can also be further modified.Conditionally Colored X-Axis Labels On Bar Chart
Introduction The code below allows you to color the x axis labels for each category of a bar chart based on the color of a secondary series. It also then hides the secondary series from being plotted. Steps STEP 1 The first step is to create a bar chart widget and add primary and secondary measures (series). STEP 2 Add a conditional color by first clicking on the color button under the secondary series and then clicking on conditional. Set your desired colors, then click OK and ensure bar colors are as intended. STEP 3 Open the widget editor (pencil) and click edit script (options). Paste the following code into the script editor: // The following Script will conditionally color the X Axis labels for series 0 (first value series) // based on the conditional color values specified in series 1 (second value series). // It will then hide series 1 from displaying widget.on('processresult',function (se, ev) { var cats = ev.result.xAxis.categories; // Stores the categories displayed on the X axis var colors = []; // Creates empty color array corresponding to category array for (var i = 0; i < cats.length; i++) { // Stores conditional colors for each element from series 1 colors[i] = ev.result.series[1].data[i].color; } ev.result.series.splice(1,1); // Removes Series 1 ev.result.xAxis.labels.formatter = function() { // Formats each element in series 0 var catIndex = cats.indexOf(this.value); // Searches cats array for each label and creates index if (catIndex > -1) { // If the label value is present in cats array, color label based on corresponding value from colors array catColor = colors[catIndex]; return '' + this.value + ''; // Returns HTML format } else { return this.value; // Returns default format if label is not present in cats array } } }) The secondary series should now be hidden, and the axis labels should be colored accordingly. STEP 4 Hit Save. Refresh the page. Hit Apply. Note: This script may be tweaked to work with other chart types. Enjoy!Auto zoom Area Map widget to a specific country on load (Linux)
Auto zoom Area Map widget to a specific country on load To test this solution, you must create a dashboard based on Sample ECommerce and an Area Map widget based on a Country column. Once it is done - please go to the widget editor, click three dots select Edit Script, then paste the following script there: As a result, we have our Area Map widget zoomed to Germany on the initial load: const countryName = 'Germany' widget.on('processresult', (scope, args)=>{ saveMapObj() }) widget.on('domready', (scope, args)=>{ zoomToCountry(L.geoJson(widget.queryResult.geoJson), window.currentMap, countryName); }) function zoomToCountry(geoData, map, countryName) { // Create a new empty GeoJSON object to store only the selected country's feature(s) geoData.eachLayer(function (layer) { if (layer.feature.properties.name === countryName) { map.fitBounds(layer.getBounds()); // Zoom to country bounds } }) } function saveMapObj() { let leafletHookStatus = $$get(prism, 'autoZoomAreaMap.leaflet.initHookAdded') if(!leafletHookStatus) { L.Map.addInitHook(function () { window.currentMap = this }) $$set(prism, 'autoZoomAreaMap.leaflet.initHookAdded', true); } } [ALT Text: A map of Europe with colored regions indicating specific areas. Central Europe is highlighted in teal, while other areas appear in gray. The United Kingdom is visible to the northwest. The date displayed at the top indicates June 10, 2025.] DO NOT CHANGE!!! 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.Turning zeroes to blanks/empty cell when Zero
Symptoms Sometimes with a pivot table full of many columns with zero and only a few non-zero values, you want the zeroes to display as empty-string. That way the nonzero values really stand out. (This is the opposite of this: Solved: nulls to 0 in pivot - Sisense Community ) Below is a widget-level script suggested by tech support which worked for me. Solution widget.transformPivot( { type: ['value'] }, function setCellBackground(metadata, cell) { if(cell.content.trim() == 0) cell.content = ' ' } )
