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();
});
})();
