Dynamically adjusting column precision in Sisense widgets

This article details a script designed to dynamically set the number of decimal places (precision) for specific columns in a Sisense widget. The precision is determined based on the first value from a dedicated "precision" dimension in the widget. This script is ideal for use cases where precision needs to be dynamically adjusted based on filters, whether the user, default filters or data security set the filters. The precision dimension will respect all filters and datasecurity, determining the returned precision value.

A common example of this functionality is if a single Sisense server serves multiple customers, who share a common data source, but have data security rules to ensure only that customers' data is shown. If each customer has their precision value, the precision value returned will vary as expected between customers, allowing a single widget to serve multiple customers, and vary data as needed.

The precision panel does not have to be enabled and visible in the widget, the precision panel item can be disabled and the script will use a custom JAQL (the Sisense API used to fetch all data from a datasource) request to retrieve the precision value.

Overview of the Script

The script modifies the precision of specified columns in a widget by:

- Retrieving the precision value from a designated column (referred to as the "precision panel item").
- Applying the precision to the specified columns (referred to as "panel items to modify").

Full Script

// This script dynamically adjusts the number of decimal places (precision)
// for specified columns in a widget based on the first value retrieved
// from the precision column in the dataset. Only the first precision value
// is used; any subsequent values in the precision column are ignored.
// Precision settings are applied at the column level, not at the row level, in Sisense.

// Note: For aggregated table-type widgets, it is recommended to manually
// set a number of decimal places in the UI when creating a new panel.
// Creating a panel without initially specifying a precision may cause issues
// with the aggregated table plugin. This is also the best procedure for standard table widgets.

widget.on('beforequery', function (se, ev) {

    // Define the precision panel item title.
    const precisionPanelItemTitle = "Prec"; // Replace with your actual precision panel item title.

    // Define the titles of the panel items to modify.
    const panelItemTitlesToModify = ["Column1", "Column2"]; // Replace with your panel item titles.

    // Global flag to enable or disable console logs.
    const enableLogging = false;

    // Utility function for logging.
    function log(message) {
        if (enableLogging) {
            console.log(message);
        }
    }

    function modifyColumnPrecision(precisionPanelItemTitle, panelItemTitlesToModify) {

        const precisionPanelItem = widget.metadata.panels[0].items.find(item => item?.jaql?.title === precisionPanelItemTitle);

        if (!precisionPanelItem) {
            log(`Precision panel item with title "${precisionPanelItemTitle}" not found.`);
            return;
        }

        const isPrecisionDisabled = precisionPanelItem.disabled === true;

        const panelItemIndexesToModify = panelItemTitlesToModify.map(title => {
            const index = widget.metadata.panels[0].items.findIndex(item => item?.jaql?.title === title);
            if (index === -1) {
                log(`Panel item to modify with title "${title}" not found.`);
            }
            return index;
        }).filter(index => index !== -1 && index !== null && index !== undefined);

        const precisionPanelItemIndexInWidget = widget.metadata.panels[0].items.findIndex(item => item?.jaql?.title === precisionPanelItemTitle);

        const columnsToModify = panelItemIndexesToModify.filter(index => index !== precisionPanelItemIndexInWidget);

        const queryCopy = jQuery.extend(true, {}, ev.query);

        if (isPrecisionDisabled) {
            queryCopy.metadata = queryCopy.metadata.filter(metaItem => metaItem.jaql.title !== precisionPanelItemTitle);
            queryCopy.metadata.unshift({ jaql: precisionPanelItem.jaql });
        }

        const limitedQuery = { ...queryCopy, count: 1 };

        runHTTP(limitedQuery).then((response) => {
            if (!response || !response.data || !response.data.values || response.data.values.length === 0) {
                log('No data returned for precision panel item. Applying default precision of 4.');
                setDefaultPrecision(columnsToModify, 4);
                return;
            }

            log(`Response from JAQL query: ${response.status}`);

            let precisionColumnIndexInResponse = 0;
            if (!isPrecisionDisabled) {
                precisionColumnIndexInResponse = queryCopy.metadata.findIndex(metaItem => metaItem.jaql.title === precisionPanelItemTitle);
            }

            let precisionValue = parseInt(response.data.values[0][precisionColumnIndexInResponse].data, 10);

            if (isNaN(precisionValue) || precisionValue <= 0) {
                log(`Invalid or unusual precision value (${response.data.values[0][precisionColumnIndexInResponse].data}). Defaulting to 4.`);
                precisionValue = 4;
            }

            let columnsUpdated = false;

            columnsToModify.forEach((columnIndex) => {
                const panelItem = widget.metadata.panels[0].items[columnIndex];
                if (!panelItem.format) {
                    panelItem.format = {};
                }
                if (!panelItem.format.mask) {
                    panelItem.format.mask = {};
                }

                const currentPrecision = panelItem.format.mask.decimals;
                if (currentPrecision !== precisionValue) {
                    panelItem.format.mask.decimals = precisionValue;
                    log(`Panel item "${panelItem.jaql.title}" updated to ${precisionValue} decimal places.`);
                    columnsUpdated = true;
                } else {
                    log(`Panel item "${panelItem.jaql.title}" already has ${precisionValue} decimal places. No update needed.`);
                }
            });

            if (columnsUpdated) {
                log('At least one panel item updated. Refreshing the widget...');
                widget.refresh();
            } else {
                log('No changes required. Widget refresh avoided.');
            }
        }).catch((error) => {
            log('Error fetching precision value from JAQL API. Applying default precision of 4.');
            setDefaultPrecision(columnsToModify, 4);
        });

        function setDefaultPrecision(columns, defaultPrecision) {
            let columnsUpdated = false;

            columns.forEach((columnIndex) => {
                const panelItem = widget.metadata.panels[0].items[columnIndex];
                if (!panelItem.format) {
                    panelItem.format = {};
                }
                if (!panelItem.format.mask) {
                    panelItem.format.mask = {};
                }

                const currentPrecision = panelItem.format.mask.decimals;
                if (currentPrecision !== defaultPrecision) {
                    panelItem.format.mask.decimals = defaultPrecision;
                    log(`Panel item "${panelItem.jaql.title}" defaulted to ${defaultPrecision} decimal places.`);
                    columnsUpdated = true;
                } else {
                    log(`Panel item "${panelItem.jaql.title}" already has ${defaultPrecision} decimal places. No update needed.`);
                }
            });

            if (columnsUpdated) {
                log('Default precision applied. Refreshing the widget...');
                widget.refresh();
            } else {
                log('No changes required. Widget refresh avoided.');
            }
        }
    }

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

    modifyColumnPrecision(precisionPanelItemTitle, panelItemTitlesToModify);
});

