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!Embedding a Google Doc or Sheet in Your Dashboard
The iFrame Widget Plugin is a powerful tool that enables you to embed any web page that you wish into your dashboard, using its URL. A great use of the iFrame widget can also be used to Embed Google Docs or Sheets. The quick access to the document or sheet can be used to store notes and comments derived from a dashboard's results. Furthermore, it can be utilized for reading or even updating the same spreadsheet being used as an Elasticube's data source (write back). Other users with whom the dashboard is shared will be able to view the content of the doc, only if its sharing settings allow it. You can decide if the user must be logged in to Google, and if so, who may view and who may edit. For more information about it, see this Google article. To embed a single fixed doc: 1. Get the required doc's URL. If you want the doc to be editable, use the shareable URL. If you want it to be presented in presentation only mode, use the Published URL. 2. Create a new iFrame widget. 3. In the Widget's edit mode, provide the document's URL to the iFrame. 4. Apply your changes. To Embed a dynamic, filter responsive selection of documents: 1. Create a table that would list all of the relevant documents' description and URL. In the below example, we are using the owner's Email as the description that we will filter by: 2. Import the table into an existing or to a new Elasticube. The table can exist as a stand-alone island, and enable filtering based on its inline dimensions, or it can be integrating into an existing model, to enable interaction with its additional filters. 3. Create a new iFrame widget based on the same cube. In the widget's URL panel, add the field that holds the documents' URLs 4. Apply changes and inspect your dashboard. You can now choose the required document by choosing the relevant Email: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} }) })Add Checkboxes To Pivot Table
reDownload: Pivot Checkboxes Introduction This article explains how to add filter checkboxes to a pivot table. Purpose/Benefits When showing a pivot table, you can make multiple selections by holding control and right clicking on several cells to set a filter. This plugin provides a way to add checkboxes to cells, which gives the user a quick way to make multiple selections. Steps The following steps will walk through the process of adding the plugin and creating a sample pivot table. 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 pivot table widget the checkbox option should show up in the context menu of the ROWS dimensions. STEP 2 - CREATE THE PIVOT TABLE On your dashboard, click the Create Widget button and select Advanced Configuration. Next, select the Pivot Table Chart from the chart types menu. Pick one or more dimensions under the ROWs category and click on the settings icon to configure the checkboxes. Make sure the pivot table is set to have Widget Affects Dashboard Filters and Enable Drill To Anywhere are DISABLED. Also make sure the dashboard filters are set to HIGHLIGHT. The widget settings should match the image below. References/Notes This plugin does not support drilldown and is only enabled for ROW dimensions in pivot tablesKeep Only 'Grand Totals' And Hide All Other Rows In A Pivot Table
Challenge: Rarely we would have to hide all the rows of pivot table and only show the Grand Total Rows. This will help us preserve the context provided by the dimension used in the rows of the pivot table but hide it to show the "Grand Total" only. Solution: In the pivot table, modify the number of rows per page = 2, so we can shrink the pivot table as small as possible. Turn on the "Grand Totals" on the top most dimension added in the rows of the pivot table. Use the script editor of the pivot table widget and copy - paste the following code. Save the script and refresh the page. widget.on("ready", function(w, args){ var rows=200; var i; for (i = 1; i < rows; i++) { if ( $("tbody tr:nth-child(1)", element).html() == $("tbody tr:last-child", element).html() ) { break; } $("tbody tr:nth-child(1)", element).remove(); } $("div.p-foot").remove(); }); You're all set! PS: Setting the rows per page = 2 will help your pivot table's performance!Extend The Functionality Of An Existing Widget
Introduction This post describes the process for creating a new plugin that extends the functionality of one of the existing Sisense widgets. There are several different aspects that you can modify, this document will focus on the most common use cases. Run code only for specific widgets When creating a plugin to extend the functionality of a widget, the type of widget will influence how you structure your code. Typically, its a good practice to create an array in your plugin that specifies what widget types are supports, along with a function that determines if the current widget is in that list. You can get the chart types from the javascript console, just open the widget editor for a given chart type and type prism.activeWidget.type into the javascript console, and it will provide the specific chart type. There are also sub-types available through prism.activeWidget.subtype. // Define allowed chart types var supportedChartTypes = ["pivot"]; // Function to determine if this chart type is supported function widgetIsSupported(type){ if (supportedChartTypes.indexOf(type) >= 0) { return true; } else { return false; } } Once you have this function defined, you can call it whenever the widget is initialized. This can be run directly from prism.run and if your widget type is supported, then it will attach your custom function to an event handler on the widget. // Registering dashboard/ widget creation in order to perform drilling prism.on("dashboardloaded", function (e, args) { args.dashboard.on("widgetinitialized", onWidgetInitialized); }); // register widget events upon initialization function onWidgetInitialized(dashboard, args) { // Hooking to ready/destroyed events args.widget.on("destroyed", onWidgetDestroyed); var shouldInit = widgetIsSupported(args.widget.type); if (shouldInit) { args.widget.on("eventName", myCustomFunction); } } // unregistering widget events function onWidgetDestroyed(widget, args) { widget.off("destroyed", onWidgetDestroyed); } Add menu options to the Widget Editor In order to add custom formatting options to a chart, a common way is to add new menu items to the widget settings menu or a value/dimension menu. To do this you need to leverage the prism.on events, which can be found here. The function to run as the event handler should check the setting.name attribute in order to determine if the menu opened is the menu you are looking for. A good practice to to create a function dedicated to checking the menu type, that just returns a boolean. // Function to determine if the checkbox option should be added function canEnabledOption(e,args,headerMenuCaption){ // Assume true by default var result = true; try { // Has the menu been added already? $.each(args.settings.items, function(){ if (this.caption == headerMenuCaption) { result = false; return result; } }) // Only show this in the widget editor var widgetEditorOpen = (prism.$ngscope.appstate == 'widget'); if (!widgetEditorOpen) { result = false; return result; } // find the widget var widget = e.currentScope.widget; if(typeof widget === "undefined"){ result = false; return result; } // Widget must be an allowed chart if(!widgetIsSupported(widget.type)){ result = false; return result; } // Make sure the user clicked on a widget's settings menu if (args.settings.name != "widget-metadataitem") { result = false; return result; } // For pivot charts, make sure the settings menu is for a value if (e.currentScope.widgetType == "pivot") { // Only capture events on the ROWS panel if (args.settings.item.$$panel.name != "rows") { result = false; return false; } } } catch(e) { result = false; } // Return result return result; } If you want to add menu items to measures/dimensions in the widget editor, you can adjust the above code to look for args.settings.name == "widget-metadataitem" and use the table below to check for specific datatypes. Menu Item Properties Available for Specific Datatypes Once you have this, you can just check the function (maybe in multiple places) and then decide what code to run. prism.on("beforemenu", function (e, args) { // Define the menu label var headerMenuCaption = "My Caption Label"; // Can we show the options? var addMenuItems = canEnabledOption(e,args,headerMenuCaption); // Add some extra menu items if (addMenuItems) { ...some code here... } }) Use Cases Here we will break down the specific types of widgets, and what the process looks like for customizing each. INDICATORS The easiest way to customize indicators is by manipulating the HTML after it's been rendered. It's pretty straightforward to do this, using jQuery (which is already loaded with Sisense). There is a helpful forum post that describes how to find specific pieces of an indicator. The script in the forum post runs for all indicators on a dashboard, but it could also be run to customize the formatting of a specific indicator. The code below shows how to find a specific indicator's text fields, title, subtitle, and numerical fields. Standard CSS is used to customize the look and feel of each of these pieces. widget.on('ready', function(sender, ev){ // General Class for all the parts of the Indicator var indicator = $('.indicator_section span',element); indicator.css('font-family','Times New Roman,Georgia,Serif'); // change font family indicator.css('font-size','50px'); // change font size indicator.css('font-style','italic'); // change font style indicator.css('white-space','normal'); // Wrap Text // primary title (id) var title = $('#title_span',element); title.css('color','blue'); title.css('font-style','Bold'); // change font style //secondary title (id) var subTitle = $('#secondary_title_span',element); subTitle.css('font-size','25px'); // change font size subTitle.css('color','green'); subTitle.css('font-style','Bold'); // change font style //catches both of the 'number' classes (primary+secondary) var numberSpan = $('.number_span,.secondary_span',element); numberSpan.css('color','red'); //id of the secondary title var subTitleSpan = $('#secondary_title_span',element); subTitleSpan.css('color','Black') //ID of the secondary number - overrides the .secondary_span css class var subSpan = $('#secondary_span',element); subSpan.css('color','#9E08C3') //a kind of purple }) If you are interested in customizing gauges, then hook into the processresult event of the widget. See the code snippet below that gets the gauge object from a widget's event object. This object contains the default color, along with the conditional colors that are set for the gauge. widget.on('processresult', function(sender,event){ var gauge = event.result.gauge; }) Relevant plugins/Javascript: Accordion Plugin Change Indicator Style Add Conditional Icons to Indicator HIGHCHARTS-BASED WIDGETS (COLUMN, BAR, AREA, PIE, POLAR, & SCATTER) Several of the charts used in Sisense (Column, Bar, Area, Pie, Polar, and Scatter widgets) leverage Highcharts behind the scenes. This means that any of these chart types can be easily manipulated using the Highcharts API. Catch the processresult or render event and look for the result variable (for processresult) or queryResult variable (for render). This will be the equivalent of $("#container").highcharts( { } ) in the Highcharts API. Once you have this object, you can customize it using any of Highchart's available options. Check out the links below that ouline common uses cases for modifying the widget using the Highcharts API. function customizeHighchartOnProcessResult(sender,event) { var highchart = event.result; } function customizeHighchartOnRender(w) { var highchart = w.queryResult; } Relevant plugins/Javascript: Filter Dashboard by underlying members of an aggregate Add Regression Line to a Chart Dynamic Buckets Center the Zero Intersect on Bar Charts Remove 0 Labels from Column/Bar Chart Show Value Labels as the Variance PIVOT WIDGET The Pivot widget works a bit differently than all the other widgets in Sisense. When you create a query from this widget, the result is a formatted HTML table. You can access this on processresult, using event.result.table. This can be manipulated just like any other HTML element, and is easily done using jQuery. One quirk of jQuery is that if you ask for the HTML of an element, it will give you the HTML of only the children of that element. In order to get the entire HTML, create an empty DIV element named myTable and append the HTML table as a child. Once you have this, you can manipulate it as needed and get the table back by using myTable.html(). widget.on('processresult', function(sender,event){ // Get the result table var myTable = $('<div>').append($(event.result.table)); // Do something to the table // Write the table back to the widget event.result.table = myTable.html(); }) One important piece to keep in mind is how to find specific columns within this table. If you look at the result.fields object, it lists out the dimensions and values that exist in the pivot table along with an index. When searching through the HTML table look for the fidx attribute which will match with the indexes. Relevant plugins/Javascript: Add Checkboxes to Pivot Copy Data from a Pivot Table TABLE WIDGET The table widget is based off the DataTables framework. This table is not pre-formatted like the Pivot table, instead it is sent back as an array of rows. Each row contains all the columns in the row, with a data field and text field. You can manipulate the text field with HTML and the browser will render it for you. The example below is the best reference for seeing this in action. Relevant plugins/Javascript: Embed Images to Pivot/Table Widget OTHER WIDGETS There are a few more widget types (treemap, calendar, sunburst) which use the D3 library. Each one of these will be pretty different, as D3.js is more of a framework for creating shapes. The rendering process here is handled more behind the scenes, instead of through configuration objects, so the best way to manipulate them is by hooking onto the ready event and manipulating the HTML after it's been written to the page. Alternatively, you can hook into the render event and manipulate the data at w.queryResult.$$rows The scatter map widget uses a the Leaflet framework, which uses Mapbox as a mapping engine. Again, the rendering for this chart type is done internally, so the best way to manipulate it is either by changing the data on render at w.queryResult.$$rows or by waiting for it to complete and hook into the ready function to manipulate the HTML on the page. Alternatively, the bottom link shows how to save the map object that gets created and allows for manipulation after it's been loaded. Relevant plugins/Javascript: Replace N/A's with 0's in Pivot Table Center/Zoom the Scatter MapD3 Force Directed Network Diagram
This article explains how to create a force directed network diagram using Sisense. This is helpful for showing hierarchies or relationship within your data. Steps The following steps will walk through the process of adding the new chart type and creating a sample funnel chart. Step 1 - Add the Plugin Download the attachment and unzip the contents into your C:\Program Files\Sisense\PrismWeb\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. Step 2 - Create the Chart On your dashboard, create a new widget and select Force Directed as the type. In order to work with this visualization, you need to specify at leas a Node ID and Linked Node ID. This is basically how the relationship is defined within the data. If you think about this in terms of an employee hierarchy, the Node ID field would usually be an employee ID and the Linked Node ID would be the employee's manager ID. There are also several optional fields you can add in Node Labels - The value label for each node (such as the employee's name) Color - This field determines how to color each node. You can use a dimension here (such as department name) or a metric (such as Sales per Employee). When using an Icon to display each node, this field is ignored Node Size - This field determines the size of each node. The higher the value, the larger the node is displayed. When not specified, all nodes are displayed with the same size Node Icon URL - Use this field to specify an image to show for each node. This should be a URL to where the images are hosted. If a node is missing an icon url, it will show a circle instead. If this field is left blank, each node will show up as a circle. Step 3 - Format the chart This visualization has several formatting options, see below for a description of each. Node Circle Size - A slider control that allows the designer to determine the range used for sizing each node Size Scale Method - Determines which scaling algorithm to use for determining the size of each node Selectable as Filters - Controls whether or not each node can set a dashboard filter when clicked on Value Labels - Controls whether or not to display labels for each node, and where to place the label Zoom from Mouse Scroll - When enabled, a user can adjust the size of the nodes by using their mouse scroll wheel Animation - Determines whether or not the chart loads with animation Gravity - A slider control for adjusting the gravity of the force layout Friction - A slider control for adjusting the friction of the force layout Charge - A slider control for adjusting the charge of the force layout References/Notes This plugin was based of a D3 Force Directed visualization. For more information on D3 in general, please see this link. For some generic examples of working with D3, please see this link and this link. The bottom 3 sliders in the Design Panel control variables for gravity, friction, & charge. These are D3 specific variables that work together to determine how the chart calculates a layout. For more information on these variables, please see the D3 Documentation or this explanation. See the attached files for the plugin and also a sample Elasticube & Dashboard Download: Plugin-Force Directed Network.zip Samples.zipReset To Default Filters When Loading Dashboard
Purpose/Benefit Sometimes it is convenient to always reset the filters to a default state when opening a dashboard. This post describes how to achieve this with a little bit of JavaScript. When users will often be resetting filters to a default state, it is also useful to have a more prominent button to do so. This post describes how to do this as well (optional). Prerequisites None Steps STEP 1 - OPEN A DASHBOARD & OPEN THE SCRIPT EDITOR Open the options menu in the upper right corner of the dashboard and choose the Edit Script option (as shown below). Note: It's not always desirable to do this for all dashboards, so these steps must be taken for each dashboard that you want to have this behavior. STEP 2 - PASTE IN THE FOLLOWING CODE SNIPPET AND CLICK SAVE window.resetFilters = function(d) { //Function to reset the dashboard filters to the default filters. Takes parameter 'd' which is a reference to the dashboard d.filters.clear(); //Clears current filters d.defaultFilters.forEach(function(filter, index){ //Loop through each default filter and apply to current dashboard filters if(index != d.defaultFilters.length - 1){ //Does not refresh filter if it is not the last filter d.filters.update(filter,{ save:true, refresh:false, unionIfSameDimensionAndSameType:true }); } else{//Only refresh dashboard on the last filter d.filters.update(filter,{ save:true, refresh:true, unionIfSameDimensionAndSameType:true }); } }) } dashboard.on('initialized', function(d){ //Resets filters to default when dashboard is first loaded (or refreshed) resetFilters(prism.activeDashboard); //Resets filters }) (Optional) Include this code snippet after the first to create a larger copy of the reset filters button on the toolbar. This may be convenient for some users. dashboard.on('initialized', function(sender, ev){ //Adds filter reset button to the toolbar toolbar = $('.actions-box'); //reference to top toolbar resetButtonHTML = "<div id=\"toolbarResetButton\" class=\"btn-immutable btn-action\" onclick=\"resetFilters(prism.activeDashboard)\" data-ng-style=\"{height: sizing.toolbar.height, \'border-left-width\': sizing.atoms.midHgap + \'px\'}\" title=\"Restore my default filters\" style=\"height: 50px; border-left-width: 30px;\"><div style=\"background-image: url(../client/resources/ux-controls/images/refresh_48.png); height: 50px; width: 50px; background-repeat: no-repeat; background-position: center\"></div>"" //button HTML with reference to resetFilters() function if($('div#toolbarResetButton').length == 0){ //Check to see if filter reset button already exists in DOM toolbar.append(resetButtonHTML); //Adds filter reset button to DOM } }) STEP 3 - ENJOY! Your dashboard filters will now reset to their default state when a user opens the dashboard!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:
