Dashboard Script for Automatic Filter Cascading
Dashboard Script for Automatic Filter Cascading This dashboard script below can be used to synchronize the selections of a set of filters. For example, if the first filter is set to ABC, all subsequent filters listed will automatically be updated to ABC as well. The script also replicates the disabled state from the first filter to the other filters. Look below for an example of Elasticube and dashboard. Please note: Our community website does not currently support .dash (dashboard) and .smodel (Elasticube) files. Please change the extensions of the files before importing them into your environment. Note that once imported, the cube still needs to be built. Example Use Case: When dealing with multiple date fields in a fact table, this article provides two options, both of which require duplicating all rows in the fact table as many times as the number of date fields. For example, if you have three date fields that you need to analyze your KPIs by, you'll have to have three copies of the fact table. While these approaches are easy to implement, oftentimes they are not feasible and scalable solutions due to the size of the data. A more scalable alternative is to duplicate the date dimension table, which is substantially smaller than a fact table (e.g. a date dimension table containing 10 years of data only has either 3,652 or 3,653 rows), then connect each date field from the fact to the corresponding date dimension table. See the picture below for an example. The next step is to add each date field as a filter to the dashboard. Keep only the first filter editable and lock the other filters. The subsequent filters will be updated automatically as users set the first filter. Ensure that the filters are correctly toggled on/off in each widget's setting. In the example above, the first widget shows the number of hospital admissions by the admission date. Therefore, only the first (Admission Date) filter should be turned on. The last step is to add the dashboard script that automatically cascades the selection of the first filter to the subsequent filters. In the filterNames variable, specify the names of the filters, starting from the first filter, from which the selections will be replicated to other filters. This is the only part of the code that requires your input. /*************************** This script is used to synchronize the selections of a set of filters ***********************/ /** E.g. if the first filter is set to ABC, all subsequent filters listed will automatically be updated to ABC as well ***/ /***************** The script also replicates the disabled state from the first filter to the other filters **************/ //Specify the names of the filters, starting from the first filter, from which the selections will be replicated to other filters //This is the only part of the code that requires your input var filterNames = ['Admission Date', 'Discharge Date', 'Last Visit Date'] //Every time a filter is changed, this code is executed dashboard.on('filterschanged',function(d) { //Find the first filter by name var filter1FilterObject = dashboard.filters.$$items.find((item) => { if (item.jaql && item.jaql.title.indexOf(filterNames[0]) !== -1) { return true; } }); //Get the JAQL filter selection of the first filter var filter1FilterObjectJAQL = filter1FilterObject.jaql; //Get the JAQL filter disabled state of the first filter var filter1FilterObjectDisabled = filter1FilterObject.disabled; //Define an array for the subsequent filters' objects var filterObjects = new Array(); //Find each of the subsequent filters by name for(i=1 ; i<filterNames.length ; i++) { filterObjects[i-1] = dashboard.filters.$$items.find((item) => { if (item.jaql && item.jaql.title.indexOf(filterNames[i]) !== -1) { return true; } }); } //Update the properties of the subsequent filters to match the first filter for(i=0 ; i<filterObjects.length ; i++) { if(typeof filterObjects[i] != 'undefined') { filterObjects[i].jaql.filter = filter1FilterObjectJAQL.filter; filterObjects[i].disabled = filter1FilterObjectDisabled; } if(filter1FilterObjectJAQL.datatype == 'datetime') { filterObjects[i].jaql.level = filter1FilterObjectJAQL.level; } } //Refresh the dashboard dashboard.refresh(); });
Color Heatmap
Version: 1.4 Disclaimer Use the download Link for L2023.7 or newer. For older versions of Sisense, use the other attached version of the ColorHeatmap plugin Introduction This article explains how to install and configure the Color Heatmap widget as seen below. A heat map (or heatmap) is a graphical representation of data where the individual values contained in a matrix are represented as colors Purpose/Benefits Use heatmap’s to represent relationship between 2 dimensions. The below example demonstrate the relationship between s Sale Person to the Categories he sold: How to Install and Configure the Extension Step 1 - Add The Plugin Download the attachment and unzip the contents into your C:\Program Files\Sisense\PrismWeb\plugins\ folder. If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder. If the plugins folder doesn't exist, just create it. After those files have been unzipped there, you may also have to restart the web server. Now, when you create a new widget the "Color Heatmap" widget should show up in the list of options. Step 2 - Create the "Color Heatmap" Widget On your dashboard, click the Create Widget button and select Advanced Configuration. Next, select the "Color Heatmap" from the drop down list. For this you will need to specify the X-Axis and Y-Axis dimension, and Values. For the below example, we have added the following: X-Axis from “Full Name”, Y-Axis from “Category Name” and Values from Total Sales. As a result, the above Color Heatmap created a matrix of Sale Person to Category showing the Total Sales for each square with its associated color. The widget also offers the following design options: Legend Position– sets the position for the legend (Top, Bottom, Left or Right) X-Axis Position– sets the position of the X-Axis labels (Top or Bottom) Y-Axis Position - sets the position of the X-Axis labels (Left or Right) Click on cell set filters– when on, clicking on a specific cell will automatically set dashboard filter to the values of X-Axis and Y-Axis --------- Sort by Day of week solution: In order to change the order of the x or y-axis for "Day of Week" Name to show as Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday is this order please use the below solution: Step 1: Change the x/y axis to numeric field - "Day of Week Number" Step 2: Use the below script on the widget: var list = { '1': 'Sunday', '2': 'Monday', '3': 'Tuesday', '4': 'Wednesday', '5': 'Thursday', '6': 'Friday', '7': 'Saturday' } widget.on('ready',function(scope){ var item = '[widgetid="' + scope.oid + '"]' var lmnts if (prism.$ngscope.appstate === "widget") { lmnts = $('.highcharts-xaxis-labels').find('tspan') } else { lmnts = $(item).find('.highcharts-xaxis-labels').find('tspan') } for (var i=0; i<lmnts.length; i++) { if ($(lmnts[i]).text() && list[$(lmnts[i]).text()]) { $(lmnts[i]).text(list[$(lmnts[i]).text()]) $(lmnts[i]).parent()[0].setAttribute("transform", "translate(0, 0) rotate(0)") } } }); References/Notes The Heatmap will not render more then 100,000 pointsEmbedding 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.'); })Changing Value Label's Font Size, Weight And Rotation
UPDATED SCRIPT IN COMMENTS Introduction Value labels at the floating figures above a series in a bar chart, column chart, or line chart. Purpose/Benefits Changing value labels can be useful to make a measure stand out more. Example Steps 1. Open the Edit Script window in the widget editor. 2. Paste the below code. 3. Change "YourMeasure" to the measure name you would like to change. (note: be sure to keep the quotes) 4. Click Save 5 Refresh the dashboard page and click Apply. /******* user configuration **********/ var seriesName = "YourMeasure"; /*************************************/ widget.on('processresult', function(sender, ev){ var data = _.find(ev.result.series, function (ser) {return ser.name == seriesName}).data _.each(data, function(value){ //enable dataLabels, change its font, make it bold and rotate by 270 value.dataLabels = {enabled:true, style:{'fontSize':'35px', 'fontWeight':'bold'}, rotation: 270} }) })Retrieve All Widget IDs, Titles And Types In A Dashboard
1) Open the Developers Console (press F12 in Chrome.) 2) Navigate to Console tab: 3) In the console prompt line, enter the following script and press Enter: 4) prism.activeDashboard.widgets.toArray() 5) This results in a printout of an object with the widget ID, title and data type:Align Numeric Values to the Right on a Table Widget
This article represents a solution for aligning numeric content to the right on any Table widget. As per the latest Sisense updates, 'domready' event changes are sometimes overwritten. This solution reacts to any Table widget change (new content displayed, the page has been changed, etc) and applies CSS formatting.
