Ensuring Accurate PDF Export Headers When Programmatically Modifying Dashboard Filters in Sisense
When working with Sisense dashboards, programmatically modifying filters upon dashboard load via dashboard scripts for a specific user or circumstance is possible. However, an issue can sometimes occur when exporting these dashboards to PDF immediately after such modifications: the filter header displayed at the top of the exported PDF displaying the current dashboard filter state may not accurately reflect the current state of the dashboard filters. This article explains the cause of this issue and presents a solution to ensure that PDF exports display the correct filter information in the header.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.Limiting Date Filters to Datasource Date Range
Limiting Date Filters to Datasource Date Range By default, Sisense allows users to select any date in a dashboard date dimension filter, regardless of whether data exists for that date in the current dashboard datasource. The native Sisense date filter selection UI highlights the earliest and latest dates available via shading and color indications, but if a user selects a date outside this range, it is accepted as the desired filter from the user. While this behavior is generally the preferred behavior, there are cases where a dashboard creator might prefer the date filter to visually and programmatically reflect only the dates for which data exists in the datasource. In scenarios where the exact number of days in the filter range the data being viewed from is vital, such as when analyzing total date data coverage, performing per-day calculations, or simply ensuring that the visual filter UI accurately represents the actual data period being shown, the date filter should ideally be modified to only display date ranges that have corresponding data. This ensures that the filter not only serves as a control mechanism but also as an accurate visual cue of the available data's temporal range. The dashboard script presented below adds this custom functionality. It works by executing two custom JAQL queries (More documentation on custom script JAQL queries is available here) to fetch the sequentially earliest and latest dates from the datasource. These dates are then used as the bounds to restrict the filter. The dimensions used to fetch the date range and the dimension used to find the matching dashboard filter can be separate or identical dimensions, as they are separated in two variables. The script supports both common date filter formats, traditional "from-to" date ranges and filters that use a list of date members. Additionally, the approach can be extended to accommodate other filter types or JavaScript based actions beyond or not including filter modification when a filter's value falls outside the desired range. For more detail and an addition example see the article Redirect Users to Different Dashboards Based on Dashboard Filters. When a user selects a date that falls outside the available range of the datasource, the code below automatically adjusts the filter. It modifies the selection so that the filter is constrained to the earliest available date if the selected date is too early, or to the latest available date if it exceeds the maximum. If both boundaries are affected, the filter is adjusted on both ends to ensure that only dates with corresponding data are displayed. This solution provides a robust method to visually align your dashboard’s date filter with the actual data available, ensuring that users have a clear and accurate reference of the data period being analyzed. The full code implementing this functionality is provided below. (function () { const enableLogging = true; // Primary date dimension for the filter (change as needed) const dateDim = "[MainTable.Revenue Date (Calendar)]"; // JAQL dimension used to fetch the earliest/latest dates (change as needed) const jaqlDateDim = "[MainTable.Billing Date (Calendar)]"; /** * Simple logging function to enable or disable console logging. */ function log(...msgs) { if (enableLogging) { console.log(...msgs); } } /** * Formats a Date object as "YYYY-MM-DD". */ function formatDate(d) { const yyyy = d.getFullYear(); const mm = String(d.getMonth() + 1).padStart(2, "0"); const dd = String(d.getDate()).padStart(2, "0"); return `${yyyy}-${mm}-${dd}`; } /** * Finds the primary date filter (single-level) based on the defined dateDim. * * Note: * - If run within a dashboard script, the variable "dashboard" is already defined. * - If within a plugin, use prism.activeDashboard. * - If within a widget script, use widget.dashboard. */ function findSingleLevelDateFilter() { if (!dashboard.filters || !dashboard.filters.$$items) return null; return dashboard.filters.$$items.find(filterObj => !filterObj.isCascading && filterObj.jaql && filterObj.jaql.dim === dateDim ); } /** * Constructs a JAQL query to fetch a date from the datasource. * @param {string} direction - "asc" to fetch the earliest date, "desc" to fetch the latest. */ function buildDateQuery(direction) { return { datasource: dashboard.datasource, metadata: [ { jaql: { dim: jaqlDateDim, datatype: "datetime", level: "days", sort: direction } } ], count: 1 }; } /** * Executes an asynchronous HTTP request for the provided JAQL query. */ function runHTTP(jaql) { const $internalHttp = prism.$injector.has("base.factories.internalHttp") ? prism.$injector.get("base.factories.internalHttp") : null; const ajaxConfig = { url: `/api/datasources/${encodeURIComponent(jaql.datasource.title)}/jaql`, method: "POST", data: JSON.stringify(jaql), contentType: "application/json", dataType: "json", async: true, xhrFields: { withCredentials: true } }; return $internalHttp ? $internalHttp(ajaxConfig, false) : $.ajax(ajaxConfig); } /** * Adjusts the date filter so that its values fall within the datasource range. * For multi-valued filters (using a "members" array), out-of-range dates are removed. * For single-valued filters with "from" and "to" fields, each is updated if outside the available range. * * @param {Object} filterObj - The primary date filter object. * @param {Date} earliestDate - The earliest available date. * @param {Date} latestDate - The latest available date. */ function adjustDateFilterIfOutOfRange(filterObj, earliestDate, latestDate) { if (!filterObj || !filterObj.jaql || !filterObj.jaql.filter) return; const jaqlFilter = filterObj.jaql.filter; let adjustmentMade = false; // Adjust multi-valued filter (members). if (Array.isArray(jaqlFilter.members) && jaqlFilter.members.length > 0) { const originalCount = jaqlFilter.members.length; const validDates = jaqlFilter.members.filter(dateStr => { const d = new Date(dateStr); return !isNaN(d.valueOf()) && (!earliestDate || d >= earliestDate) && (!latestDate || d <= latestDate); }); if (validDates.length < originalCount) { jaqlFilter.members = validDates; adjustmentMade = true; log("Adjusted members filter to valid dates:", validDates); } } // Adjust "from" date if necessary. if (typeof jaqlFilter.from === "string") { const fromDate = new Date(jaqlFilter.from); if (earliestDate && fromDate < earliestDate) { jaqlFilter.from = formatDate(earliestDate); adjustmentMade = true; log("Adjusted 'from' date to:", jaqlFilter.from); } } // Adjust "to" date if necessary. if (typeof jaqlFilter.to === "string") { const toDate = new Date(jaqlFilter.to); if (latestDate && toDate > latestDate) { jaqlFilter.to = formatDate(latestDate); adjustmentMade = true; log("Adjusted 'to' date to:", jaqlFilter.to); } } if (adjustmentMade) { log("Date filter adjusted for dimension:", dateDim); } } /** * Retrieves the earliest and latest dates from the datasource, * then adjusts the primary date filter so that its values fall within that range. */ function updateDateFilter() { const queryEarliest = buildDateQuery("asc"); const queryLatest = buildDateQuery("desc"); Promise.all([runHTTP(queryEarliest), runHTTP(queryLatest)]) .then(([responseEarliest, responseLatest]) => { let earliestDate = null; let latestDate = null; if (responseEarliest && responseEarliest.data && responseEarliest.data.values?.length) { const eStr = responseEarliest.data.values[0][0].data; const dt = new Date(eStr); if (!isNaN(dt.valueOf())) { earliestDate = dt; log("Earliest date from datasource:", formatDate(dt)); } } if (responseLatest && responseLatest.data && responseLatest.data.values?.length) { const lStr = responseLatest.data.values[0][0].data; const dt = new Date(lStr); if (!isNaN(dt.valueOf())) { latestDate = dt; log("Latest date from datasource:", formatDate(dt)); } } const filterObj = findSingleLevelDateFilter(); if (!filterObj) { log("No primary date filter found; cannot adjust date filter."); return; } adjustDateFilterIfOutOfRange(filterObj, earliestDate, latestDate); }) .catch(err => { log("Error fetching datasource date range:", err); }); } // Call updateDateFilter() when filters change. dashboard.on('filterschanged', function () { updateDateFilter(); }); // Call updateDateFilter() on dashboard load dashboard.on('initialized', function () { updateDateFilter(); }); })();Passing Filters via URL Parameters for Dashboards with Separate Datasources
Sisense includes a native included feature and format for passing URL filters via URL parameters, as documented here. By default, this functionality copies filters in full, including the datasource parameter of the filter, and includes every filter automatically. It results in very long URL's, and includes many parameters that are not always required, as the full filter object is included. Previous Knowledge Base articles articles have discussed how similar behavior can be recreated customized via scripting for more flexible usage. However, those approaches applied only to member-type filters, excluding other filter types and multi-level dependent filters. The code shared below demonstrates a more flexible filter modification via URL modification approach. It includes both creating URL parameters and reading URL parameters for filter modification, whether this code is in a script or plugin. This method applies to all filter types and can be used to transfer filters between dashboards using different datasources. This code works in both dashboard and widget scripts as well as plugins. If your datasources use different dimension names, this code can be adopted to map and match the aligned dimensions.How to Automatically Hide Accordion Widgets When Clicking on Tabber (Linux)
How to Automatically Hide Accordion Widgets When Clicking Outside the Accordion Window (Linux) Introduction This article addresses the specific scenario where accordion widgets remain visible even after interacting with other interface elements, such as tabber widgets, in a dashboard environment. Step-by-Step Guide Integrate a widget Script in the widget edit mode > click the 3 dots menu > Edit Script: widget.on("ready", function(w, e) { var tabs = document.querySelectorAll(".listDefaultCSS > span.listItemDefaultCSS, .listItemContainer > span.listItemDefaultCSS, .listItemContainer.selected > span.listItemDefaultCSS"); tabs.forEach(function(tab) { tab.addEventListener("click", function(e) { var accordions = document.querySelectorAll(".bk-accordion"); var arrows = document.querySelectorAll(".bk-arrow"); accordions.forEach(function(accrd) { if (accrd.style.opacity == "1") { accrd.style.opacity = "0"; accrd.style.padding = "0"; accrd.style.height = "0"; } }); arrows.forEach(function(arr) { if (arr.style.opacity == "1") { arr.style.opacity = "0"; } }); }); }); }); This script will detect clicks on the tabber widgets and hide any visible accordion windows by setting their opacity, padding, and height to zero. After adding the script, navigate through your dashboard to ensure that accordion widgets hide as expected when clicking on a tabber widget or other areas. [ALT Text: A data visualization dashboard displaying a bar graph titled "Profit per Product." The graph shows profit values for different products with values reaching up to 10,000. The total EU profit is indicated as 23,36K, with a profit rate of 49.81%. Tabs for additional analysis are visible at the top.] Conclusion By incorporating the provided dashboard script, users can enhance their user interface experience by ensuring accordion widgets automatically hide when interacting with other dashboard elements. Despite the current design limitations regarding the accordion's position, the script offers a viable solution to improve dashboard interactions. 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.Redirect users to different dashboards based on dashboard filters
This article discusses and shares the full code of a dashboard script that redirects users to a different dashboard ID based on the user's filter selections or initial loaded filter state. In the particular example shared in this article, the script checks whether the selected date filter (either from a members filter or a from/to filter range) includes an earlier date than the earliest date in the current dashboard's datasource. If this is the case, the script redirects the user to a specified alternate dashboard, preserving any additional URL segments and query parameters in the URL. Any other type of filter state can also be used to determine on when the script should redirect, including non-date filters using similar scripts.Pivot and Table Widget Scripting for Replacing Values with Colored Arrows
Sisense table and pivot widgets offer extensive customization options for data presentation, allowing developers to tailor widget content to specific needs, as discussed in detail in previous articles. While the Pivot 2.0 API includes a built-in transformPivot function for data transformation, Table widgets typically require direct DOM manipulation. This article provides a step-by-step guide for replacing numeric (or other) values in both widget types with arrow characters via scripting. It also demonstrates how to assign colors or apply conditional logic to these transformations. Examples include the use of Unicode arrow characters, though any other characters or combinations of characters can of course be utilized.Dynamically adjusting column precision in Sisense widgets
This article details a script designed to dynamically set the number of decimal places (precision) for specific columns in a Sisense widget. The precision is determined based on the first value from a dedicated "precision" dimension in the widget. This script is ideal for use cases where precision needs to be dynamically adjusted based on filters, whether the filters are set by the user, default filters, or data security. The precision dimension will of course respect all filters and datasecurity, which determines the precision value returned. A common example of this functionality, is if a single Sisense server serves multiple customers, which share a common datasource, but have datasecurity rules to ensure only that customers data is shown. If each customer has their own precision value, the precision value returned will vary as expected between customer, allowing a single widget to serve multiple customers, and vary data as needed. The precision panel does not have to be enabled and visible in the widget, the precision panel item can be disabled and the script will use a custom JAQL request to retrieve the precision value.
Dynamic filtering and embedding tooltips in Sisense dashboards
Dynamic filtering and embedding tooltips in Sisense dashboards Overview This solution enhances Sisense dashboards by introducing dynamic filtering and embedding visual insights directly within tooltips. It allows users to quickly explore related data without navigating to a different screen, making data analysis faster and more intuitive. Supported by various types of charts: Line chart, Column chart, Bar chart, Pie chart. ALT text: A bar chart displaying data for "Total Cost of Admission" in a healthcare context. The chart compares costs for "Cardiology" and "Emergency Room" services across the years 2011, 2012, and 2013. The year 2011 has a bar for Cardiology at approximately 2 million and a higher bar for Emergency Room around 3.5 million. 2012 is not depicted, and 2013 shows substantial increases for both categories, with Cardiology around 3 million and Emergency Room approximately 4 million. A tooltip highlights specific values when hovering over the chart. How to Set It Up Prepare the Target Dashboard Create a new dashboard with a single widget. This widget will display the filtered data relevant to the user’s interaction. Ensure this dashboard focuses on specific details you want to highlight (e.g., sales by region or category, date). Add the Script to the Source Dashboard On the dashboard where you want to enable this functionality: Go into Edit Mode for the widget where tooltips will show embedded insights. Click the three dots on the widget, then select Edit Script. Replace the script logic with the provided code and update the following: Widget ID: The ID of the widget you’re editing. Dashboard ID: The ID of the target dashboard (created in Step 1). Number of Filters: Define how many filters you want to pass to the target dashboard. Adjust the Filters List Filters are passed to the target dashboard in the order they appear in the source dashboard’s filter list. If specific filters are critical (e.g., date or region), move them to the beginning of the filters list in the source dashboard. Test the Configuration Interact with the widget in the source dashboard to ensure the tooltip displays the filtered target dashboard correctly and includes the desired filters. How It Works User Interaction: When a user hovers over a data point, the script identifies the category or value of interest (e.g., region, date). Dynamic Filters: The selected category is passed as a filter to the target dashboard and other relevant filters from the source dashboard. Embedded View: The tooltip contains a mini, interactive version of the target widget, which provides detailed insights without requiring navigation. Where to Use It Sales Analysis: Hover over a region in a chart to view detailed sales figures for that region. Inventory Management: Quickly drill down into stock levels for a selected category. Customer Insights: See detailed demographic or behavior data tied to a selected group. Operational Monitoring: Provide instant access to system or process metrics based on high-level dashboard interactions. Benefits Streamlined Navigation: No need to switch between dashboards manually. Enhanced User Experience: Users access detailed data quickly and visually. Context Preservation: Filters maintain the relevance of displayed data. Improved Decision-Making: Faster access to insights enables quicker actions. Important Notes Ensure the target dashboard is optimized to display relevant data for the selected category. Filters passed to the target dashboard are processed in order, so prioritize essential filters by moving them to the top of the list. Users should test thoroughly to confirm all filters are passed correctly and that the embedded tooltip displays as expected. The tooltip may take a few seconds to load as it is a separate dashboard. Only one category is passed to the target dashboard This approach transforms Sisense dashboards, making them interactive and insightful while maintaining simplicity for users. Please note the solution provided is without official support from the Sisense side.
