Skip to main content
Sisense Community logo
  • Community Feedback
  • Chapters
  • Events
  • Forums
    • Help and How To
    • Product Feedback Forum
    • Strategy & Use Cases
  • Blogs
  • KB Docs
    • KB Docs
    • Add-Ons & Plug-Ins
    • APIs
    • Best Practices
    • Blox
    • CDT
    • Cloud Managed Service
    • Data Models
    • Data Sources
    • Embedding Analytics
    • How-Tos & FAQs
    • Onboarding
    • PySisense
    • Security
    • Sisense Administration
    • Sisense Intelligence & AI
    • Troubleshooting
    • Widget & Dashboard Scripts
  • Support
  • Learning
    • Sisense Academy: Free Courses and Certifications
    • Official Developer Documentation
    • Official Product Documentation
    • Official Sisense Youtube Channel
    • Sisense Compose SDK Playground
  • Use Case Gallery
Loading…
   
All PostsDiscussionsBlogsIdeasQuestions
Leaderboards
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
  •                    
Discussions
  • TagsChevronRightIcon
Code-First
  • Blog banner
    • PySisenseChevronRightIcon

    Modifying Sisense Widgets With PySisense

                                                                                                                                     

    Modifying Sisense Widgets With PySisense Overview A chart widget's metadata, including the dimension driving its category axis and the measures driving its values, lives inside the widget's own JSON definition rather than on a separate settings screen. Changing one, through the widget's Edit panel, can be done quickly. Mass modifying the same field across many widgets, or repeating the same change every time a dashboard is rebuilt from a template, is not something the Sisense UI is built to do in bulk. This article describes swap_widget_dimension.py , a Python command-line tool that reads a Sisense chart widget, replaces the field used by one item on a named panel, and writes the result back using the pySisense library. By default it targets the categories panel, a bar chart's category axis, but the same get_widget_by_id and update_widget calls apply to any panel in the widget's metadata.panels structure, including values , so the tool also works for swapping a measure. The worked example throughout is a bar chart with two versions on a dashboard that differ only in their measure: one totals Revenue, the other totals Cost. The script changes only the field of one panel item on one widget per run. It is meant primarily as an example, and can be adapted to cover other widget attributes as needed. The full source is available on GitHub in this repository . About PySisense PySisense is a Python SDK for the Sisense REST API, installed via pip . It provides wrappers around common Sisense operations and handles authentication, session management, and request errors internally. This script uses three methods from pySisense's Dashboard class: get_widget_by_id , which retrieves a widget's full current definition; update_widget , which writes a modified definition back and strips the fields Sisense manages internally ( oid , owner , created , and so on) before sending the request; and, through Dashboard 's access_mgmt attribute, get_my_user , which identifies the Sisense user the API token belongs to. Note: The current release of PySisense on PyPI does not yet include get_widget_by_id , update_widget , or find_widgets_by_type . This functionality is planned for the next PySisense release. The development branch of the PySisense repository already contains it and can be installed directly from there: pip install "git+https://github.com/sisense/pysisense.git@dev" How a chart widget stores its fields A Sisense chart widget's configuration lives under metadata.panels , a list of panel objects. For a bar chart, the panel named categories holds the dimension shown on the category axis, and the panel named values holds the measures. Each panel has an items list, and each item carries a jaql object identifying one field, with table , column , and dim keys ( dim in the form [Table.Column] ), plus a title and other fields, such as datatype or agg , that vary by field type. Panel names and item order can differ by widget type and by how a chart was originally built, so it's worth confirming the structure on the specific widget being changed rather than assuming it. Running the script with --show prints a widget's current panels for exactly this purpose, before anything is changed: python swap_widget_dimension.py --show Prerequisites Python 3.10 or later pySisense (for now, currently the dev branch, this will be merged into the main branch shortly) and PyYAML Network access to the Sisense instance A Sisense API token: Admin > REST API > v1.0 > GET /authentication/tokens/api Only a dashboard's owner can write to its widgets. If the API token's user does not own the dashboard being changed, use a token belonging to the dashboard's owner, or transfer ownership first with pySisense's change_dashboard_owner . Setup pip install "git+https://github.com/sisense/pysisense.git@dev" pyyaml Create a config.yaml with the Sisense instance details, the widget to change, and the field to switch to: sisense: domain: https://your-instance.sisense.com token: YOUR_API_TOKEN_HERE is_ssl: true widget: dashboard_id: YOUR_DASHBOARD_ID widget_id: YOUR_WIDGET_ID dimension: table: Commerce column: Region title: Region Run with --show first to confirm the widget has the panel expected, then continue with the sequence in Usage. Configuration reference sisense.domain , sisense.token , sisense.is_ssl : connection details for the Sisense instance. widget.dashboard_id , widget.widget_id : the widget to change. Both can be overridden for a single run with --dashboard-id and --widget-id without editing config.yaml . widget.admin_access : left unset by default, which auto-detects the right setting (see Troubleshooting below). Set to true or false , or pass --admin-access or --no-admin-access , only to skip that auto-detection. dimension.panel : the metadata.panels entry to change. categories for a bar chart's category axis, values for its measures. Defaults to categories if unset. Can be overridden with --panel . dimension.table , dimension.column , dimension.title : the new field to apply. table and column identify it; title is the label shown on the widget and defaults to column when omitted. All three can be overridden with --table , --column , and --title . Usage Inspect the widget's current panels before changing anything: python swap_widget_dimension.py --show Preview the change without writing it: python swap_widget_dimension.py Apply the change: python swap_widget_dimension.py --apply If the result is not correct, revert to the widget's prior state: python swap_widget_dimension.py --undo Troubleshooting Whether adminAccess is needed depends on ownership, not the Sisense version. get_widget_by_id can fail with a schema validation error mentioning adminAccess , on Sisense instances that reject the parameter outright, or with a permission error, on instances that require it for a widget the API token's user doesn't own. Rather than have the caller guess which applies, the script tries without admin access first, and only retries with admin access if that fails. It also prints a line identifying the token's user and whether that user owns the widget, so the reason either attempt succeeded is visible. --admin-access and --no-admin-access are still available to force one path and skip the auto-detection. Worked example: swapping a measure from Revenue to Cost A dashboard can have two versions of the same bar chart widget that differ only in their values panel, one totaling Commerce.Revenue , the other Commerce.Cost . Pointing the script at the Revenue widget and targeting the values panel swaps it to Cost, keeping its sum aggregation intact: python swap_widget_dimension.py \ --dashboard-id YOUR_DASHBOARD_ID --widget-id YOUR_WIDGET_ID \ --panel values --table Commerce --column Cost --title "Total Cost" \ --apply Running --show against the widget beforehand returns: { "table": "Commerce", "column": "Revenue", "dim": "[Commerce.Revenue]", "datatype": "numeric", "columnTitle": "Revenue", "tableTitle": "Commerce", "agg": "sum", "title": "Total Revenue" } and after --apply : { "table": "Commerce", "column": "Cost", "dim": "[Commerce.Cost]", "datatype": "numeric", "columnTitle": "Cost", "tableTitle": "Commerce", "agg": "sum", "title": "Total Cost" } table , column , dim , columnTitle , tableTitle , and title all changed to Cost; datatype and the sum aggregation in agg carried over unchanged, exactly as build_new_jaql is designed to do. Common use cases Reusing one chart across dashboard variants A bar chart that should show a different measure, or a different category dimension, on each of several dashboards can be built once and adjusted per dashboard with a single command instead of being rebuilt in the widget editor each time. Correcting a field after templating Dashboards built from a template sometimes carry the template's placeholder field. Rather than reopening every affected widget by hand, each one can be pointed at the correct field directly. Working with more than one item on a panel A widget with more than one item on the panel being changed, such as a grouped bar chart with two category dimensions, needs --match-column to identify which one to replace: python swap_widget_dimension.py --apply --match-column Category Full option reference usage: swap_widget_dimension.py [-h] [--config FILE] [--dashboard-id ID] [--widget-id ID] [--panel PANEL] [--table TABLE] [--column COLUMN] [--title TITLE] [--match-column COLUMN] [--show] [--apply] [--undo] [--backup-file FILE] [--admin-access | --no-admin-access] Swap the field used by one item on a Sisense chart widget panel. options: -h, --help show this help message and exit --config FILE Path to config.yaml. --dashboard-id ID Overrides widget.dashboard_id from config.yaml. --widget-id ID Overrides widget.widget_id from config.yaml. --panel PANEL Name of the metadata.panels entry to change, for example categories (a bar chart's category axis) or values (its measures). Overrides dimension.panel from config.yaml. Defaults to 'categories' if neither is set. --table TABLE Overrides dimension.table from config.yaml. --column COLUMN Overrides dimension.column from config.yaml. --title TITLE Overrides dimension.title from config.yaml. --match-column COLUMN Column name of the item to replace within --panel, if it has more than one item. Defaults to the first item. --show Print the widget's current metadata.panels and exit. Use this to confirm panel names and item order before swapping anything. --apply Write the change to Sisense. Without this flag, only a preview is printed. --undo Restore the widget to the state recorded in --backup- file, then exit. --backup-file FILE Where the widget's prior state is saved before a change, and read back from during --undo. --admin-access Always fetch with adminAccess=true, skipping auto- detection. Needed when the API token's user does not own the widget. --no-admin-access Always fetch without adminAccess=true, skipping auto- detection. Needed on Sisense instances that reject adminAccess as an unrecognized query parameter. Use case Using swap_widget_dimension.py instead of manual widget edits offers several advantages: Applies a field change to a widget directly through its JSON definition, without opening the widget editor Works on either a chart's category dimension or its measures, through the same --panel option Provides a way to confirm a widget's panel structure with --show before any change is made Auto-detects whether admin access is needed, rather than requiring the caller to know their token's relationship to the widget in advance Provides a backup of the widget's prior state before every applied change, with --undo to restore it Demonstrates how pySisense's get_widget_by_id and update_widget can be used together to read, modify, and write back any field in a widget's definition, not only its dimension Summary swap_widget_dimension.py gives Sisense administrators and developers a way to change the field used by a chart widget's category axis or its measures from the command line, with a preview step, a way to inspect the widget's actual structure first, auto-detection of whether admin access is needed, and a backup that makes any applied change reversible. The same get_widget_by_id and update_widget pattern used here, fetch the widget, modify the field that needs to change, write it back, applies to other widget fields beyond the ones shown in this example. """swap_widget_dimension.py: swap the field used by one item on a Sisense chart widget panel. Reads a widget with ``Dashboard.get_widget_by_id()``, replaces the field used by one item in a named ``metadata.panels`` entry, such as ``categories`` (a bar chart's category axis) or ``values`` (its measures), and writes the result back with ``Dashboard.update_widget()``. "Dimension" is used loosely here, the way most readers of this script will mean it: the field dropped into that panel, whether it is technically a dimension or a measure. This is a worked example of pySisense's widget read and write functions, not a general purpose widget editor: it changes one field on one panel item on one widget per run, and does not touch anything else on the widget. Note: get_widget_by_id, update_widget, and find_widgets_by_type are not yet in the pySisense release on PyPI. Install from the dev branch to use this script: pip install "git+https://github.com/sisense/pysisense.git@dev" Usage ----- python swap_widget_dimension.py --show # print the widget's panels, change nothing python swap_widget_dimension.py # preview the swap, change nothing python swap_widget_dimension.py --apply # write the change to Sisense python swap_widget_dimension.py --undo # restore from the backup file python swap_widget_dimension.py --panel values --apply # swap a measure instead of the category axis Whether a widget fetch needs adminAccess=true depends on whether the API token's own user already owns the widget. Rather than requiring the caller to know that in advance, fetch_widget() below tries without admin access first and only escalates if that fails. """ from __future__ import annotations import argparse import json import sys from pathlib import Path from typing import Any import yaml from pysisense import Dashboard, SisenseClient SCRIPT_DIR = Path(__file__).parent DEFAULT_CONFIG_PATH = SCRIPT_DIR / "config.yaml" DEFAULT_BACKUP_PATH = SCRIPT_DIR / "widget_backup.json" # The metadata.panels entry this script targets when --panel and # dimension.panel are both left unset: a bar chart's category axis. DEFAULT_PANEL_NAME = "categories" def load_config(path: Path) -> dict[str, Any]: """Load and parse the YAML configuration file. Parameters ---------- path : Path Location of the config.yaml file to read. Returns ------- dict[str, Any] The parsed configuration. Empty dict if the file is empty. Raises ------ SystemExit If no file exists at ``path``. """ if not path.exists(): sys.exit( f"Config file not found: {path}\n" "Copy config.example.yaml to config.yaml and fill in the instance URL, " "token, and widget details." ) with path.open() as config_file: return yaml.safe_load(config_file) or {} def build_client(config: dict[str, Any]) -> SisenseClient: """Construct a SisenseClient from the ``sisense`` section of config.yaml. Parameters ---------- config : dict[str, Any] The full parsed configuration, as returned by :func:`load_config`. Returns ------- SisenseClient A client ready to use with pySisense's Dashboard class. Raises ------ SystemExit If ``sisense.domain`` or ``sisense.token`` is missing. """ sisense_config = config.get("sisense", {}) domain = sisense_config.get("domain", "") token = sisense_config.get("token", "") if not domain or not token: sys.exit("config.yaml must include sisense.domain and sisense.token.") return SisenseClient(domain=domain, token=token, is_ssl=sisense_config.get("is_ssl", True)) def describe_current_user(dashboard: Dashboard) -> dict[str, Any] | None: """Look up the Sisense user the API token belongs to. Used only to print a diagnostic line explaining whether the token's own user already owns the widget being changed, which is what actually determines whether admin access is needed (see :func:`fetch_widget`). A failed lookup here is not fatal to the rest of the script. Parameters ---------- dashboard : Dashboard A pySisense Dashboard instance, whose ``access_mgmt`` attribute exposes ``get_my_user()``. Returns ------- dict[str, Any] | None The logged-in user object (``GET /api/users/loggedin``), or ``None`` if the lookup failed for any reason. """ try: user = dashboard.access_mgmt.get_my_user() except Exception: return None if isinstance(user, dict) and "error" not in user: return user return None def fetch_widget( dashboard: Dashboard, dashboard_id: str, widget_id: str, *, admin_access: bool | None, ) -> tuple[dict[str, Any], bool]: """Fetch a widget, auto detecting whether admin access is required. Whether a fetch needs ``adminAccess=true`` depends on whether the API token's own user already owns the widget, not on the Sisense version. On top of that, some Sisense versions reject ``adminAccess`` outright as an unrecognized query parameter, which looks identical from here to "not the owner". Rather than have the caller guess, this tries the cheaper, no-admin-access request first, and only escalates if that fails. Parameters ---------- dashboard : Dashboard A pySisense Dashboard instance. dashboard_id : str The ``oid`` of the dashboard that contains the widget. widget_id : str The ``oid`` of the widget to fetch. admin_access : bool | None ``True`` or ``False`` forces that mode and skips auto-detection. ``None`` tries without admin access first, then retries with admin access only if the first attempt fails. Returns ------- tuple[dict[str, Any], bool] The fetched widget, and whether admin access was actually used to retrieve it. Raises ------ SystemExit If the widget cannot be fetched in the requested mode(s). """ if admin_access is not None: widget = dashboard.get_widget_by_id(dashboard_id, widget_id, admin_access=admin_access) if "error" in widget: sys.exit(f"Could not fetch widget: {widget['error']}") return widget, admin_access widget = dashboard.get_widget_by_id(dashboard_id, widget_id, admin_access=False) if "error" not in widget: return widget, False error_without_admin_access = widget["error"] widget = dashboard.get_widget_by_id(dashboard_id, widget_id, admin_access=True) if "error" not in widget: return widget, True sys.exit( "Could not fetch widget, with or without admin access.\n" f" Without admin access: {error_without_admin_access}\n" f" With admin access: {widget['error']}" ) def print_fetch_diagnostics(dashboard: Dashboard, widget: dict[str, Any], admin_access_used: bool) -> None: """Print a line explaining whether the token's user owns this widget. Purely informational: makes the reason admin access was, or was not, needed for this particular widget visible, instead of leaving the ``True``/``False`` in ``admin_access_used`` unexplained. Printed to stderr, not stdout, so it never lands in ``--show``'s JSON output and can be piped straight into something like ``jq``. Parameters ---------- dashboard : Dashboard A pySisense Dashboard instance, used to look up the current user. widget : dict[str, Any] The widget returned by :func:`fetch_widget`. admin_access_used : bool Whether the fetch that retrieved ``widget`` used admin access. Returns ------- None """ access_note = "with admin access" if admin_access_used else "without admin access" current_user = describe_current_user(dashboard) if current_user is None: print(f"Fetched widget {access_note} (could not look up the current user for comparison).", file=sys.stderr) return identity = current_user.get("email") or current_user.get("userName") or current_user.get("_id") owns_widget = current_user.get("_id") == widget.get("owner") ownership_note = "owns this widget" if owns_widget else "does not own this widget" print(f"Fetched widget {access_note}. Authenticated as {identity!r}, who {ownership_note}.", file=sys.stderr) def find_panel_item( widget: dict[str, Any], panel_name: str, match_column: str | None, ) -> tuple[list[dict[str, Any]], int]: """Locate the panel item to change. Parameters ---------- widget : dict[str, Any] The full widget object, as returned by ``get_widget_by_id``. panel_name : str The ``metadata.panels`` entry to search, for example ``"categories"`` (a bar chart's category axis) or ``"values"`` (its measures). match_column : str | None If given, selects the item whose current ``jaql.column`` equals it. If ``None``, the first item in the panel is used, which is correct whenever that panel only has one item, such as a simple bar chart's single category dimension or single measure. Returns ------- tuple[list[dict[str, Any]], int] The panel's ``items`` list, and the index within it of the item to change. Raises ------ SystemExit If the widget has no panel named ``panel_name``, the panel has no items, or ``match_column`` does not match any item in it. """ panels = widget.get("metadata", {}).get("panels", []) panel = next((candidate for candidate in panels if candidate.get("name") == panel_name), None) if panel is None or not panel.get("items"): sys.exit(f"No {panel_name!r} panel with items found on this widget. Run --show to inspect its structure.") items = panel["items"] if match_column is None: return items, 0 for index, item in enumerate(items): if item.get("jaql", {}).get("column") == match_column: return items, index sys.exit(f"No item with column {match_column!r} found in the {panel_name!r} panel. Run --show to see the current items.") def build_new_jaql(existing_jaql: dict[str, Any], table: str, column: str, title: str | None) -> dict[str, Any]: """Return a copy of ``existing_jaql`` with its field swapped. ``table``, ``column``, and ``dim`` always change. ``title`` changes to ``title`` or, if that is ``None``, to ``column``. ``columnTitle`` and ``tableTitle`` (Sisense's own display-label mirrors of ``column`` and ``table``, confirmed by inspecting a live widget) are updated to match whenever the existing jaql already had them, so nothing about the field still points at the old one under a different key. ``agg`` and every other key already present (``datatype``, and so on) are carried over unchanged. Preserving ``agg`` in particular is what makes this safe to use on a ``values`` panel item, not only a ``categories`` one: swapping Revenue for Cost keeps the sum aggregation the widget already had, instead of resetting it. Parameters ---------- existing_jaql : dict[str, Any] The jaql object currently on the panel item being replaced. table : str The table of the new field. column : str The column of the new field. title : str | None The label to show on the widget for the new field. Defaults to ``column`` when ``None``. Returns ------- dict[str, Any] A new jaql object with the field swapped. """ new_jaql = dict(existing_jaql) new_jaql["table"] = table new_jaql["column"] = column new_jaql["dim"] = f"[{table}.{column}]" new_jaql["title"] = title or column if "columnTitle" in new_jaql: new_jaql["columnTitle"] = column if "tableTitle" in new_jaql: new_jaql["tableTitle"] = table return new_jaql def write_widget(dashboard: Dashboard, dashboard_id: str, widget_id: str, payload: dict[str, Any]) -> None: """Write ``payload`` back to Sisense via ``Dashboard.update_widget()``. Parameters ---------- dashboard : Dashboard A pySisense Dashboard instance. dashboard_id : str The ``oid`` of the dashboard that contains the widget. widget_id : str The ``oid`` of the widget being written. payload : dict[str, Any] The full widget object to write back. Returns ------- None Raises ------ SystemExit If the API reports a write failure. """ result = dashboard.update_widget(dashboard_id, widget_id, payload) if "error" in result: sys.exit(f"Update failed: {result['error']}") def parse_args() -> argparse.Namespace: """Define and parse this script's command-line arguments. Returns ------- argparse.Namespace The parsed arguments. """ parser = argparse.ArgumentParser(description="Swap the field used by one item on a Sisense chart widget panel.") parser.add_argument("--config", default=str(DEFAULT_CONFIG_PATH), metavar="FILE", help="Path to config.yaml.") parser.add_argument("--dashboard-id", metavar="ID", help="Overrides widget.dashboard_id from config.yaml.") parser.add_argument("--widget-id", metavar="ID", help="Overrides widget.widget_id from config.yaml.") parser.add_argument( "--panel", help="Name of the metadata.panels entry to change, for example categories (a bar " "chart's category axis) or values (its measures). Overrides dimension.panel from " f"config.yaml. Defaults to {DEFAULT_PANEL_NAME!r} if neither is set.", ) parser.add_argument("--table", help="Overrides dimension.table from config.yaml.") parser.add_argument("--column", help="Overrides dimension.column from config.yaml.") parser.add_argument("--title", help="Overrides dimension.title from config.yaml.") parser.add_argument( "--match-column", metavar="COLUMN", help="Column name of the item to replace within --panel, if it has more than one item. " "Defaults to the first item.", ) parser.add_argument( "--show", action="store_true", help="Print the widget's current metadata.panels and exit. Use this to confirm panel " "names and item order before swapping anything.", ) parser.add_argument( "--apply", action="store_true", help="Write the change to Sisense. Without this flag, only a preview is printed.", ) parser.add_argument( "--undo", action="store_true", help="Restore the widget to the state recorded in --backup-file, then exit.", ) parser.add_argument( "--backup-file", default=str(DEFAULT_BACKUP_PATH), metavar="FILE", help="Where the widget's prior state is saved before a change, and read back from during --undo.", ) admin_access_group = parser.add_mutually_exclusive_group() admin_access_group.add_argument( "--admin-access", dest="admin_access", action="store_true", default=None, help="Always fetch with adminAccess=true, skipping auto-detection. Needed when the API " "token's user does not own the widget.", ) admin_access_group.add_argument( "--no-admin-access", dest="admin_access", action="store_false", help="Always fetch without adminAccess=true, skipping auto-detection. Needed on Sisense " "instances that reject adminAccess as an unrecognized query parameter.", ) return parser.parse_args() def main() -> None: """Entry point: parse arguments, then fetch, inspect, swap, or restore a widget's dimension.""" args = parse_args() config = load_config(Path(args.config)) client = build_client(config) dashboard = Dashboard(api_client=client) widget_config = config.get("widget", {}) dashboard_id = args.dashboard_id or widget_config.get("dashboard_id") widget_id = args.widget_id or widget_config.get("widget_id") if not dashboard_id or not widget_id: sys.exit( "dashboard_id and widget_id are required, either in config.yaml under 'widget' " "or via --dashboard-id/--widget-id." ) admin_access = args.admin_access if args.admin_access is not None else widget_config.get("admin_access") if args.undo: backup_path = Path(args.backup_file) if not backup_path.exists(): sys.exit(f"No backup file found at {backup_path}. Nothing to undo.") original_widget = json.loads(backup_path.read_text()) write_widget(dashboard, dashboard_id, widget_id, original_widget) print(f"Widget {widget_id} restored from {backup_path}.") return widget, admin_access_used = fetch_widget(dashboard, dashboard_id, widget_id, admin_access=admin_access) print_fetch_diagnostics(dashboard, widget, admin_access_used) if args.show: print(json.dumps(widget.get("metadata", {}).get("panels", []), indent=2)) return dimension_config = config.get("dimension", {}) panel_name = args.panel or dimension_config.get("panel") or DEFAULT_PANEL_NAME table = args.table or dimension_config.get("table") column = args.column or dimension_config.get("column") title = args.title or dimension_config.get("title") if not table or not column: sys.exit("table and column are required, either in config.yaml under 'dimension' or via --table/--column.") items, index = find_panel_item(widget, panel_name, args.match_column) old_jaql = items[index].get("jaql", {}) new_jaql = build_new_jaql(old_jaql, table, column, title) print(f"\nWidget: {widget.get('title') or widget_id!r} ({widget.get('type')})") print(f" {'Panel:':<15}{panel_name!r}") print(f" {'Current field:':<15}{old_jaql.get('table')}.{old_jaql.get('column')} (title: {old_jaql.get('title')!r})") print(f" {'New field:':<15}{table}.{column} (title: {new_jaql['title']!r})") if not args.apply: print("\n[PREVIEW] No changes made. Re-run with --apply to write this change to Sisense.") return backup_path = Path(args.backup_file) backup_path.write_text(json.dumps(widget, indent=2)) print(f"\nBacked up current widget state to {backup_path}.") items[index]["jaql"] = new_jaql write_widget(dashboard, dashboard_id, widget_id, widget) print(f"Widget {widget_id} updated. To revert: python {Path(__file__).name} --undo --backup-file {backup_path}") if __name__ == "__main__": main()

    Before and After Change Via Tool
    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.

    Jeremy Friedel
    Jeremy Friedel
    0
             
  • Blog banner
    • Embedding AnalyticsChevronRightIcon

    From Zero to ComposeSDK: Building a Sisense React Application with an AI Coding Assistant

                                                                                                                                     

    From Zero to ComposeSDK: Building a Sisense React Application with an AI Coding Assistant A computer with no development related setup, no code editor application, no programming languages or runtimes such as Node installed, no development tooling of any kind, can create a running ComposeSDK React application in a single development session, connected to a live Sisense instance and ready for continued iteration. The computer can be a laptop, desktop, or server, and the finished app can be served from localhost, a local IP address, or a domain. Almost none of this development and setup has to be done by hand. An AI coding assistant installs the tooling, runs the commands, and writes the code, while the developer describes what should happen and confirms the results along the way. This style of development is sometimes called vibe coding, which refers to working conversationally and letting the AI handle the mechanics. Any modern LLM based coding assistant can drive this workflow. It needs two capabilities, editing code files in the project folder and running terminal commands. This article uses Claude Code in VS Code as the concrete example, but the same steps work in other editors with agentic assistants, in a standalone CLI tool like Claude CLI, or in a desktop app with terminal access like Claude Desktop. The prompts translate directly and are not specific to Claude. Only two steps require manual installation. The first is installing the text editor with its AI assistant, and the second is generating an API token inside the Sisense web interface. Everything else happens through conversation, from installing Node.js to creating the project, configuring credentials, generating the .ts representation of a Sisense data model, and building the widgets, dashboards, and other visualizations themselves. The starting point on the Sisense side is a modern Linux instance with at least one data model, either an ElastiCube or a live model, and permission to generate an API bearer token on it. Prerequisites A modern Linux Sisense instance with at least one data model Permission to generate an API bearer token on that instance A computer with the ability to install programs and CLI access Access to an LLM like Claude, or similar Getting Started An editor and an AI assistant have to be installed before anything can be done conversationally, so the first two steps are done manually. Download and install Visual Studio Code . Open the Extensions view in VS Code, search for the assistant of choice, install it, and sign in. For Claude Code, see the Claude Code documentation . Other agentic assistants with file editing and terminal access work the same way. One behavior is worth knowing about before the first prompt. Agentic assistants ask for approval before running each terminal command, so expect to click Yes regularly during setup, and read each command before approving it. The approval step exists so the developer always knows what is about to run on the machine, and it only provides that protection if the commands are actually read. Most assistants also offer an option to allow certain commands without asking again, which becomes convenient once a pattern has been reviewed a few times. Editing files inside the project folder is a sensible thing to approve universally. Commands that reach outside the project folder, or that install programs system wide, should be reviewed every time and not auto approved. How Granular to Be The sections below proceed step by step, with each prompt doing one focused thing. That structure is deliberate. It makes each step verifiable before the next is added, and it gives the reader a chance to learn what is happening at every stage. It is not a limitation of the LLM. In practice, an agentic assistant handles much larger requests comfortably. Given an empty folder and little else, a single prompt can carry the project most of the way. Set up a new ComposeSDK React application in this folder. Install Node if it is missing, create a React TypeScript Vite project, add the Sisense ComposeSDK packages, and start the dev server. My Sisense instance is at https://example.sisense.com and I will provide an API token in the file where it is needed, tell me where it is. The assistant works through the chain on its own, installing what is missing, flagging where the token goes, and reporting back when the dev server is running. Granular prompts tend to be the better choice while learning. Broad prompts work better once the process is familiar. Most real sessions mix both freely. If any errors are visible, pasting the error message into the LLM chat will often allow the LLM to resolve the issue. Checking the browser developer tools console for error messages to copy can also be helpful. Handing the Environment to the Assistant From this point forward, work happens in the assistant's chat panel. The first task is the development environment itself. To open the Claude VS Code UI, click the orange flower icon in the top right corner when a text file is open in VS Code. Open VS Code in a new empty folder that will hold the project (File, Open Folder, then create or select a directory. Name the folder appropriately, to match the name of the ComposeSDK (CSDK) application). Create an empty text file if the Claude VS Code icon (or the equivalent for the LLM in use) is hidden. Then open the LLM chat panel and ask the assistant to set up the toolchain. Install the latest LTS version of Node on this machine, make sure it is on the path, then verify that node and npm both work from the terminal. The assistant detects the operating system, picks an appropriate install method for that platform, runs it, and confirms the versions. If the path changed during installation, the assistant will usually mention that the terminal or VS Code may need a restart before the change takes effect. Node.js can also be installed manually for the relevant OS from the Node.js website download section , if the LLM struggles to install it via CLI. This first exchange establishes the pattern for everything that follows. State the goal, let the LLM assistant run the commands, confirm the result, move to the next step. Creating the Project With the toolchain verified, the next request creates the application. Create a new React TypeScript app in this folder using Vite, install the dependencies, and start the dev server so I can confirm the default page loads. The assistant plans and sets up the project, installs packages, and starts the Vite dev server, usually at http://localhost:5173 . Open that URL in a browser and confirm the default Vite page renders. Confirming each layer before adding the next is a habit worth keeping. If a later step fails, a verified baseline makes it obvious where the problem was introduced. Installing ComposeSDK ComposeSDK ships as npm packages, so adding it is one request. Add the Sisense ComposeSDK packages to this project: sdk-ui and sdk-data as dependencies, and the SDK CLI as a dev dependency. The three packages serve distinct purposes. @sisense/sdk-ui provides the React components, the charts, dashboards, and filter UI. @sisense/sdk-data provides the query construction layer, including dimensions, measures, and filter factories. @sisense/sdk-cli is a development tool used to generate the TypeScript data model files covered later in this article, and never ships with the application. Full ComposeSDK documentation is available on developer.sisense.com. Connecting to the Sisense Instance Two values connect and authenticate the CSDK application to Sisense, the instance URL and an API bearer token. Generate the token in the Sisense web interface, from the user profile menu under API Token ( Sisense documentation on generating API tokens ). Copy it once generated. The recommended approach keeps the token out of the LLM chat entirely, so the LLM never has access to it. The assistant builds the configuration with the token left blank. Create a .env file with VITE_SISENSE_URL set to https://example.sisense.com and VITE_SISENSE_TOKEN left blank. Add .env to .gitignore, deny your own read access to .env in your permission settings, wrap the app in SisenseContextProvider using those environment variables, and tell me where to paste my token. The assistant creates the .env file with the relevant keys, excludes it from both version control and its own file access, and points to where the token goes. Editing the URL and pasting the token into .env directly in the editor completes the setup. From that point the application can read the bearer token value but the assistant cannot. In Claude Code specifically, the mechanism for blocking its own access is a permission deny rule rather than an ignore file. The rule lives in .claude/settings.json at the project level, or in ~/.claude/settings.json to apply across all projects. { "permissions": { "deny": ["Read(./.env)", "Read(./.env.*)"] } } The /permissions command in Claude Code can also add this rule interactively, saving it to .claude/settings.local.json . Other assistants have their own equivalents, whether ignore files or access controls. Whichever tool is in use, it is worth verifying the block before pasting the real token. Put a dummy value in .env , ask the assistant to read the file, and confirm it refuses. Pasting the token straight into the chat and letting the assistant write the whole file also works, but is not recommended, since it means trusting the chat tool with a credential. Either way, keep .env out of version control and rotate the token if exposure is suspected. Clicking the refresh icon in the Profile bearer token page rotates the token. A token from a viewer role user is all that is required for CSDK, as long as that user has access to every dashboard and data source the application uses. Bearer tokens are also not the only option. ComposeSDK can authenticate with Web Access Tokens (WAT) and SSO as well, which are worth considering beyond initial development ( ComposeSDK authentication documentation ). The resulting setup looks approximately like this in main.tsx , the entry file Vite generates. Wrapping inside App.tsx works just as well, as long as the provider sits above every ComposeSDK component. import { createRoot } from 'react-dom/client'; import { SisenseContextProvider } from '@sisense/sdk-ui'; import App from './App.tsx'; // The provider supplies the connection to every ComposeSDK component below it. // Values come from .env so credentials stay out of source control. createRoot(document.getElementById('root')!).render( <SisenseContextProvider url={import.meta.env.VITE_SISENSE_URL} token={import.meta.env.VITE_SISENSE_TOKEN} > <App /> </SisenseContextProvider> ); Smoke Testing the Connection Before building further, it is worth confirming the application can reach and authenticate with the Sisense instance. There are two reasonable ways to do it. The fastest is DashboardById , which renders an existing Sisense dashboard and requires no data model work in the project. Open any dashboard in Sisense and copy its OID from the browser URL. Render the dashboard with OID 65a1b2c3d4e5f6a7b8c9d0e1 from my Sisense instance on the main page using DashboardById, replacing the default Vite content. The alternative is a minimal chart against a known data model, which previews the widget workflow used in the rest of this article. Either one confirms the same thing, that the URL and token work and the instance is reachable from the browser. A rendered dashboard means the connection is established. A blank or broken one usually traces back to one of these common causes. The token is invalid or belongs to a user without access to the dashboard or data source. Regenerate the token and confirm the user can open the dashboard in Sisense directly. The URL is incorrect, commonly a missing https:// or a trailing path. The value should be the bare instance origin. The browser blocks the request with a CORS error. ComposeSDK typically works without any CORS configuration, but if the console shows a CORS error, an administrator can add http://localhost:5173 to the allowed origins in the Sisense Admin section under Security Settings ( Sisense documentation on CORS configuration ). Browser console errors generally identify which of these applies, and the error output can be pasted straight into the chat for diagnosis. Generating the TypeScript Data Model ComposeSDK includes a built in tool for this step. The SDK CLI installed earlier generates a .ts file that describes the data model, listing its tables, dimensions, and measures as typed TypeScript objects, so widget code references real fields with autocomplete rather than hand typed strings. It works the same way whether the model is an ElastiCube or a live model. Being specific in the prompt matters here. Naming the goal, a .ts file generated by the SDK's own CLI, steers the assistant toward the right tool, there otherwise being the possibility it would try to create or hallucinate its own representation. Use the ComposeSDK CLI (the get-data-model command from @sisense/sdk-cli) to generate a .ts file describing my data model named Sample ECommerce, and save it to src/models. Use the token in .env. Under the hood this runs a command of the following form. # Generates a .ts file describing the data model's tables, dimensions, and measures. # The token and URL are the same values used by the app itself. npx @sisense/sdk-cli get-data-model \ --url https://example.sisense.com \ --token <token> \ --dataSource "Sample ECommerce" \ --output src/models/sample-ecommerce.ts The generated .ts file exports a typed object, conventionally imported as DM , containing every table, dimension, and measure in the model. Everything built from this point on references fields through that object, which means typos become compile errors instead of silently empty charts. Building the First Widget With the .ts model file in place, building the first real chart is a single request. Using the generated Sample ECommerce data model in src/models, add a column chart below the dashboard showing total Revenue by Condition. The assistant writes a component along these lines. import { Chart } from '@sisense/sdk-ui'; import { measureFactory } from '@sisense/sdk-data'; import * as DM from './models/sample-ecommerce'; // A minimal ComposeSDK chart: one category dimension, one aggregated measure. <Chart dataSet={DM.DataSource} chartType="column" dataOptions={{ category: [DM.Commerce.Condition], value: [measureFactory.sum(DM.Commerce.Revenue, 'Total Revenue')], }} /> Once it renders, iteration can be done through continued conversation or manual code editing. The Vite application will auto update with any changes, without requiring manual refreshes. Change it to a bar chart, break it down by Gender, and only include 2024 data. The assistant adds the breakBy entry to the data options and a date filter to the chart, and the result appears on the next hot reload. Describe a change, see it render, refine. That loop is the core of the workflow, and it is the same whether the change is a chart type, a filter, or a layout adjustment. Styling and Structuring the Application The same conversational approach turns the result from a test page into something that looks like an application. Add a professional looking header and footer to the application, including the title [app title], and lay out the dashboard and the chart in cards. What this prompt actually says will vary by user and project. The card layout here is just one option, and the assistant follows whatever visual direction it is given, from minimal to fully branded. One distinction is worth knowing. Application CSS and widget theming are separate layers. Page layout, headers, and footers are ordinary React and CSS. The appearance of the ComposeSDK widgets themselves, including chart colors, fonts, and backgrounds, is controlled through the SDK's ThemeProvider component ( ComposeSDK theming documentation ). Asking for "a dark theme on the charts" versus "a dark background on the page" will, correctly, touch different code. Working with Live Data The ComposeSDK application itself does not typically contain or store data, though exceptions exist, a point that is often misunderstood. Every widget sends a JAQL query to the Sisense instance at render time, so the CSDK application always reflects the current state of the data model. For an ElastiCube that means fresh data appears after each build, and for a live model queries hit the source directly. In neither case does updated data require rebuilding or redeploying the application. The generated .ts model file only needs regeneration when the schema changes, such as new fields, renamed columns, or restructured tables. Data refreshes do not affect it. When the schema does change, regeneration is one request. My data model schema changed. Use the ComposeSDK CLI to regenerate the .ts model file and tell me if any existing widget code references fields that no longer exist. Because the model file is typed, removed fields surface as compile errors, and the assistant can identify and fix the affected components in the same exchange. Prompt Library The prompts below are starting points, not strings that need to be copied exactly. Everything in this article was done conversationally, and describing the goal in natural language as it comes to mind is usually faster than locating a prompt to paste. What makes these prompts work is their specificity, with actual field names, file locations, and a clear definition of when the task is finished. Those qualities carry over to any phrasing, including a single broad prompt that covers several steps at once. Setup and environment: Install the latest LTS version of Node, make sure it is on the path, and verify node and npm from the terminal. Create a React TypeScript Vite app here, install dependencies, and start the dev server. Set up a complete ComposeSDK React app in this folder: install anything missing, create the project, add the SDK packages, and get the dev server running. ComposeSDK installation and connection: Add the ComposeSDK packages: sdk-ui and sdk-data as dependencies and the SDK CLI as a dev dependency. Create a .env with my Sisense URL and a blank token, add it to .gitignore, deny your own read access to it, wire up SisenseContextProvider from those variables, and tell me where to paste my token. The dashboard component shows a network error. Here is the browser console output: [paste]. Diagnose whether this is CORS, the token, the URL, or something else. Data and widgets: Use the ComposeSDK CLI to generate a .ts file for the data model named [name] into src/models. Render the dashboard with OID [oid] using DashboardById. Add a line chart of [measure] by [date dimension] at month granularity, with a member filter on [dimension] limited to [values]. Add breakBy on [dimension] to the existing column chart and move the legend to the bottom. Advanced: Use useExecuteQuery to fetch [measure] grouped by [dimension] and render the result in a plain HTML table instead of an SDK chart component. Use onBeforeRender on the line chart to set the line width to 3 and enable data labels through the underlying Highcharts options. The advanced prompts reference real ComposeSDK extension points. useExecuteQuery runs a query directly and returns rows for custom rendering, and onBeforeRender exposes the underlying Highcharts options object before a chart draws, allowing customization beyond the SDK's own props ( useExecuteQuery reference , chart customization reference ). Useful Links ComposeSDK documentation ComposeSDK quickstart for React Claude Code documentation Sisense REST API and authentication documentation The techniques in this article generalize. Any capability in the ComposeSDK documentation, from dashboard filters to custom widget types, is reachable the same way. Describe it to the assistant with enough specificity, confirm the result in the browser, and refine conversationally.

    Jeremy Friedel
    Jeremy Friedel
    3
             
  • Blog banner
    • APIsChevronRightIcon

    Connection Tool - Programmatically Remove Unused Datasource Connections, and List All Connections

                                                                                                                                                                     

    Connection Tool - A Tool to Programmatically Remove Unused Datasource Connections, and List All Connections     Managing connections within your Sisense server 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. Introducing the Sisense Connection Prune Tool The Sisense Connection Prune Tool is a Python-based Sisense API based tool designed to programmatically identify and delete unused connections. It generates a CSV report listing all connections and their associated datasources, streamlining your connection management process. If desired, it can automatically remove all unused connections automatically from a Sisense server.   Using the Tool, sourcing the Virtual Environment, generating the Connection CSV   CSV Output of Used Connections and Associated Datasources   CSV opened visually to view as Table, Excel and other programs and text editors can open CSV files   Sisense Connection Prune Tool README Here's the full README included with the tool: # Sisense Connection Prune Tool A command-line tool to list used data connections and prune unused Sisense connections via CSV. It allows you to generate a CSV file of all connections and their dependencies, then delete those connections if needed, after removing the connections to keep from the CSV. ## Features - **Dry Run Mode**: Simulate deletions without making any changes. - **CSV-Based Flow**: Easily inspect and list connections to remove before deletion. - **Logging**: Extensive logs if needed. - **Error Handling**: Clear and descriptive messages for issues encountered during execution. ## Usage 1. **Activate the Virtual Environment** After downloading this project folder, activate the Python virtual environment bundled with it that includes all Python dependencies. - **Windows**: `venv\Scripts\activate` - **macOS/Linux**: `source venv/bin/activate` 2. **Configure the Tool** Open the `config.yaml` file and set your Sisense server URL, bearer token, CSV file path, and log file path. For example: ```yaml server_url: "https://your.sisense.server" bearer_token: "your_bearer_token_here" dry_run: true csv_file_path: "connections.csv" log_file_path: "connection_tool.log" ``` - **server_url**: The URL of your Sisense instance. - **bearer_token**: Your Sisense API token for authentication. - **dry_run**: If set to `true`, deletions will be simulated (no real deletions). - **csv_file_path**: Where the CSV file should be created and read from. - **log_file_path**: Where log file will be stored. 3. **Run the Tool** ```bash python3 ConnectionPruneTool.py ``` You will be prompted to choose an option: 1. **Generate connection CSV** - Fetches all Sisense connections. - Immediately removes (or simulates removing, if `dry_run` is `true`) any connection with no dependencies. - Writes all remaining connections and their dependencies to the CSV file. - **Important**: Inspect the CSV file and remove lines for any connections you want to **keep**. 2. **Delete connections from CSV list** - Reads the CSV file. - Removes or simulates removing each connection still listed. - Provides a summary report of which connections were deleted or bypassed. 4. **Review the Logs** Check the file specified in `log_file_path` for a record of all actions taken or simulated if needed. This is helpful for understanding what happened during each run and diagnosing any issues. ## Example Workflow 1. **Generate CSV** ```bash python3 ConnectionPruneTool.py # Choose option 1 when prompted ``` After generation, open the CSV file and **delete rows** corresponding to any connections you want to **keep**. 2. **Delete Connections** ```bash python3 ConnectionPruneTool.py # Choose option 2 when prompted ``` The tool will read the CSV and delete the remaining listed connections (or simulate deletion, if `dry_run` is enabled). ## Notes - Unused connections are removed automatically in step 1, without a CSV step - To keep a connection, remove its line from the CSV before proceeding with deletion. - If `dry_run` is set to `true`, no actual deletions will occur, only simulated logs and printed messages. - The log file will be cleared at the start of each run, so be sure to review or archive logs (or change log file name in config), if needed. ​ This is a command-line tool to list used data connections and prune unused Sisense connections, in general and via a CSV list. It allows a user with a data admin or higher bearer token to generate a CSV file of all connections and their dependencies, then delete those connections if needed, from the remaining connections in the CSV. Example Output: Deleted unused connection: Old_DB_Connection (ID: 123abc) CSV file generated at connections.csv. It contains 25 row(s) of active connections. Please review and remove lines for connections you want to keep before running deletion step by running tool again. Remaining lines will be deleted in deletion mode. Summary Report: Total lines in CSV (active used connections): 25 Deleted Unused Connections: - Old_DB_Connection No connections were bypassed.   API Endpoints Used Retrieve connections: GET /api/v2/connections Retrieve dependencies: GET /api/v2/connections/{connection_id}/getAllDependencies Delete connection: DELETE /api/v2/connections/{connection_id} Full Code connections.py - Uses Sisense API endpoints to: Fetch all connections (GET /api/v2/connections). Retrieve datasource dependencies for a specific connection (GET /api/v2/connections/{connection_id}/getAllDependencies). Delete a specific connection (DELETE /api/v2/connections/{connection_id}).     from helperFunctions import load_config, api_get, api_delete config = load_config() headers = {"Authorization": f"Bearer {config['bearer_token']}"} base_url = config["server_url"] # Retrieve all connections from Sisense def get_all_connections(): endpoint = f"{base_url}/api/v2/connections" return api_get(endpoint, headers) # Retrieve dependencies of a specific connection def get_connection_dependencies(connection_id): endpoint = f"{base_url}/api/v2/connections/{connection_id}/getAllDependencies" return api_get(endpoint, headers) # Delete a specific connection def delete_connection(connection_id): endpoint = f"{base_url}/api/v2/connections/{connection_id}" return api_delete(endpoint, headers) helperFunctions.py - Uses the Requests library to handle API requests (GET/DELETE). Catches and logs API errors. Uses PyYAML to read the config.yaml file for configuration.     import yaml import requests import logging # Load configuration from YAML file def load_config(): with open("config.yaml", "r") as file: return yaml.safe_load(file) # Configure logging settings def setup_logging(log_file): logging.basicConfig( filename=log_file, level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s", ) # Perform a GET request with error handling def api_get(endpoint, headers): try: response = requests.get(endpoint, headers=headers, verify=False) response.raise_for_status() return response.json() except requests.RequestException as e: logging.error(f"GET request failed: {e}") print(f"Error during GET request: {e}") return None # Perform a DELETE request with error handling def api_delete(endpoint, headers): try: response = requests.delete(endpoint, headers=headers, verify=False) response.raise_for_status() return response except requests.RequestException as e: logging.error(f"DELETE request failed: {e}") print(f"Error during DELETE request: {e}") return None ConnectionPruneTool.py - Serves as the entry point for the entire tool. Implements a CLI for user interaction. Invokes connections.py and helperFunctions.py to retrieve, analyze, and optionally delete Sisense connections. Uses Pandas to build a DataFrame of connection details and writes the results to a CSV file. Reads back the CSV file to remove selected connections after manual edits. Manages logs and prints summary reports to the terminal. import pandas as pd import logging import os from connections import ( get_all_connections, get_connection_dependencies, delete_connection, ) from helperFunctions import load_config, setup_logging # Load config yaml config = load_config() setup_logging(config["log_file_path"]) # Clear log file at start open(config["log_file_path"], "w").close() dry_run = config["dry_run"] csv_path = config["csv_file_path"] def generate_connections_csv(): """ Retrieves all connections from Sisense, deletes any that have no dependencies (unless in dry_run mode), and writes the remaining used connections and their dependent data models to a CSV file. """ # Create or clear existing CSV open(csv_path, "w").close() connections = get_all_connections() if connections is None: logging.error("Failed to retrieve connections.") print("Failed to retrieve connections.") return data = [] deleted = [] bypassed = [] # Go through each connection returned from the API for conn in connections: dependencies = get_connection_dependencies(conn["oid"]) if dependencies is None: logging.warning( f"Failed to retrieve dependencies for: {conn['name']} ({conn['oid']})" ) print( f"Failed to retrieve dependencies for: {conn['name']} ({conn['oid']})" ) bypassed.append(conn["name"]) continue # If no dependencies, optionally delete the connection (not in dry-run) if not dependencies: action_msg = "[Dry Run] Would delete" if dry_run else "Deleted" log_str = ( f"{action_msg} unused connection: {conn['name']} (ID: {conn['oid']})" ) print(log_str) logging.info(log_str) if not dry_run: try: response = delete_connection(conn["oid"]) # If response is None, treat it as a failed deletion if response is None: logging.error( f"Failed to delete unused connection: {conn['name']} ({conn['oid']})." ) print( f"Error deleting unused connection: {conn['name']} ({conn['oid']})" ) bypassed.append(conn["name"]) else: deleted.append(conn["name"]) except Exception as e: logging.error( f"Exception while deleting unused connection: {conn['name']} ({conn['oid']}) - {e}" ) print( f"Error deleting unused connection: {conn['name']} ({conn['oid']})" ) bypassed.append(conn["name"]) else: # If the connection is used, add each dependency row to CSV. # Use a fallback value if 'title' or 'oid' is missing. for dep in dependencies: dep_title = dep.get( "title", "NULL TITLE Share Datasource with associated Bearer Token user to include in CSV", ) dep_oid = dep.get("oid", "NULL OID") data.append( { "Connection Name": conn["name"], "Connection ID": conn["oid"], "Elasticube/Data Model Name": dep_title, "Elasticube/Data Model ID": dep_oid, } ) # Create a DataFrame of only the used connections (with datasource dependencies) df = pd.DataFrame(data) df.to_csv(csv_path, index=False) row_count = len(df) logging.info(f"Generated CSV at {csv_path}") print( f"CSV file generated at {csv_path}. " f"It contains {row_count} row(s) of used connections.\n" "Please review and remove lines for connections you want to keep " "before running deletion step by running tool again. Remaining lines will be deleted in deletion mode." ) # Summaries for auto-deleted (unused) connections print("\nSummary Report:") print(f"Total lines in CSV (active used connections): {row_count}") if deleted: print("\nDeleted Unused Connections:") for d in deleted: print(f" - {d}") else: print("\nNo connections were deleted in this step.") if bypassed: print("\nBypassed Connections:") for b in bypassed: print(f" - {b}") else: print("\nNo connections were bypassed in this step.") def delete_connections_from_csv(): """ Reads the CSV (remaining lines after user review, removing lines for connections to keep), then deletes each connection listed. If a delete fails or returns None, the connection is logged and added to 'bypassed'. """ if not os.path.exists(csv_path): print( "CSV file does not exist. Please generate it first or fix the config path." ) return df = pd.read_csv(csv_path) deleted = [] bypassed = [] for _, row in df.iterrows(): conn_id = row["Connection ID"] conn_name = row["Connection Name"] action_msg = "[Dry Run] Would delete" if dry_run else "Deleted" log_str = f"{action_msg} connection: {conn_name} ({conn_id})" print(log_str) logging.info(log_str) if not dry_run: try: response = delete_connection(conn_id) if response is None: logging.error( f"Failed to delete connection: {conn_name} ({conn_id})" ) print(f"Error deleting connection: {conn_name} ({conn_id})") bypassed.append(conn_name) else: deleted.append(conn_name) except Exception as e: logging.error( f"Exception while deleting connection: {conn_name} ({conn_id}) - {e}" ) print(f"Error deleting connection: {conn_name} ({conn_id})") bypassed.append(conn_name) # Print summary report print("\nSummary Report:") if deleted: print("Deleted Connections:") for d in deleted: print(f" - {d}") else: print("No connections were deleted.") if bypassed: print("\nBypassed:") for b in bypassed: print(f" - {b}") else: print("No connections were bypassed.") if __name__ == "__main__": # If there is no CSV file found, select step 1 automatically if not os.path.exists(csv_path): print( "No CSV file found; defaulting to generating connections CSV. " "Correct config if CSV file name has changed." ) generate_connections_csv() else: choice = input( "Choose an option:\n" "1 - Generate connection CSV\n" "2 - Delete connections from CSV list\n" "Enter your choice (1 or 2): " ) if choice == "1": generate_connections_csv() elif choice == "2": delete_connections_from_csv() else: print("Invalid option. Please enter '1' or '2'.") Conclusion By automating the detection of inactive connections and simplifying their removal, the Sisense Connection Prune Tool reduces clutter in the Sisense server Connection Manager UI while minimizing the risk of unintentionally impacting active datasources. Whether you opt for a dry-run mode to review potential deletions in a generated CSV file, or to simply list connections, or proceed with the full removal of unused connections, this tool offers a clear, flexible, and reliable approach to keeping your connections organized. A full copy of the tool, is attached below.        

    Jeremy Friedel
    Jeremy Friedel
    4
             
  • Blog banner
    • Widget & Dashboard ScriptsChevronRightIcon

    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.   Coloring and Shading Indicate Dates Outside Range 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. Modifying "from" value to match data date range Same for "To" value Removing date member values that are outside data date range (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(); }); })();   Console Output of Script modifying filter to match data date range

    Jeremy Friedel
    Jeremy Friedel
    0
             
  • Blog banner
    • Widget & Dashboard ScriptsChevronRightIcon

    Passing Filters via URL Parameters for Dashboards with Separate Datasources

                                                                                     

    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 and shorter URL's. 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 Sisense Add-ons/Plugins . If your datasources use different dimension names, this code can be adopted to map and match the aligned dimensions. This code uses the full filter JAQL parameter object, if any filter parameters outside the JAQL parameter are needed they can be added via slight modification of this code. This code below includes code for: Reading URL Parameters: The decodeAllFilterParams function updates dashboard filters by reading URL parameters, using keys such as FilterParam and FilterMLParam concatenated with a sanitized version of the filter’s dimension. This is then used to modify the dashboard's filters. Writing URL Parameters: The redirectToDashboard function encodes all current filters back into the URL when a redirect occurs. This ensures that filters persist across dashboard changes even when the underlying datasource differs. Managing the Redirect Flag: Functions like redirectFlagPresent and removeRedirectFlag check for and remove a special flag in the URL (i.e., "justRedirected"). This flag prevents infinite redirect loops. These functions are only required if your code includes redirect functionality.     /** * Returns a URL-encoded version of the given string. * (This removes special characters and spaces that do not belong in a URL.) */ function sanitizeDim(str) { return encodeURIComponent(str || ""); } /** * Reads URL parameters to update existing dashboard filters. * - For single-level filters, the key is "FilterParam" + sanitized(filter.jaql.dim). * - For multi-level filters, the key is "FilterMLParam" + sanitized(level.dim). * Updates only filters whose dimension already exists on the dashboard. */ function decodeAllFilterParams() { if (!dashboard.filters || !dashboard.filters.$$items) return; const url = new URL(window.location.href); dashboard.filters.$$items.forEach(filter => { if (filter.jaql) { const key = "FilterParam" + sanitizeDim(filter.jaql.dim); if (url.searchParams.has(key)) { const value = url.searchParams.get(key); try { const parsed = JSON.parse(value); console.log("Updated filter.jaql for dimension", filter.jaql.dim, "with", parsed); filter.jaql = parsed; } catch (err) { console.log("Error parsing parameter for key", key, err); } } } if (filter.levels && Array.isArray(filter.levels)) { filter.levels.forEach(level => { const key = "FilterMLParam" + sanitizeDim(level.dim); if (url.searchParams.has(key)) { const value = url.searchParams.get(key); try { const parsed = JSON.parse(value); console.log("Updated multi-level filter for dimension", level.dim, "with", parsed); level.filter = parsed; } catch (err) { console.log("Error parsing multi-level parameter for key", key, err); } } }); } }); } /** * Checks if the redirect flag ("justRedirected") is present in the URL. */ function redirectFlagPresent() { const currentUrl = new URL(window.location.href); return currentUrl.searchParams.get("justRedirected") === "true"; } /** * Redirects to a different dashboard by updating the URL. * It re-encodes all current filters into the URL so that the receiving dashboard can read them. * - Single-level filters: Key = "FilterParam" + sanitized(filter.jaql.dim) * - Multi-level filters: Key = "FilterMLParam" + sanitized(level.dim) * Additionally, a redirect flag ("justRedirected") is added to prevent infinite loops. */ function redirectToDashboard(newDashboardId) { if (redirectFlagPresent()) { console.log("Redirect flag is present; skipping additional redirect."); return; } const currentDashboardId = dashboard.oid; if (!currentDashboardId) { console.log("No current dashboard ID; cannot redirect."); return; } const currentUrl = window.location.href; if (!currentUrl.includes(currentDashboardId)) { console.log("Current URL does not contain the dashboard ID; skipping redirect."); return; } const replacedUrl = currentUrl.replace(currentDashboardId, newDashboardId); const urlObj = new URL(replacedUrl); // Re-add all current filters. if (dashboard.filters && dashboard.filters.$$items) { dashboard.filters.$$items.forEach(filter => { if (filter.jaql) { const key = "FilterParam" + sanitizeDim(filter.jaql.dim); urlObj.searchParams.set(key, JSON.stringify(filter.jaql)); } if (filter.levels && Array.isArray(filter.levels)) { filter.levels.forEach(level => { const key = "FilterMLParam" + sanitizeDim(level.dim); if (level.filter) { urlObj.searchParams.set(key, JSON.stringify(level.filter)); } else { urlObj.searchParams.set(key, JSON.stringify(level)); } }); } }); } // Add the redirect flag. urlObj.searchParams.set("justRedirected", "true"); const finalUrl = urlObj.toString(); console.log("Redirecting to URL:", finalUrl); window.location.href = finalUrl; } /** * Removes the redirect flag ("justRedirected") from the URL using history.replaceState, * allowing future filter changes to trigger a redirect. */ function removeRedirectFlag() { const url = new URL(window.location.href); if (url.searchParams.has("justRedirected")) { url.searchParams.delete("justRedirected"); window.history.replaceState(null, "", url.toString()); console.log("Removed redirect flag from URL."); } } An example of a URL including multiple filters encoded in this matter: ?FilterParam%255BMainTable.email%255D=%7B"table":"MainTable","column":"email","dim":"%5BMainTable.email%5D","datatype":"text","merged":true,"datasource":%7B"address":"LocalHost","title":"Old%20Data%20Script%20Test","id":"localhost_aOldIAAaDataIAAaScriptIAAaTest","database":"aOldIAAaDataIAAaScriptIAAaTest","fullname":"localhost%2FOld%20Data%20Script%20Test","live":false%7D,"firstday":"mon","locale":"en-us","title":"email","collapsed":true,"isDashboardFilter":true,"filter":%7B"explicit":true,"multiSelection":true,"members":%5B"aabramofaz@amazon.com"%5D%7D%7D&FilterMLParam%255BMainTable.first_name%255D=%7B"explicit":true,"multiSelection":true,"members":%5B"Abeu","Abigale"%5D%7D&FilterMLParam%255BMainTable.gender%255D=%7B"explicit":false,"multiSelection":true,"all":true%7D&FilterParam%255BMainTable.Revenue%2520Date%2520(Calendar)%255D=%7B"table":"MainTable","column":"Revenue%20Date","dim":"%5BMainTable.Revenue%20Date%20(Calendar)%5D","datatype":"datetime","merged":true,"datasource":%7B"address":"LocalHost","title":"New%20Data%20Script%20Test","id":"localhost_aNewIAAaDataIAAaScriptIAAaTest","database":"aNewIAAaDataIAAaScriptIAAaTest","fullname":"localhost%2FNew%20Data%20Script%20Test","live":false%7D,"firstday":"mon","locale":"en-us","level":"days","title":"Days%20in%20Revenue%20Date","collapsed":true,"isDashboardFilter":true,"filter":%7B"explicit":true,"multiSelection":true,"members":%5B"2024-12-11T00:00:00","2025-03-12T00:00:00"%5D%7D%7D&justRedirected=true This includes the full filter information for the filters shown in the screenshot below:   This code above shows a flexible approach to passing and modifying filters via URL parameters in Sisense. By leveraging custom scripting, you can transfer filters, regardless of their type, between dashboards even if they are backed by separate datasources. This level of control is particularly useful for scenarios where dashboards need to transfer filters seamlessly, preserving filter context during navigation. In practice, you can adapt this code to your specific needs, including mapping dimensions when datasources differ or integrating it within dashboard or widget scripts and plugins. This can be used to enhance user experience by maintaining filter continuity, but it also can allow the implementation of advanced routing logic and customize dashboard interactions.  Whether you are transferring filters between a legacy dashboard and a new dashboard or building dynamic filter-preserving navigation for a custom applications, this solution provides a good starting code for many types of customizations.

    Jeremy Friedel
    Jeremy Friedel
    0
             
  • Blog banner
    • Widget & Dashboard ScriptsChevronRightIcon

    Redirect users to different dashboards based on dashboard filters

                                                                                                                             

    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 filter date range) includes an earlier date than the earliest date in the current dashboard's data source. 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 when the script should redirect, including non-date filters using similar scripts. Example Use Case Limited vs. Full Dataset : One dashboard data source might serve only recent data, while another dashboard data source stores all historical data. If a user selects a date that pre-dates one dataset, this script will seamlessly redirect them to the alternate dashboard that includes older data. Improved User Experience : Instead of displaying empty or invalid data for older dates, users are smoothly guided to the correct “all data” view dashboard. Full Script     /** * 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.  Example of a date selection including dates before the first date in dataset [A LT Text: A digital interface showing a table titled "Days in Date." Below the title, there are three dates listed: 11/26/20, 11/25/20, and 11/27/20. The date 11/26/20 is highlighted in yellow, along with a toggle switch on the right side of the image.] Example Output with logging variable enabled [ ALT Text: A screenshot of a computer terminal showing error messages related to date filters for a calendar dimension in a dataset. The messages indicate that a selected date of November 26, 2020, is valid while a date of November 25, 2020, is earlier than the earliest date in the dataset (November 11, 2020). The terminal displays a redirecting notice to an alternate dashboard.]  

    Jeremy Friedel
    Jeremy Friedel
    0
             
    • Add-ons & Plug-InsChevronRightIcon

    Plugin CustomMultiLevelFilter - Modify Filter Reset Behavior of Multi-level Dashboard Filters

                                                             

    This article discusses a  plugin  and equivalent dashboard script that override how Sisense default handles changes to multi-level dashboard filters. By default, Sisense automatically resets all lower filter levels to “Include All” whenever a higher-level filter is changed. This is intended to ensure that the combined effect of multiple levels always returns non-zero results. However, this behavior is not always preferred, especially if there are many levels in the filter and reapplying each level’s previous state is time-consuming. This plugin addresses that by preserving lower-level filter settings whenever a higher-level filter changes, reverting the Sisense-forced resets that occur automatically. Plugin Overview The CustomMultiLevelFilter plugin equivalent dashboard script intercepts Sisense’s forced “Include All” resets in multi-level member filters. It differentiates genuine user-initiated changes from automated resets. Once installed, the plugin listens for filter changes, identifies whether the changes are user-driven or automated reset, and reverts the automated filter changes ones.   The main javascript file in the plugin, main.6.js can be used unmodified as a dashboard script, the code can simply be copied as a standard dashboard script as an alternative to the plugin. Key Behaviors Detect & Revert Lower-Level Resets: When a higher-level filter is updated, Sisense sets lower filters to “Include All.” The plugin prevents this and restores the previously selected members or exclude settings if it identifies a forced reset scenario. Timing Threshold: A configurable time window allows the plugin to classify filter changes that arrive quickly (and change only to “Include All”) as Sisense-forced resets rather than genuine user actions. Temporary Plugin Pause for “Reset Filters”: If the user explicitly clicks the Reset Filters button, the plugin stops reverting changes for a short window, ensuring that a intended full filter reset to the default state. Below is the README file included with the plugin, which details installation steps, configuration, and known limitations.   # Custom Multi-Level Filter Revert Plugin ## Description This plugin detects and reverts Sisense’s automatic “forced resets” on multi-level filters to lower-level filters when a higher-level filter is changed, preserving only genuine, intentional user filter changes. When a higher-level filter is switched from membership to another membership or to **Include All**, Sisense may automatically reset lower-level filters to **Include All**. This plugin reverts those lower-level filters back to their previous member or exclude state. If the **Reset Filters** button is clicked, the plugin deactivates its revert logic for a short interval, allowing the reset to occur without plugin intervention. A timing threshold is provided to accommodate delayed Sisense queries (JAQL calls) that might trigger a secondary filter-change event. If a second event arrives within that threshold and sets only member filters to **Include All**, it is treated as an automatic forced reset rather than a user-driven change. ## Installation 1. **Download the plugin:** - Extract the compressed archive to `/opt/sisense/storage/plugins/` - Or, in **Admin > System Management > File Management**, upload the extracted folder to the `plugins` directory. 2. **Wait for the plugin to load.** 3. **Refresh the dashboard.** ## Configuration Two time-related variables, defined near the top of the main JavaScript file, influence this plugin’s behavior: 1. **autoResetThresholdMS** (initial default: 3000) Sets how long after the first filter change event a second event is considered a forced reset. Any filters changed from membership to **Include All** within this period are reverted. 2. **resetButtonIgnoreMS** (initial default: 500) Defines how long revert logic is ignored after the **Reset Filters** button is clicked. This ensures an intentional complete filter reset to the dashboard default filters is not reversed by the plugin. These values can be adjusted to accommodate different JAQL response times and user interaction patterns. If a dashboard has the dashboard object parameter `dashboard.disableFilterPlugin` set to true, the plugin will be disabled on this dashboard. This can be placed in the dashboard script. ## Limitations - The plugin only affects multi-level filters. Single-level filters are not altered. - The plugin specifically manages member-type multi-level filters and is not designed for other types of filters. - Reloading the dashboard clears stored filter states, causing any accumulated data of filter states stored by this plugin to be lost for the new session. - Adjusting `autoResetThresholdMs` involves a tradeoff. A higher value may risk treating rapid user actions as forced resets, while a lower value may allow some actual Sisense-forced resets to slip through. - Sisense can issue multiple query results in quick succession for multiple levels, causing filters to be attempted to be set to **Include All** several times within the threshold. The plugin will detect and revert these resets repeatedly, which is normal for multiple asynchronous JAQL calls in sequence. - The plugin does not block intentional user actions to set a filter to **Include All**. Only resets matching the time-based criteria are reverted. How the Code Works   Below is a brief walkthrough of the main JavaScript logic included in the plugin:   Plugin Initialization The code runs inside the prism.on("dashboardloaded", ...) event functions, ensuring it only applies once the Sisense dashboard is fully loaded. It sets up two timestamps—lastFilterChangeTimestamp and resetButtonClickedTimestamp—used to track recent filter changes and reset-button clicks.   Event Handlers initialized: Captures the initial state (instanceid + levels) of every multi-level filter. This provides a baseline for comparison whenever filters are changed. filterschanged: The core logic: If the Reset Filters button was clicked recently (within resetButtonIgnoreMs ms), the plugin skips the revert logic (letting the user’s reset stand). Otherwise, the plugin checks how long since the last filter change: If the change occured within autoResetThresholdMs, and all changed levels went resetting from a “member” selection to “Include All,” the plugin classifies it as a forced reset and reverts to the old state. If exactly one filter level changed, it is assumed to be a genuine user action, so no filter change is performed. If multiple levels are changed at the same instant, only the earliest changed level is kept, and any subsequent levels changed to “Include All” are reverted. Storing & Updating State After reverting (or confirming) changes, the code updates a global window.oldFilterStates object so that the next filterschanged event knows the final “current” filter state. If the dashboard is reloaded, this state is not preserved, and the plugin starts over with loaded filter state. This is identical to standard Sisense filter behavior. Usage of autoResetThresholdMs This threshold helps differentiate between genuine user actions and Sisense’s auto-resets. If Sisense triggers a forced reset a few seconds after the original user action (due to slow JAQL calls or large data sets), it still arrives within the threshold and gets reverted automatically. Working with the Reset Button The user may want to revert all filters back to the set default filter state. Clicking the Reset Filters button triggers a short ignore window (resetButtonIgnoreMs). During this period, the plugin allows any Sisense-forced resets to persist, ensuring the user’s explicit intention of a full reset is honored. Example Code Snippets   Detecting Multi-Level Filters // Filters that have multiple levels (item.levels) // are tracked by storing their instanceid and level states if (item.levels && item.levels.length) { window.oldFilterStates[i] = { instanceid: item.instanceid, levels: JSON.parse(JSON.stringify(item.levels)) }; } Classifying Filter States function getFilterType(filterObj) { if (!filterObj) return "none"; if (filterObj.all) { return "all"; } if ( (filterObj.members && filterObj.members.length > 0) || (filterObj.exclude && filterObj.exclude.members && filterObj.exclude.members.length > 0) ) { return "member"; } return "none"; } Reverting Forced Resets The updateDashboard function is discussed in detail in this community article.  if (timeSinceLastChange < autoResetThresholdMs) { // Check if all changed levels are member -> all if (allAreMemberToAll) { // Revert them changedLevelIndices.forEach(function (lvl) { newLevels[lvl].filter = JSON.parse(JSON.stringify(oldLevels[lvl].filter)); }); // Update Sisense args.dashboard.$dashboard.updateDashboard(args.dashboard, ["filters"]); args.dashboard.refresh(); } }   Using This Plugin as a Basis for Your Own This plugin combines custom JavaScript logic and Sisense’s plugin framework to alter default behavior. It can serve as a template for other Sisense plugins that need to: Listen for Sisense events such as initialized or filterschanged. Maintain custom state across events. Conditionally override or revert Sisense UI actions. Apply custom CSS or logic to the Sisense UI elements. With Sisense’s open plugin architecture, this approach can be adapted to create specialized behaviors for filters, widgets, or dashboards.     The CustomMultiLevelFilter plugin addresses a frequently requested feature: preserving lower-level filters when higher-level filters change. By leveraging Sisense’s event hooks and carefully distinguishing user actions from forced resets, it gives users greater control over multi-level filtering scenarios—especially in dashboards with deep hierarchies or many filter levels. Filter Behavior With Plugin  [ ALT text: A digital interface displaying product details, including fields for Brand (ABC), Category (GPS Devices), Quantity (1), Age Range (35-44), and Visit ID (45698). The text is highlighted in yellow against a white background.]     Filter Behavior Without Plugin [ ALT text: A user interface display showing a form with fields labeled "Brand," "Category," "Quantity," "Age Range," and "Visit ID." The brand is listed as "ABC," the category is "GPS Devices," the quantity is "1," the age range is "35-44," and the visit ID is "45698." Various fields are highlighted in yellow, and there is an edit icon visible in the corner.] How did the plugin work for you? What other type of plugin are you looking to learn more about? Let me know in the comments!

    Jeremy Friedel
    Jeremy Friedel
    0
             
    • BloxChevronRightIcon

    BloX: replicating action “send me the report now”

                                                                                     

    BloX: replicating action “send me the report now” This article explains how to develop an action to send a dashboard as a report to the end user. This action replicates the action “Send me the report now”. This action is available only to the dashboard’s owner, but we will develop a BloX action, which will be available for other users. To solve this challenge you will need to do the following: Create a widget of the type ‘BloX’ on the dashboard you want to have the ability to send reports to the end-users; Create a custom action with the following code:       const { widget } = payload; //Get widget’s object from the payload const internalHttp = prism.$injector.get('base.factories.internalHttp'); //Get internal factory to run API requests internalHttp({ url: '/api/v1/reporting', method: 'POST', contentType: 'application/json', data: JSON.stringify({ assetId: widget.dashboard.oid, assetType: "dashboard", recipients: [ { type: 'user', recipient: prism.user._id } ], preferences: { inline: true } }) }).then(res => console.log(res.data));     This action will have the following snippet:   { "type": "sendMeReport", "title": "Send me report" }   Use this snippet in the widget you have created:   { "style": "", "script": "", "title": "", "showCarousel": true, "body": [], "actions": [ { "type": "sendMeReport", "title": "Send me report" } ] }   Now, you have a button on the widget. After clicking this button, Sisense will send a report for the currently authenticated user. Please, note that there will be no indication of the running action. When the action is completed there will be a message in the browser’s console. Feel free to customize the logic of this action to show the end-user that the report is generating. Custom action is a powerful tool, which allows you to create custom interactions. Use this approach to create a new action easily. You can customize the proposed action by adding an indication of the running action or by using custom parameters to change format or size of the generated report (check the description of the endpoint /api/v1/reporting for additional information). Check out related content: Creating customer actions Reporting Send Reports

    Oleksii Demianyk
    Oleksii DemianykAdmin
    0
             
    • Add-ons & Plug-InsChevronRightIcon

    How to change the color of the JumpToDashboard icon in Sisense on Linux

                                                     

    How to change the color of the JumpToDashboard icon in Sisense on Linux This article provides a step-by-step guide on how to change the color of the JumpToDashboard (JTD) icon in the widget header toolbar within Sisense. This customization can enhance the visual integration of the dashboard with your organization's branding. Step-by-Step Guide Log in to Sisense as an administrator. Navigate to Admin > File Management . Locate the JTD Plugin under plugins > jumpToDashboard > styles . Modify the CSS File by opening style.css in a text editor. Add the following lines to change the icon color and position under the .jtd-title-img object:  background-color: #047e90; Save the changes. Navigate to Admin > Server & Hardware > Add-ons , disable the jumpToDashboard add-on and wait for the system to rebuild. Re-enable the jumpToDashboard add-on and wait for the system to rebuild again. Refresh your dashboard to ensure the changes have taken effect. The JTD icon should now display in the specified color and position. Conclusion By following these steps, you can successfully customize the color of the JumpToDashboard icon in Sisense. This allows for better alignment with your organization's design preferences and enhances the overall user interface. 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.

    Liliia Kislitsyna
    Liliia KislitsynaAdmin
    0
             
    • TroubleshootingChevronRightIcon

    Mastering custom actions in Sisense: debugging and understanding payload properties

                                                             

    Mastering custom actions in Sisense: debugging and understanding payload properties When you use a custom action from the community, develop your own, or try to understand why your action does not work as expected, sometimes you need to change the custom action’s logic. Maintaining someone’s code or developing your code is not easy when the code is executed in a 3rd party environment (Sisense). In this article, I will explain what properties are passed to the action and how to debug custom actions. When you need to understand what goes wrong, use a magic word debugger . When the browser’s developer tool is open, the code’s execution will be stopped on this code’s line. Let’s create a simple action with a single line of the code: debugger As the action’s snippet use the following one: {     "type" : "testAction" ,     "title" : "Test action" ,     "data" : {         "firstArgument" : "string" ,         "secondArgument" : "boolean"    } } Use this snippet as an action. As a result, you will see a new button on your widget. Open the developer tool of your browser and click on this button. The code’s execution was stopped on the line debugger and you will see the following code: (function anonymous(payload) {     debugger; })  If you hover over the variable payload , you will see its value. Using this argument, you can get access to other parts of the currently opened dashboard. For example, the dashboard’s filters are located in the following path: payload.widget.dashboard.filters Also, you can get other widgets from the dashboard, using the following construction: payload.widget.dashboard.widgets.get(widgetId) In this logic, the variable widgetId is the identifier of the widget you want to get from the current dashboard. Word debugger can be added to almost any line of your action, so you can debug your logic. If the code’s execution was not stopped in the  debugger , there can be the following reasons: Some issues occurred earlier in the custom action, so you need to place the  debugger earlier; The custom action was not called, because it does not exist. In this case, you will see the following message in the console: BloX Alert!  The action type was not found. Breakpoints are deactivated: [ALT text: Screenshot of a debugging interface showing a toolbar at the top with various icons. An arrow points to the "Deactivate breakpoints" icon, accompanied by a tooltip that indicates the keyboard shortcut (Command + F8) for the action. Below, there are sections labeled "Breakpoints," "Scope," and "Call Stack," with options and statuses displayed.]     Conclusion: A summary of key takeaways or final points. Using this approach you can easily develop your actions.   References/Related Content: Links and resources for further reading. https://docs.sisense.com/main/SisenseLinux/creating-custom-actions.htm https://sisense.dev/guides/customJs/jsApiRef/widgetClass https://sisense.dev/guides/customJs/jsApiRef/dashboardClass 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.  

    Oleksii Demianyk
    Oleksii DemianykAdmin
    0