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.
Limiting Date Range Filters in Sisense Dashboards
Wide date ranges in Sisense dashboards can lead to performance issues, especially when using live models or querying large datasets. For live data models, large queries increase costs as more data is pulled from the data warehouse. For Elasticubes, this can cause performance bottlenecks. To avoid these issues, here is a quick solution to "limit" the date range users can select, ensuring both cost-efficiency and smooth performance. Read more to find out how!
Schedule sequential ElastiCube builds using windows task scheduler
In many cases we would like to schedule our ElastiCubes builds to run one by one, using just one command, or to run in a specific hour so our users won't create or watch dashboards while the cube is building and also will have recently updated data. Although you can schedule a cube to build in a specific hour through the scheduled build settings in the ElastiCube Manager, this post can help you to define more complex and specific terms to run the build. For example building all your ElastiCubes in one process and determining a specific hour for the build. Step 1: Copy the script and save it as .bat file This script contains a few steps to activate a cube build. The following lines are the commands to build the cubes. Add a new line for every ElastiCube you want to build. It is crucial to add the "/wait" part so every command start in its turn only after the previous is finished. @echo off start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\<My_Cube_A>.ecube" serverAddress="LocalHost" mode="restart" start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\<My_Cube_B>.ecube" serverAddress="LocalHost" mode="restart" @end For more information about available command parameters, please see our PSM documentation at https://sisense.dev/guides/legacy/psm/#sisense-shell-psm-exe. Using the PSM commands, you can build from either a .ecube file or an existing ElastiCube. Example to build from a .ecube file: start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\Users\Administrator\Documents\My ElastiCubes\Northwind.ecube" serverAddress="LocalHost" mode="restart" Example to build from an existing ElastiCube in the ElastiCube Server Console: start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="Northwind" serverAddress="LocalHost" mode="restart" Here are the available build types: mode="restart": Rebuilds the ElastiCube entirely. mode="full": Rebuilds the ElastiCube and accumulates data for tables marked as accumulative. This mode should only be used for accumulative builds. mode="changes": Rebuilds from scratch tables that have changed in the ElastiCube schema. mode="metadataonly": Updates the ElastiCube server with the ElastiCube schema, without building. Step 2 : Creating a new task Open the windows task scheduler and create a new task. Name the task and set security filters as followed. *note that the user account running the task MUST BE AN ADMINISTRATOR USER Step 3: Setting the Triggers In the triggers tab create a new trigger and define the time to activate the command, this can be an interval for every few hours or run on a specific hour. In case you want to run the task a few times a day in predetermined hours, for every build occurrence create a new trigger. (i.e one occurrence in the morning and one at night). Step 4: Specifying the build action and finishing In the actions tab create a new one and choose "Start a Program" action, then choose the .bat file you created. Edit the Condition and Settings tabs as required (optional) and hit OK. Test the task to see if it's working by right clicking on the task and choosing "Run". In cases you want one cube to run every hour and another cube only once a day at a specific time, and don't want the builds to run in the same time, you can create a task with the following script to run every hour, with a loop that contains a time window to run a second cube. the following script will run CUBE_A every time the task starts, but CUBE_B will run only if the time is between 4pm and 5pm: @echo off start /wait /d "C:\Program Files\Sisense\Prism" psm ecs stopstart /wait /d "C:\Program Files\Sisense\Prism" psm ecs startstart /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="CUBE_A" serverAddress="LocalHost" mode="restart" set "now=%time: =0%" if "%now%" geq "16:00:00,00" if "%now%" lss "17:00:00,00" ( start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="CUBE_B" serverAddress="LocalHost" mode="restart" ) @end In the “if” line you should change the time window to run the other cubes. Add a line for each cube, add a new loop for every time window (notice the parenthesis) **NOTE: if a build fails for some reason, the following ones in the script will not run.