How It Works

1. Precision Panel Item: The script locates the panel item corresponding to the precision column.
2. Fetching Precision Value: Executes a JAQL query to fetch the first precision value from the dataset.
3. Validating Precision: If the fetched precision is invalid, the script defaults to a precision of 4. This can of course be modified
4. Updating Columns: The script applies the validated precision value to the specified columns in the widget.
5. Refreshing the Widget: If updates are made, the widget is refreshed to apply the changes.

Use Cases

- Dynamic Precision Updates: Automatically adjusting precision based on data or user filter changes.
- Standardization: Ensuring consistent formatting across multiple columns in a widget.
- Improved Usability: Simplifying the user experience by automating precision adjustments.

By using this script, Sisense users can effectively manage precision in their widgets, enhancing data presentation and usability.

Precision Set To Two. Note How Datasecurity and Filters Determine the Precision Value Returned, and that the precision panel can be disabled

ALT Text: A table labeled "Precision" on the left side displays three columns: "Column2," "Prec," and "Column1." The rows contain numerical values, with "Prec" numbers ranging from 2 to 7. On the right side, there are input fields for filtering with "Precision," showing values "2" and "7" along with a toggle switch.

Precision Panel Disabled, but precision still set programmatically

ALT text: A table displaying two sections. The first section titled "Columns" features two column headers, "Column2" and "Prec," along with a checkbox. The second section titled "Precision" contains numerical data organized into two columns: "Column2" with values 7.44, 0.76, 4.63, 9.33, 8.15, and 6.34; and "Column1" with values 6.13, 1.40, 0.58, 4.42, 2.36, and 1.46.

Precision set to 5

ALT Text: A side-by-side image showing a table titled "Precision" on the left and a control panel on the right. The table contains two columns labeled "Column1" and "Column2" with three rows of numerical data. The control panel features two input fields with numeric values set at 5 and 7, likely for filtering or selecting data.

Precision set to 1

ALT Text: A user interface displaying a data table titled "Precision." The table has two columns labeled "Column1" and "Column2" with numerical values, including entries like 5.4 and 0.4 in the first row. On the right side, there are input fields for filtering the data, with conditions for values greater than or equal to 1 and 7.

Precision set to 3

ALT text: A data table titled "Precision" displaying two columns labeled "Column1" and "Column2." The table contains five rows of numerical data. A filter section on the right allows for inputting values for "Precision" with fields for numbers "≥ 3" and "≤ 7." There is a delete icon below the filter fields.

Jeremy Friedel

Posted 1 year ago

Team Lead, Software Engineering of FES SWE at Sisense

0 comments