/**
* Main function that checks a single-level date filter of day-level granularity
* (either 'members' or 'from/to') for a single selected by variable date dimension. The dashboard script determines
* whether the selected date is earlier than the earliest date in the current dashboard’s
* datasource. If so, the function redirects to another dashboard while preserving the rest
* of the URL parameters and path segments.
*
* Use Case:
* - One dashboard has a datasource with only recent data (no older dates).
* - Another dashboard has a datasource with all historical data.
* - If a user selects a date earlier than the “recent” dataset supports, the script redirects
* them to the “all data” dashboard, retaining any query parameters or path info in the URL.
*/
function checkAndRedirectIfNeeded() {
/**
* Toggle console logging for clarity or debugging.
*/
const enableLogging = true;
/**
* The dimension string for the date filter in the current dashboard's datasource.
* Example: "[Commerce.Date (Calendar)]".
* This code checks a single-level date filter of day-level granularity for this dimension. Change as needed
*/
const dateDim = "[Commerce.Date (Calendar)]";
/**
* The alternate dashboard ID to which a user is redirected if they select a date
* older than what the current datasource includes. This dashboard ID replaces the current
* dashboard ID in the URL, preserving any extra path or query parameters.
*/
const alternateDashboardId = "67bebb6863199f002a7b0906";
/**
* Logs to the console only if enableLogging is true.
* @param {...any} msgs - Items to log.
*/
function log(...msgs) {
if (enableLogging) {
console.log(...msgs);
}
}
/**
* Searches the dashboard's filters for a single-level date filter matching dimStr.
* Returns the filter object if found, otherwise null.
* @param {string} dimStr - The date dimension to look for.
*/
function findDateFilter(dimStr) {
if (!dashboard.filters || !dashboard.filters.$$items) {
log("No filters accessible on this dashboard.");
return null;
}
for (const filter of dashboard.filters.$$items) {
if (filter.jaql && filter.jaql.dim === dimStr) {
log("Found date filter for dimension:", dimStr);
return filter;
}
}
return null;
}
/**
* Builds a minimal JAQL query to fetch the earliest date (sorted ascending) from the specified dimension.
* Uses the current dashboard's datasource, and returns only 1 row (count: 1).
*
* This ensures we get the earliest available date in the dataset for the dimension in question,
* typically returned as an ISO-like string in the response data.
*
* @param {string} dimStr - The dimension string representing the date field.
*/
function buildEarliestDateQuery(dimStr) {
return {
datasource: dashboard.datasource,
metadata: [
{
jaql: {
dim: dimStr,
datatype: "datetime",
level: "days",
sort: "asc"
}
}
],
count: 1
};
}
/**
* Runs the JAQL query using Sisense's internal HTTP service if available, otherwise jQuery.ajax.
* The request is made synchronous (async: false). If the internal service doesn't exist, fallback is standard AJAX.
*
* @param {Object} jaql - The JAQL query object.
* @returns {Promise} - Resolves with the server response or rejects on error.
*/
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: false,
xhrFields: { withCredentials: true }
};
return $internalHttp ? $internalHttp(ajaxConfig, false) : $.ajax(ajaxConfig);
}
/**
* Replaces only the dashboard ID in the current URL with alternateDashboardId,
* preserving the rest of the URL (query parameters, path segments, etc.).
* If the current dashboard ID is not found, the redirect is canceled.
*/
function redirectToAlternateDashboard() {
const currentDashId = dashboard.oid;
if (!currentDashId) {
log("Cannot determine current dashboard ID. Redirect canceled.");
return;
}
const currentUrl = window.location.href;
if (!currentUrl.includes(currentDashId)) {
log("Current URL does not contain the expected dashboard ID. Redirect canceled.");
return;
}
const newUrl = currentUrl.replace(currentDashId, alternateDashboardId);
log("Redirecting to alternate dashboard:", newUrl);
// Perform the actual redirect
window.location.href = newUrl;
}
/**
* Main logic flow:
* 1) Fetch the earliest date in the dataset for dateDim by building and running a JAQL query.
* 2) Locate the single-level date filter for dateDim in the dashboard filters.
* 3) Check the user's selected date:
* - If 'members' type, use the first member.
* - If 'from/to' type, use 'from'.
* 4) Compare the selected date (JS Date) to the earliest date (JS Date).
* 5) If selected date is earlier, redirect to the alternate dashboard; otherwise, do nothing.
*/
const jaqlQuery = buildEarliestDateQuery(dateDim);
runHTTP(jaqlQuery)
.then(response => {
if (!response?.data?.values?.length) {
log("No data returned for earliest date query. No redirect needed.");
return;
}
// The earliest date is stored in .data, typically an ISO-like date string (e.g. "2023-01-05T00:00:00").
const earliestDateString = response.data.values[0][0].data;
log("Earliest date in the dataset:", earliestDateString);
// Convert the earliest date string to a JS Date object for comparison
const earliestDate = new Date(earliestDateString);
if (isNaN(earliestDate.valueOf())) {
log("Earliest date is invalid or unrecognized:", earliestDateString);
return;
}
// Find the single-level date filter on the dashboard
const dateFilter = findDateFilter(dateDim);
if (!dateFilter || !dateFilter.jaql.filter) {
log("Date filter not found or missing filter object. Skipping redirect check.");
return;
}
let selectedDateStr = null;
// If it's a 'members' type filter, use the first member, this is the earliest
if (
Array.isArray(dateFilter.jaql.filter.members) &&
dateFilter.jaql.filter.members.length > 0
) {
selectedDateStr = dateFilter.jaql.filter.members[0];
log("Filter type: 'members'. Selected date string:", selectedDateStr);
// If it has 'from' (and possibly 'to'), use 'from'
} else if (dateFilter.jaql.filter.from) {
selectedDateStr = dateFilter.jaql.filter.from;
log("Filter type: 'from/to'. Selected 'from' date string:", selectedDateStr);
} else {
log("Filter is not recognized or has no selected date. Skipping redirect.");
return;
}
// Convert the filter's date string to a JS Date
const selectedDate = new Date(selectedDateStr);
if (isNaN(selectedDate.valueOf())) {
log("Selected date is invalid or unrecognized:", selectedDateStr);
return;
}
// Compare for earliest date
if (selectedDate < earliestDate) {
log("Selected date is earlier than the earliest dataset date. Redirecting...");
redirectToAlternateDashboard();
} else {
log("Selected date is within or after earliest dataset date. No redirect needed.");
}
})
.catch(error => {
log("Error retrieving earliest date or comparing filter date:", error);
});
}
/**
* Calls the main logic function after the dashboard is initialized.
*/
dashboard.on("initialized", function () {
checkAndRedirectIfNeeded();
});
/**
* Also calls the main logic function whenever filters change.
*/
dashboard.on("filterschanged", function () {
checkAndRedirectIfNeeded();
});
How It Works
-
Earliest Date Lookup
The script first builds a small custom secondary JAQL query (sorted in ascending order, limited to one result) to determine the earliest date in the data source. It executes this query using Sisense’s internal service function, using the native Sisense cookies. This type of scripting functionality has been discussed in more detail in previous knowledge base articles, such as this article. -
Filter Inspection
After fetching the earliest date, the script searches for a single-level date filter matching the configured date dimension.- If the filter uses a members array, the script reads the first member.
- If the filter uses a from and to range structure, it uses the from date, as this is always the earliest.
-
Comparison
The script converts both the earliest date and the selected date into JavaScript Date objects. If the selected date is earlier than the earliest date, the script triggers a redirect. -
Redirect Logic
- Only the dashboard ID portion of the current URL is replaced with the specified alternateDashboardId.
- Path segments, query parameters, and any other pre-existing parts of the URL remain the same.
Use Cases
-
Limited vs. Full Dataset
When one Sisense dashboard is restricted to recent data (for example, the last 30 days) and another includes all historical data. This script diverts users to the appropriate dashboard seamlessly. -
Consistent User Experience
Avoids confusion when a date filter extends beyond a limited dataset’s timeframe. Instead of showing empty or invalid data, the user is sent to a more comprehensive dashboard. -
Multi-Dashboard Navigation
Ensures the user can continue with the same URL parameters and path segments, simply swapping the restricted dashboard for the alternate “all data” one.
Conclusion
By employing this or similar scripts based on this form of customization (this example is more advanced in using a custom secondary JAQL request to fetch the first data value, but this is not necessary in many scenarios), it is possible to streamline date-based filter navigation or other filter-based navigation between dashboards or data sources, guaranteeing for example that if a date selection falls outside the scope of a limited dataset, the user automatically sees data in a broader data source dashboard. This functionality improves user experience and allows for wide customizability.
