Dynamically Updating Widget Titles Based on Filter Selections in Sisense (Linux)
Dynamically Updating Widget Titles Based on Filter Selections in Sisense (Linux) Introduction This article guides how to dynamically change the title of a Sisense widget based on the filter selection. While this is an advanced solution and not natively supported by Sisense functionality, a custom script can be used to achieve this effect. Please proceed with caution, as this is a custom implementation. [ALT Text: A dashboard display titled "Europe, USA (by Region)" showing a gauge indicating an "Actual Margin" of 0.22. The gauge ranges from -0.25 to 0.25. A sidebar lists regions: Europe, N/A, and USA, with checkboxes next to each.] Step-by-Step Guide Navigate to the desired dashboard in Sisense and select the widget you want to change the title dynamically. Open the widget's settings and access the script editor under the 3 dots menu > Edit Script. Copy and paste the following code into the widget's script editor: widget.on("render", function () { let title = "by Region"; // Set a default title // Find the specific filter by its column name let filterUsed = dashboard.filters.$$items.find( (filter) => filter.jaql.column === "Region" ); // Determine the filter text or set default let filterText = filterUsed && filterUsed.jaql.filter.members?.length ? filterUsed.jaql.filter.members.join(", ") : "All Regions"; // Flag to prevent multiple updates let isModified = false; if (!isModified) { widget.title = filterText + ' ' + title; isModified = true; } }); Adjust the filter.jaql.column value instead of "Region" in the script to match the actual filter you intend to use. Update the default title instead of "by Region". Modify the "All Regions" value to be displayed for the “Include All” filter. Save the changes in the script editor. Apply different filter selections on your dashboard to verify that the widget title updates according to the chosen filters. Troubleshooting and Limitations: Ensure that the filter column specified in the script matches an existing filter on the dashboard. The script works only for filters of include type. For excluding filters the additional logic should be implemented. Conclusion By following the steps outlined above, you can set up your Sisense widget titles to dynamically update based on filter selections. While this approach offers flexibility, it involves a custom script and requires careful handling, as modifications made this way are outside Sisense's standard support. 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.Week over week analysis with custom fiscal year: Use case of a fuel and convenience retail operator
Introduction Week-over-week (WoW) analysis is a key part of performance tracking for fast-moving, high-traffic businesses such as convenience stores, gas stations, and car washes. For these organizations, aligning the fiscal calendar with operational cycles rather than the standard calendar year makes reporting more meaningful. In this use case, the fiscal year begins on the closest Sunday to January 1st, ensuring each year starts with a full week. This structure simplifies weekly reporting and keeps week-to-week comparisons consistent across years, which is important for tracking trends like fuel sales, store traffic, and service volumes. While nonstandard, this setup is commonly used in practice. What the Solution Does For standard, fixed calendar or fiscal years, week-over-week analysis can be achieved using the “First Month of Fiscal Calendar” and “First Day of Week” settings, along with the PASTYEAR function. However, for dynamic fiscal years that begin on a weekday closest to January 1st, these features don’t provide a usable solution, since the start date can fall in the previous or following calendar year. The solution uses the https://www.sisense.com/marketplace/add-on/filtered-measure/ and a custom dashboard script to handle the custom fiscal year. Two year filters are added to the dashboard: one represents the selected fiscal year (user-selectable), and the other represents the prior year for comparison (locked and optionally hidden), which is automatically set with a dashboard script. The Filtered Measure plugin applies the selected-year filter to the measure for the chosen period, while the prior-year filter applies to the measure for the corresponding period in the previous year. This approach ensures that week-over-week calculations respect the custom fiscal calendar, providing accurate comparisons across equivalent weeks. Note: In this particular implementation, the fiscal years and week numbers are pre-calculated in the database and stored as numeric columns. To create a Date dimension table in your Elasticube with fiscal years starting on the first Sunday closest to January 1st, refer to the SQL example below. Why It’s Useful This solution addresses the native functional limitation by respecting the custom fiscal calendar, ensuring weekly trends are comparable across years. As a result, teams can reliably track key metrics, such as fuel sales, store traffic, and service volumes, on a true week-by-week basis, supporting better operational planning and more informed decision-making. Attachments WeekoverWeekAnalysiswithCustomFiscalYear.dash.txt (dashboard) Sample ECommerce - Custom Fiscal Year.smodel.txt (elasticube) JS Script - Automatic Update for Second Year Filter.txt (dashboard script) SQL Query - Dim Date with Custom Fiscal Year.txt (custom table SQL query) For the script to hide the second filter, refer to this BINextLevel article: https://www.binextlevel.com/post/hide-dashboard-filters. Note: remove the .txt extension before importing the dashboard (.dash) and the Elasticube (.smodel) files.
Pivot2: "Add Sparklines In Pivots" Re-Implemented
Introduction This plugin was created to allow dashboard designers to create sparklines within a pivot table. This plugin currently supports line, area, and column charts as the sparklines. Steps The following steps will walk through the process of adding the new plugin. STEP 1 - ADD THE PLUGIN Download the latest version of the plugin from this link and extract the enclosed folder into the plugins folder: /opt/sisense/storage/plugins/ STEP 2 - CREATE THE PIVOT FORMULA The main challenge with sparklines is that they require more data than just what's displayed in the pivot table. In order to specify the extra dimensions needed, you can create a multi-pass aggregation function that this plugin will use to generate a new query. The screenshots below show how to create a formula that includes the extra dimension needed to get supporting data used by the sparkline. Please note, you can use either a measure or starred formula for the last item in the sparkline formula. STEP 3 - DEFINE YOUR SPARKLINE Click on the settings menu of your formula, and then the sparkline option. You should see a menu listing the available chart types. If you change your formula, you must recheck the sparkline box. Also, you can set the color of the sparkline, similarly to how you would for any other value in the pivot table.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.Connection Tool - Programmatically Remove Unused Datasource Connections, and List All Connections
Managing connections within your Sisense environment can become complex over time, if there are a large number of connections, and connections are often added, and replace earlier datasource connections. In some scenarios unused connections can accumulate, potentially cluttering the connection manager UI with no longer relevant connections. Although unused connections typically represent minimal direct security risk, it's considered best practice to maintain a clean, organized list of connections, and in some scenarios it can be desired to remove all unused connections. Sisense prevents the deletion of connections actively used in datasources, safeguarding your dashboards and datasources from disruptions. However, inactive or "orphaned" connections remain after datasources are deleted or a connection is replaced, potentially contributing to unnecessary UI complexity in the connection manager UI. Connections can be of any type Sisense supports, common types include various SQL connections, Excel files, and CSV files, as well as many data providers, such as Big Panda. This tool can also be used to list all connections, with no automatic deletion of unused connections.
Using BloX to enable or disable Pivot rows in Sisense dashboards (Linux)
Using BloX to enable or disable pivot rows in Sisense dashboards (Linux) Step-by-Step Guide To enable or disable a row in a Pivot widget using BloX, follow these steps: 1. Create a Custom Action in BloX: Open the BloX widget in edit mode. Click on the three-dot icon. Select "Create Action" and name it manageRows. 2. Insert Action Code: Copy and paste the following code snippet into the action creation area: let widgets = payload.widget.dashboard.widgets.$$widgets; let pivotWidgets = widgets.filter(widget => widget.type === 'pivot2'); pivotWidgets.forEach(widget => { let panel = widget.metadata.panels.find(p => p.name === 'rows'); if (!panel) return; // Replace [Commerce.Condition] with the dimension of the row to be managed. let item = panel.items.find(i => i.jaql ?.dim === '[Commerce.Condition]'); if (!item) return; item.disabled = !item.disabled; widget.changesMade('pivotchanged', ['metadata']); widget.refresh(); }); 3. Configure the Action Parameters: Add the following action parameters in the second step: { "type": "manageRows", "title": "title" } 4. Apply the Action: Click "Create" and then "Apply" to save your custom action. 5. Import the Dashboard: Import the provided dashboard, which is based on the sample cube "Sample ECommerce" and contains a BloX widget with the script that utilizes the created action, and also two Pivot widgets for testing purposes. 6. Test the Button: Use the "ChangePivot" button within the dashboard to toggle the visibility of the "Condition" row. Conclusion: By following this guide, you can create interactive dashboards that allow users to enable or disable pivot rows with a simple button click. This functionality enhances the user experience by providing dynamic interaction with the data. References/Related Content Sisense BloX Custom Actions Guide Creating Custom Actions in Sisense 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.Customizing the Sisense User Interface with Interactive Buttons and Icons
Sisense plugins and scripts enable extensive customization of the Sisense user interface, allowing developers to add interactive elements such as buttons and icons to enhance functionality and user experience. A common use case of plugins involves adding clickable icons or buttons that trigger specific plugin features or open custom UI elements. This article outlines the process for adding these interactive elements using a practical example.Update and add new Highcharts modules for use in Sisense plugins
Update and add new Highcharts modules for use in Sisense plugins The JavaScript library framework Highcharts is natively included in Sisense and is utilized in many native Sisense widgets as well as in numerous Sisense plugins. Although Sisense typically does not alter the Sisense Highcharts library version with every release, the versions of Highcharts included in Sisense may change when upgrading to a new major version release. Highcharts can load additional chart types and other types of functionality via JS module files that contain code-adding features such as additional chart types, which can be used within plugins along with additional code to create additional widget types. If a plugin utilizes a Highcharts module, you can source the module directly in the "plugin.json" file's source parameter, as shown in this example: "source": [ "HighchartModule.js", ], To determine the current Highcharts version being used in your Sisense version, you can use the "Highcharts" command in the web console while viewing any page on your Sisense server. After identifying the current Highcharts version, you can find the corresponding module hosted on the Highcharts code hosting website using the following URL format: https://code.highcharts.com/${Highcharts_Version}/modules/${module_name}.js For example: https://code.highcharts.com/6.0.4/modules/heatmap.js You can save this module and upload it to the plugin folder or replace the older module JS file simply by copying and pasting the code directly. Be sure to update the "plugin.json" file to point to the new module file if the file name has changed or if this is the first time the module is included. Simply sourcing the module file in the "plugin.json" file is sufficient to load the module into Highcharts; no further code is required to load the module.Loading Amchart5 and Other External Libraries via Script Tags in Plugins
This article explains how to load external libraries, such as Amchart5, into Sisense plugins by dynamically adding script tags to the page header to load the library. This method can avoid potential issues associated with other loading techniques but also offers flexibility such as using an external CDN to reduce plugin size and file count. Previous articles have discussed how to load external libraries and modules for Sisense plugins via adding the file to the plugin folder, and adding the file to the "source" parameter array in the plugin.json.
