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.How to rename the Jump to Dashboard (JTD) name in the right-click menu
This article provides guidance on how to customize the name displayed in the right-click menu for the Jump to Dashboard (JTD) feature in Sisense. This is particularly useful for making dashboard names more user-friendly and intuitive.Setting Date Filter Members to Specific Days of the Week in Sisense Programmatically
Sisense natively supports a wide range of date filter functionalities and formats. However, certain scenarios may call for unusual custom programmatic forms of date filtering, such as filtering to specific days of the week within a defined date range. For example, focusing a dashboard view on only Wednesdays in a given period, or on weekend dates, can yield valuable insights in time-sensitive analyses. This cannot be directly configured with standard Sisense UI filters when only one date dimension is present in the data source. In the previous article, the concept of date calculations was demonstrated by leveraging JavaScript date objects, modifying filter parameters programmatically via dashboard or widget scripting, and converting those date objects into formats Sisense expects. (For reference, see: Using Native Javascript Date Calculations To Modify Sisense Date Filters) The same principle can be applied to "Day of the Week" based filtering. Sisense allows defining a custom set of date members as a filter. By dynamically constructing a list of dates that fall on certain days of the week within a chosen date range, it is straightforward to pass these custom members into Sisense filters, setting them as members of a date member filter and creating a selective date filter. In the snippet below, JavaScript generates a list of dates falling on specified weekdays—such as Saturdays and Sundays—within a given start and end date range. It then formats them into the string format Sisense expects for filter members. These can be applied within the filter object just as demonstrated in the previously linked article on programmatic date filter modification.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.Customizing Upper Label (PlotBands) Labels in Sisense Column Chart Widgets for Long Category Labels
Sisense allows customization of chart settings through Highchart option configurations, as documented in this community post leveraging the Highcharts settings of the widget using Sisense widget scripting capabilities. When creating Sisense column chart widgets with two fields in the "Categories" panel, an upper label is created. These labels can be long, requiring management of lengthy category labels in column chart widgets. In the Highcharts library terminology, these upper labels are called plotBands. This article explains how to modify plotBand label settings in Sisense column chart widgets to support long upper category labels by adjusting font size and enabling multi-line labels. This method demonstrates another use of the beforeviewloaded event to adjust Highcharts configurations in a Sisense widget, similar to adjusting offset sizes in pie charts as discussed in this article.
