Connection Tool - Programmatically Remove Unused Datasource Connections, and List All Connections
Managing connections within your Sisense environment can become complex over time, if there are a large number of connections, and connections are often added, and replace earlier datasource connections. In some scenarios unused connections can accumulate, potentially cluttering the connection manager UI with no longer relevant connections. Although unused connections typically represent minimal direct security risk, it's considered best practice to maintain a clean, organized list of connections, and in some scenarios it can be desired to remove all unused connections. Sisense prevents the deletion of connections actively used in datasources, safeguarding your dashboards and datasources from disruptions. However, inactive or "orphaned" connections remain after datasources are deleted or a connection is replaced, potentially contributing to unnecessary UI complexity in the connection manager UI. Connections can be of any type Sisense supports, common types include various SQL connections, Excel files, and CSV files, as well as many data providers, such as Big Panda. This tool can also be used to list all connections, with no automatic deletion of unused connections.
Supercharging Your Tabular Views: AG Grid Powered by Sisense Compose SDK
Supercharging Your Tabular Views: AG Grid Powered by Sisense Compose SDK How to Build a Tabular View with Sisense Compose SDK and AG Grid This guide walks through how to use Sisense Compose SDK's query hooks to power an AG Grid table component in React. We'll break down the steps, explain the code, and highlight the key functionalities of AG Grid. Introduction Tables are essential in BI applications for summarizing large datasets and providing users with a simple way to navigate, filter, and analyze data. AG Grid is an ideal solution for tabular views, offering robust capabilities like sorting, filtering, grouping, and data exporting. When combined with the Sisense Compose SDK, you can power real-time, data-driven applications that provide users with seamless, customizable data interactions. In this example, we are using a sample eCommerce dataset, available through the Sisense Compose SDK free trial, to demonstrate how powerful this combination can be. Users can explore product sales by country, age range, and time period in a fully interactive grid. AG Grid's advanced tabular capabilities include features like multi-level row grouping, custom value formatting, pivot mode for creating complex data hierarchies, and the ability to export data to formats like CSV. These features, when integrated with Sisense's query hooks and real-time data, enable developers to create highly dynamic dashboards where users can manipulate large datasets with ease. This combination of Sisense Compose SDK and AG Grid empowers developers to create rich, interactive data experiences, allowing users to filter and manipulate data at granular levels, all while leveraging real-time querying powered by Sisense. Step-by-Step Breakdown of the Code Setting Up the Project packages used: npm install ag-grid-react ag-grid-community @mui/material @sisense/sdk-ui @sisense/sdk-data Register AG Grid Modules AG Grid uses modules to enable functionality like client-side row models, and sorting and filtering. We register the modules to be used within AG Grid: import { AgGridReact } from "ag-grid-react"; import "ag-grid-community/styles/ag-grid.css"; import "ag-grid-community/styles/ag-theme-alpine.css"; These imports ensure that AG Grid is properly styled and functional. Setting Up the Query with Sisense SDK Sisense Compose SDK’s `useExecuteQuery` is used to fetch data from your data sources. The query can include dimensions and measures which AG Grid will render. const queryProps = useMemo(() => ({ dataSource: DM.DataSource, dimensions: [DM.Country.Country, DM.Commerce.AgeRange, DM.Commerce.Date.Years], measures: [ measureFactory.sum(DM.Commerce.Revenue, "Total Revenue"), measureFactory.sum(DM.Commerce.Quantity, "Total Quantity"), ], }), []); Here, `useExecuteQuery` executes the query based on the defined data source (`DM.DataSource`), dimensions (e.g., country, age range), and measures (e.g., revenue, quantity). Fetching and Displaying Data We leverage React's `useEffect` hook to update the state of `rowData` once data is fetched. This ensures AG Grid displays up-to-date information. const { data, isLoading, isError } = useExecuteQuery(queryProps); useEffect(() => { if (!isLoading && !isError && data) { const rows = data.rows.map((row) => ({ country: row[0]?.text || "N/A", ageRange: row[1]?.text || "N/A", year: row[2]?.text || "N/A", revenue: row[3]?.data || 0, quantity: row[4]?.data || 0, })); setRowData(rows); } }, [data, isLoading, isError]); This block processes the raw data returned from the query and formats it for use in the AG Grid. Column Definitions AG Grid requires column definitions that define how each field should be displayed. const columnDefs = useMemo(() => [ { field: "country", headerName: "Country" }, { field: "ageRange", headerName: "Age Range" }, { field: "year", headerName: "Year" }, { field: "revenue", headerName: "Total Revenue", valueFormatter: (params) => abbreviateNumber(params.value), // Helper function for number formatting }, { field: "quantity", headerName: "Total Quantity", valueFormatter: (params) => abbreviateNumber(params.value), }, ], []); We define five columns: country, age range, year, revenue, and quantity. The `valueFormatter` function ensures that numbers are displayed in an abbreviated format (e.g., "1.2K" for thousands). AG Grid Configuration The grid configuration includes `defaultColDef` for common properties across all columns (like filtering and sorting), and `animateRows` for smoother transitions. const defaultColDef = useMemo(() => ({ flex: 1, minWidth: 100, sortable: true, filter: true, resizable: true, }), []); Here, all columns are set to be sortable, filterable, and resizable by default. Exporting Data AG Grid’s API allows exporting table data to CSV. We use a button to trigger the export functionality: const onBtnExport = useCallback(() => { gridRef.current.api.exportDataAsCsv(); }, []); Rendering the Grid Finally, we render the AG Grid component, passing the `rowData`, `columnDefs`, and `defaultColDef` as props: <AgGridReact ref={gridRef} rowData={rowData} columnDefs={columnDefs} defaultColDef={defaultColDef} animateRows={true} /> This sets up the AG Grid to dynamically render data retrieved via Sisense Compose SDK. Value of Tabular Views Tabular views are crucial for presenting structured data, providing an easy way to explore, filter, and analyze datasets. AG Grid’s built-in features like sorting, filtering, and exporting make it a perfect fit for visualizing data-rich tables in BI environments. Features of AG Grid - Sorting and Filtering: Users can sort and filter data by column. - Grouping: Group rows by common fields. - Customization: Full flexibility in column definitions and row configurations. - Exporting: Allows exporting table data to CSV format. - Performance: Handles large datasets efficiently. Using Sisense Compose SDK to Power Components Sisense Compose SDK is an API-first approach to data querying, which powers the `useExecuteQuery` hook in this component. By combining it with AG Grid, you can easily visualize dynamic, real-time data in table format. Here is a functional example in Github https://github.com/sisensers/ag-grid-compose-sdk.gitLimiting 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.Auto zoom Area Map widget to a specific country on load (Linux)
Auto zoom Area Map widget to a specific country on load To test this solution, you must create a dashboard based on Sample ECommerce and an Area Map widget based on a Country column. Once it is done - please go to the widget editor, click three dots select Edit Script, then paste the following script there: As a result, we have our Area Map widget zoomed to Germany on the initial load: const countryName = 'Germany' widget.on('processresult', (scope, args)=>{ saveMapObj() }) widget.on('domready', (scope, args)=>{ zoomToCountry(L.geoJson(widget.queryResult.geoJson), window.currentMap, countryName); }) function zoomToCountry(geoData, map, countryName) { // Create a new empty GeoJSON object to store only the selected country's feature(s) geoData.eachLayer(function (layer) { if (layer.feature.properties.name === countryName) { map.fitBounds(layer.getBounds()); // Zoom to country bounds } }) } function saveMapObj() { let leafletHookStatus = $$get(prism, 'autoZoomAreaMap.leaflet.initHookAdded') if(!leafletHookStatus) { L.Map.addInitHook(function () { window.currentMap = this }) $$set(prism, 'autoZoomAreaMap.leaflet.initHookAdded', true); } } [ALT Text: A map of Europe with colored regions indicating specific areas. Central Europe is highlighted in teal, while other areas appear in gray. The United Kingdom is visible to the northwest. The date displayed at the top indicates June 10, 2025.] DO NOT CHANGE!!! 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.Hide vertical lines on the Pivot widget (Linux)
Hide vertical lines on the Pivot widget To test this solution, you must create a dashboard based on any cube and create any Pivot widget. Once it is done - please go to the widget editor, click three dots select Edit Script, then paste the following script there: const howManyRowsToIgnore = 2; widget.on('ready', () => { const cells = element[0].querySelectorAll('.table-grid__cell'); let maxColIndex = 0; cells.forEach(cell => { const match = cell.className.match(/table-grid__cell--col-(\d+)/); if (match) { const colIndex = parseInt(match[1], 10); if (colIndex > maxColIndex) { maxColIndex = colIndex; } } }); cells.forEach(cell => { const colMatch = cell.className.match(/table-grid__cell--col-(\d+)/); const rowMatch = cell.className.match(/table-grid__cell--row-(\d+)/); if (colMatch && rowMatch) { const colIndex = parseInt(colMatch[1], 10); const rowIndex = parseInt(rowMatch[1], 10); if (rowIndex >= howManyRowsToIgnore) { if (colIndex !== 0) { cell.style.borderLeft = 'none'; } if (colIndex !== maxColIndex) { cell.style.borderRight = 'none'; } } } }); }); Please note there is a howManyRowsToIgnore parameter that specifies how many first rows to ignore. It is done to have the possibility to leave vertical lines for header values if needed. If you need to hide all vertical lines including header-related lines as well - just set this value to 0. As a result, we have a Pivot widget with hidden vertical lines: [ALT Text: A table titled "Budget" displaying a list of brands along with their corresponding total costs. The first entry, "ABC," shows a total cost of $3,564,045.86. The subsequent entries include brands such as "Addimax," "Adderax," and "Addulator Inc," along with their respective total costs, ranging from around $100 to several million. The table spans multiple rows and includes a total of 2094 entries. The header indicates columns for "Brand" and "Total Cost."] 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.
REST API - Adding Data Level Security
When building code to automate the process of adding users (or groups), it may be beneficial to add security around those users. Follow the steps below to learn how to add data level security through the REST API: Step 1 From your SiSense home page, navigate to the Manage tab and choose the option for REST API. From here, click on the link to the REST API Reference interface. Step 2 From here, choose the 0.9 version and expand the Elasticube section and scroll down to the POST /elasticubes/datasecurity section. Step 3 The sample code below shows a valid JSON object to use as part of the REST API request. Starting from the sample code, replace the value for party with a user identifier that already exists in your system. Then paste the code into the REST API interface and click run. You should see a response of 200, which indicates a successful operation. [{ "server": "LocalHost", "elasticube": "Sample Lead Generation", "table": "Lead Generation", "column": "Country", "datatype": "text", "shares": [{ "party": "5c0f85690ca2f66cc242e266", "type": "user" }], "members": [ "United States", "England" ] }] Notes The server name value is case sensitive, so make sure it matches with your system. The sample provided is specific to one of the sample databases that comes with SiSense. To modify this to a different system, just update necessary fields to include your specifications. The sample provided is an array that consists of a single element. This could be modified to contain several entries all sent at once.How to Troubleshoot UI Issues Using DevTools (HAR File & Console Logs)
If a webpage, dashboard, or widget isn't loading properly, encounters errors during exporting, importing, editing, or opening, or if buttons are unresponsive and error messages appear, you can use Developer Tools (DevTools) in your browser to diagnose the issue. This guide will show you how to: - Check the Network tab for failed requests. - Use the Preview and Response tabs to see details of errors. - Check the Console tab for other issues. - Save a HAR file to share with support.Custom error notification image
Custom error notification image This article explains how to develop the plugin to replace the default error image. Of course, in ideal world there are no errors, but who does live in the ideal one? As a result of this article’s implementation, we will accomplish the following: ALT Text: A comparison table with two columns titled "Original" and "Custom." Each column contains the same message about an error querying a data model, mentioning a specific dimension related to healthcare. The "Original" message features a yellow warning icon, while the "Custom" message has a red explosion icon. Both messages prompt the user to "Try again." To accomplish this we will develop a new plugin. For more details about plugin development please check this article: https://community.sisense.com/t5/knowledge-base/plugin-dev-tutorial/ta-p/9087 To create a new plugin we will need to do the following: Use file manager to navigate to the folder ‘plugins’; In the folder ‘plugins’ create a new folder and name it ‘updateErrorLogo’; Open the newly created folder and create two files: main.6.js; plugin.json. Open the file ‘plugin.json’ and paste there the following content: { "name": "updateErrorLogo", "source": [ "main.6.js" ], "lastUpdate": "2025-01-10T22:36:03.764Z", "folderName": "updateErrorLogo", "isEnabled": true, "version": "1.0.0", "skipCompilation": true, "pluginInfraVersion": 2 } 5. Open the file `main.6.js’ and paste there the following code: //Import your image. Value of the variable newErrorSvg is internal URL to the image import newErrorSvg from './newErrorSvg.svg'; //Subscription at the loading directive slfQueryErrorBrand mod.directive('slfQueryErrorBrand', [ () => { return { restrict: 'C', link: async($scope, lmnt, attrs) => { $(lmnt).hide(); //Hiding default logo await fetch(newErrorSvg).then((response) => { //Load SVG return response.text().then(svgContent => { //Convert response into text lmnt[0].innerHTML = svgContent; //Replace default content with our custom one }); }).catch((error) => { console.error('Error loading SVG:', error); }).finally(() => { $(lmnt).show(); //Show logo }); } }; } ]); 6. Place an image with the name ‘newErrorSvg.svg’ you want to be shown instead of the default. Now, after the plugins rebuild you will see your new image instead of the default one. Enjoy your customized error image. In the attachment, you can find the completed plugin.
