Clickable URL Links In Pivot Rows
Render existing HTML code stored in your data model Follow the steps below in order to URL clickable within a pivot table. Some steps are different depending on whether your Sisense instance is running on Windows or Linux. You can check whether your Sisense instance is running on Linux or Windows by following the steps in this article: Check whether running Sisense on Linux or Windows Common Steps 1. Create a custom column within the table with the URLs you would like to make clickable. In this example I called it HTMLURL Craft the HTML version of the link using <a href> tags. I used column "Link" as the URL and column "Name" as the display text '<a href="' + [Link] + '" target="_blank">' + [Name] + '</a>' This will create a column with the URL wrapped with HTML elements. 3. Rebuild the cube to include the custom column 4. Create a pivot table with the new column Additional Steps for Sisense on Linux These steps will only need to be performed once and can only be taken by an Admin or Sys Admin in the Sisense instance. 5. Go to Admin tab > System Management > Configuration 6. Within the resulting window, expand the "Pivot Table" section, and toggle on "Allow rendering Pivot Table content as HTML" 7. Then scroll down to the bottom of the page and click Save 8. Check back on your pivot widget, it should be rendering the HTML as a hyperlink. Additional Steps for Sisense on Windows 5. To render the HTML, we need to edit the the script of the widget. Within the widget editor, click the 3 dots at the top right hand corner, then click "Edit Script" 6. Paste the following script into the script editor window. Then click Save. widget.on('ready', function(){ var tags = $('tbody span:contains("<")'); for(var i = 0; i<tags.length; i++){ $(tags[i]).replaceWith(String(tags[i].textContent)) } }); 7. Close the script window and refresh the widget page. The result should be a formatted link 8. Save the newly edited widget by hitting "Apply" Limitations File exported to Excel/CSV will contain not rendered HTML code. Using capabilities of Pivot API (for Linux only) to add additional HTML code on the client side to links stored in cube. Add a custom field in your data model and compute there your link. Your link should have the following view: https://example.com?displayedValue=[SomeValue]&{optionalParameters} In terms of this link, displayedValue is a value that a user will see instead of the link on the pivot Build your cube. Add created field/fields to your Pivot widget and apply the following widget's script: var linkRowNumber = [ 1, 3 ]; //Columns' numbers where we need to render links. Numeration in JS starts from 0. var displayedValue = 'displayedValue'; //Name of the query parameter that stores displayed value for link var defaultDisplayedValue = 'Link'; //This value will be displayed on top of the link, if another value was not found widget.transformPivot( { type: ['member'] }, function setCellBackground(metadata, cell) { if (metadata.rowIndex && linkRowNumber.includes(metadata.colIndex)) { cell.content = "<a href=" + cell.content + ' target="_blank">' + getParameterByName(displayedValue , cell.content) + "</a>"; //Prepare link cell.contentType = "html"; } } ); function getParameterByName(name, url) { name = name.replace(/[\[\]]/g, "\\$&"); var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"); var results = regex.exec(url); if (!results) return defaultDisplayedValue; if (!results[2]) return defaultDisplayedValue; return decodeURIComponent(results[2].replace(/\+/g, " ")); } Now, links will be rendered by the widget's script. Also, when you export this Pivot into Excel/CSV, these links will be shown as valid links without any additional HTML code: Enjoy!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: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.How to Change the Pivot Header Row Style with TransformPivot
How to Change the Pivot Header Row Style with TransformPivot This solution is based on Pivot 2.0 API supported in Sisense version L8.2.1 or later (not supported in Sisense for Windows). For this solution, we shall use cellStyle from Pivot 2.0 API. The following options are available to adjust the cell style: Name Type Description fontSize number or string Text size fontWeight number or string Text weight fontStyle string Text style lineHeight string Text line height textAlign string Text alignment: 'left', 'right' or 'center' color string Text color backgroundColor string Cell background color padding number or string Cell padding borderWidth number Cell border width borderColor string Cell border color (with CSS fallback) Below I provide a widget script that identifies the first row in the pivot widget and utilizes transformPivot to replace the existing cell style with the new backgroundColor, color, and textAlign. Other cellStyle parameters can be set as in the example script. widget.transformPivot({}, (metadata, cell) => { if (metadata.rowIndex === 0) { cell.style = cell.style || {}; cell.style.backgroundColor = '#0057B7'; //sets cell background color cell.style.color = '#FFDD00'; //sets cell text color cell.style.textAlign = 'right'; //sets cell text align }; } ); To utilize the script, open the pivot widget in edit mode, use the three dots menu, and choose "Edit Script". Paste the script, save the changes, and refresh the dashboard. Enjoy the result! 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.Embedding Twitter Widgets In Sisense Dashboards
Introduction In this post we will demonstrate how to add a twitter feed within a dashboard. Business Case Viewing the latest tweets from key people in your industry may be an enhancing capability. Example Embedded Twitter Widget that is shown below can retrieves Twitter feeds from any public Twitter account. Steps STEP 1 - GENERATE A TWITTER USER WIDGET Twitter requires that you be logged in their site in order to generate an embedded widget. Configure your widget and generate its script at the Twitter New Widget Page STEP 2 - EMBED THE TWITTER SCRIPT IN YOUR HTML FILE Simply copy the generated script into the <body> section of your HTML on the server. // <![CDATA[ !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+"://platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); // ]]> Consider adding the "data-chrome" attribute to your element and customizing its appearance using "nofooter", "transparent", "noborders", "noheader" and "noscrollbar" keywords. For more information see Advanced Appearance Customization. STEP 3 - PLACE YOUR HTML FILE Move your HTML file to C:\Program Files\Sisense\PrismWeb. Make note of your HTML file's name, for example: "helloworld.html" STEP 4 - ADD THE "IFRAME WIDGET" PLUGIN Download plugin library and follow installation instructions for "iframe Widget" plugin STEP 5 - EMBED YOUR HTML FILE IN YOUR DASHBOARD Add a new iframe widget and insert URL in the following form: http://localhost:PORT/FILENAME Replacing PORT with your Sisense port (found in the address bar of Sisense Web) and FILENAME with your HTML file name noted in Step 3. For example: http://localhost:8081/helloworld.html Click Apply to finalize. See also Twitter User Widgets OverviewEmbedding 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.'); })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!Capture User Filters Then Write Back To MongoDB
Use case: Capturing what the end-user has selected to filter. Then writing back the filtered selection to the app mongodb for further analysis of customer dashboard interaction. Instructions: Add the code below as a dashboard script. Notes: Current script will write to the application mongodb leveraging the custom_data api. Update the ajax call to post data to another source. prism.on('dashboardloaded', function () { prism.activeDashboard.$$events.filterschanged.handlers.push(handleEventsChange); console.log(prism.activeDashboard)}) function handleEventsChange(sender, event) { var length = event.items.length; console.log(event) var username = prism.user.userName; event.items.forEach(curJAQL => { if (curJAQL.jaql.filter != null && curJAQL.jaql.filter.exclude != null) { var payload = { type: 'user-filter-exclude', user: username, dimension: curJAQL.jaql.dim, filterValues: curJAQL.jaql.filter.exclude.members } console.log(payload) $.ajax({ url: '/api/v1/custom_data', method: 'POST', contentType: "application/json", data: JSON.stringify(payload) }) } else if (curJAQL.jaql.filter.explicit===true){ var payload = { type:'user-filter-include', user: username, dimension: curJAQL.jaql.dim, filterValues: curJAQL.jaql.filter.members } console.log(payload) $.ajax({ url: '/api/v1/custom_data', method: 'POST', contentType: "application/json", data: JSON.stringify(payload) }) } });}
