Advanced Pivot Widget Scripting - Combining Custom JAQL and the Pivot 2.0 API

While the Pivot Table Widget Type is a highly customizable and flexible Sisense widget for representing data in tabular form, certain use cases may be best achieved through custom code and scripting. The Pivot 2.0 JavaScript API facilitates the modification of existing pivot table cells, including updating cell values and adding data to cells not present in the initial results.

This article reviews a use case where pivot table values are replaced with specific string text, substituting non-descriptive integers (in this case, the integer "1").

Although adding an additional row dimension could accomplish this visualization, it was not desired in this case due to the existing extensive nesting of three-row dimensions and columns. Adding a fourth row would significantly increase blank spaces in the table, reducing information density and readability.

The replacement value will be retrieved from the data source via a second JAQL API request, as the dimension data replacement values are not present in the widget's initial raw results. JAQL is the query language used by all native Sisense widgets to fetch data from data sources.

While the JAQL request and code to find the correct value for each cell are specific to this data source, table, and use case, the principles and methods—combining custom JAQL requests and using the Pivot 2.0 JavaScript API—are versatile techniques applicable to many other use cases. This particular widget script can serve as a general example. Other use cases might be simpler but will employ the same techniques.

Each half of this solution can be used independently as examples, depending on the specific use case: one half being a secondary JAQL query based on the initial query, and the other matching the cell value to specific raw result values, regardless of their origin.

To better illustrate the specific use case, which aids in understanding the code and the widget table from which this request originated, this example involves three rows in the pivot widget, one column, and one value. The relationships between the rows were not 1:1. For example, a "Division" contains multiple "Assets," each with multiple dates and descriptions. Each "Date" has multiple "Description" and "Value" pairs, with each "Description" corresponding to only one "Value."

Screen Shot 2024-03-10 at 10.04.01 PM.png

Each "Date" would have multiple "Description" and "Value" pairs. Somewhat unusually these "Descriptions" and "Values" are always 1:1 in the datasource for a specific date (and other rows), no"Description" would correspond to more than one "Value" and the "# of unique Values" would always be "1" in the table.

In this pivot table, "Description" is a column, not a row. Below is a screenshot showing some of these "Description" and "Value" pairs, in a different table widget. The "Values" can be both non-numeric strings and integers in text form.

Screen Shot 2024-03-10 at 10.03.36 PM.png

Before any code is applied via widget script a column in the pivot table widget in question appears as below, where any value if present would be "1", as there is one unique value present.

Screen Shot 2024-03-10 at 10.00.59 PM.png

The script retrieves the corresponding values from the data source via JAQL and replaces the "1" with the appropriate value. In this example, the values are temperatures, not counts of unique values, as shown below.

Screen Shot 2024-03-10 at 10.00.22 PM.png

Each replacement involves finding the matching value for a given description of the current value. There will always be only one matching value for each description (the description being the columns in this table). Each row will contain multiple descriptions, and each row (which is a date) will not contain all descriptions, so some columns will be blank.

The dimensions used for this query are very similar to the current widget metadata dimensions, so these will be copied and used as a template. The "beforequery" event is used to run this additional query just before the main JAQL query of the widget.

// Replace "# of Unique Values" with matching cell contents using custom JAQL call

// Script runs before JAQL query sent to fetch data for widget
widget.on('beforequery', function (_, widgetObject) {

    // Copy of current widget JAQL for modification for custom JAQL call
    let customJAQL = [...widgetObject.query.metadata];

The initial setup involves copying the current widget JAQL for modification for the custom JAQL call. Dimensions and panels not relevant to the request are removed and replaced with relevant ones.

Any relevant filters, whether custom and defined in the script, or based on existing widget filters or dashboard filters present may also be added to filter the query values returned from the secondary JAQL request.

The current metadata panels of a widget can be logged for assistance in finding the relevant dimensions with the console command:

prism.activeWidget.metadata.panels

Screen Shot 2024-05-23 at 7.48.10 PM.png

The full path to finding a relevant dimension name is:

prism.activeWidget.metadata.panels[panelIndex].items[itemIndex].jaql.dim

Below is one example of replacing and modifying the JAQL payload.

    // Replace JAQL of existing widget for values needed to find matching string contents to replace "# of Unique Values" cells
    // Modify as needed for future use to match names of dimensions as needed
    customJAQL[3] = {
        "jaql": {
            "dim": "[Brand.Brand]"
        }
    }

    customJAQL[4] = {
        "jaql": {
            "dim": "[Category.Category]"
        }
    }

Now that the modified JAQL query payload is ready, the API request can now be made, via the standard JAQL API endpoint.

    // Full query object copy
    let customQuery = { ...widgetObject.query }

    // Set query metadata to modified metadata
    customQuery.metadata = customJAQL;

    // Function to make a custom JAQL Request
    function jaqlAPI(jaql) {
        // Use $internalHttp service if exists
        const $internalHttp = prism.$injector.has("base.factories.internalHttp") ?
            prism.$injector.get("base.factories.internalHttp") : null;
        // Ajax configurations
        const ajaxConfig = {
            url: "/api/datasources/" + encodeURIComponent(jaql.datasource.title) + "/jaql",
            method: "POST",
            data: JSON.stringify(jaql),
            contentType: "application/json",
            dataType: "json",
            async: false
        };
        // Use $internalHttp service
        // else use default ajax request
        const httpPromise = $internalHttp ? $internalHttp(ajaxConfig, true) : $.ajax(ajaxConfig);

        // Return response
        return httpPromise.responseJSON;
    };

    // Make custom JAQL call to return replacement string values
    let response = jaqlAPI(customQuery);

The Sisense internalHTTP library is used to make this request, as shown in the examples in this article.

This approach can help avoid many request header-related issues when making secondary JAQL requests in a widget or dashboard script.

Using the existing widget query and adapting it, instead of creating an entirely new query from scratch, can be a useful technique in various other use cases, this is primarily for convenience and is identical to defining the equivalent JAQL payload in full within the script.

The relevant values are now returned from the data source via the JAQL response and can be used to replace the values in the pivot cells with the correct values.

Next, a function is defined to match the individual cells with the corresponding replacement value.

   // Search for matching value from custom JAQL request with string replacement value for "# of Unique Values" cells
    function findMatchFromResponse(jaqlResponseValues, cellColumns) {

        // For date comparisons
        const parseDate = (dateString) => new Date(dateString).toISOString();

        // Loop through JAQL response from custom call to find matching values for current cell
        for (let row of jaqlResponseValues) {

            // Set to false whenever any column does not match current cell
            let foundMatch = true;

            // Check columns ignores column after index 3, column 4 contains string match if previous three match
            for (let i = 0; i < 4; i++) {
                // For date columns convert to date ISO strings and compare
                if (cellColumns[i].name.includes("(Calendar)")) {
                    if (parseDate(row[i].data) !== parseDate(cellColumns[i].member)) {
                        // If all columns do not match go to next row in response
                        foundMatch = false;
                        break;
                    }
                }
                else {
                    // If not date string compare values directly
                    if (row[i].data !== cellColumns[i].member) {
                        // If all columns do not match go to next row in response
                        foundMatch = false;
                        break;
                    }
                }
            }

            // If all columns before column index 4 all match, current row is a match and column index 4 contains the corresponding string contents
            if (foundMatch) {
                // Return contents to replace "1" in cell, stop search as match found
                return row[4].data; // Return the value of the fourth item in the matching row
            }
        }

        // Return null if no match is found in custom JAQL response
        return null;
    }

This function can now be used to match rows between the primary and secondary JAQL responses, which have different dimensions but share some row dimensions. The second function parameter corresponds with the specific format the Pivot 2.0 API uses to return values about the current column of the cell, with some modifications.

To handle data dimensions, the function converts dates to Javascript programmatic date-type objects. This ensures that date formatting differences do not prevent finding an identical match. Other values are compared directly without conversion.

This general logic flow can be adapted to find matching rows between any two JAQL requests with overlapping dimensions. Depending on the exact use case, slight modifications may be necessary. Similar code can be used not only for other pivot table use cases but also, for example, to modify tooltips by adding additional dimensions not present in the widget, for widget types that include tooltips, such as bar and column chart widgets. This particular function uses hard-coded column indexes, which can be changed to adaptive variables or different indexes as needed.

Using the Pivot 2.0 API, the cell contents can now be replaced with the previously defined findMatchFromResponse function and the secondary JAQL responses. The Pivot 2.0 API provides the current cell's metadata (such as the exact name of the column), and position in the table (including row and column indexes), and allows overriding the existing value with a new value.

The rowIndex: ['member'] ensures that only value cells are affected; no header or direct row value cells are impacted, so no additional conditions are needed to check if the current cell is a value cell.

The cell metadata array, which contains information about the cell's row position, is modified by adding the cell's column to the same object. This provides a single array containing all the information needed to use the findMatchFromResponse function to find the matching replacement value with the already retrieved new values.

    // Use Pivot 2.0 API to replace cell contents using findMatchFromResponse function to find values
    // Runs when table renders, but can be set in any widget event before
    widget.transformPivot({ rowIndex: ['member'] },

        function (metadata, cell) {

            // All "# of Unique Values" are aimed to be "1" in current dataset and table
            if (!isNaN(cell.value)) {

                // Copy of current cell values, contains cell position in pivot except "Column" panel
                let metadataRows = [...metadata.rows]

                if (metadata.columns && metadata.columns.length > 0) {
                    // Add current cell "Column" panel position 
                    metadataRows.push({ "name": "", "member": metadata.columns[0].member })

                    // Matching contents from custom JAQL for current cell position
                    let newCellContent = findMatchFromResponse(response.values, metadataRows);

                    // If match found
                    if (newCellContent != null && newCellContent.length != 0) {
                        // Replace "1" with matching string value from custom JAQL call
                        cell.content = newCellContent;
                    }
                    else {
                        cell.content = " ";
                    }

                }
            }

        })

This type of functionality can be used independently of this particular instance. For example, the code could be adapted to use a constant dictionary included in the script instead of a secondary JAQL request.

Cell styling with CSS style rules can also be added simultaneously with the cell content replacement.

Here is the full script:

// Replace "# of Unique Values" with matching cell contents using custom JAQL call

// Script runs before JAQL query sent to fetch data for widget
widget.on('beforequery', function (_, widgetObject) {

    // Copy of current widget JAQL for modification for custom JAQL call
    let customJAQL = [...widgetObject.query.metadata];

    // Replace JAQL of existing widget for values needed to find matching string contents to replace "# of Unique Values" cells
    // Modify as needed for future use to match names of dimensions as needed
    customJAQL[3] = {
        "jaql": {
            "dim": "[Brand.Brand]"
        }
    }

    customJAQL[4] = {
        "jaql": {
            "dim": "[Category.Category]"
        }
    }

    // Full query object copy
    let customQuery = { ...widgetObject.query }

    // Set query metadata to modified metadata
    customQuery.metadata = customJAQL;

    // Function to make a custom JAQL Request
    function jaqlAPI(jaql) {
        // Use $internalHttp service if exists
        const $internalHttp = prism.$injector.has("base.factories.internalHttp") ?
            prism.$injector.get("base.factories.internalHttp") : null;
        // Ajax configurations
        const ajaxConfig = {
            url: "/api/datasources/" + encodeURIComponent(jaql.datasource.title) + "/jaql",
            method: "POST",
            data: JSON.stringify(jaql),
            contentType: "application/json",
            dataType: "json",
            async: false
        };
        // Use $internalHttp service
        // else use default ajax request
        const httpPromise = $internalHttp ? $internalHttp(ajaxConfig, true) : $.ajax(ajaxConfig);

        // Return response
        return httpPromise.responseJSON;
    };

    // Make custom JAQL call to return replacement string values
    let response = jaqlAPI(customQuery);

    // Search for matching value from custom JAQL request with string replacement value for "# of Unique Values" cells
    function findMatchFromResponse(jaqlResponseValues, cellColumns) {

        // For date comparisons
        const parseDate = (dateString) => new Date(dateString).toISOString();

        // Loop through JAQL response from custom call to find matching values for current cell
        for (let row of jaqlResponseValues) {

            // Set to false whenever any column does not match current cell
            let foundMatch = true;

            // Check columns ignores column after index 3, column 4 contains string match if previous three match
            for (let i = 0; i < 4; i++) {
                // For date columns convert to date ISO strings and compare
                if (cellColumns[i].name.includes("(Calendar)")) {
                    if (parseDate(row[i].data) !== parseDate(cellColumns[i].member)) {
                        // If all columns do not match go to next row in response
                        foundMatch = false;
                        break;
                    }
                }
                else {
                    // If not date string compare values directly
                    if (row[i].data !== cellColumns[i].member) {
                        // If all columns do not match go to next row in response
                        foundMatch = false;
                        break;
                    }
                }
            }

            // If all columns before column index 4 all match, current row is a match and column index 4 contains the corresponding string contents
            if (foundMatch) {
                // Return contents to replace "1" in cell, stop search as match found
                return row[4].data; // Return the value of the fourth item in the matching row
            }
        }

        // Return null if no match is found in custom JAQL response
        return null;
    }

    // Use Pivot 2.0 API to replace cell contents using findMatchFromResponse function to find values
    // Runs when table renders, but can be set in any widget event before
    widget.transformPivot({ rowIndex: ['member'] },

        function (metadata, cell) {

            // All "# of Unique Values" are aimed to be "1" in current dataset and table
            if (!isNaN(cell.value)) {

                // Copy of current cell values, contains cell position in pivot except "Column" panel
                let metadataRows = [...metadata.rows]

                if (metadata.columns && metadata.columns.length > 0) {
                    // Add current cell "Column" panel position 
                    metadataRows.push({ "name": "", "member": metadata.columns[0].member })

                    // Matching contents from custom JAQL for current cell position
                    let newCellContent = findMatchFromResponse(response.values, metadataRows);

                    // If match found
                    if (newCellContent != null && newCellContent.length != 0) {
                        // Replace "1" with matching string value from custom JAQL call
                        cell.content = newCellContent;
                    }
                    else {
                        cell.content = " ";
                    }

                }
            }

        })

})

When the script is applied to the widget, the values in the pivot table that were previously indistinguishable "1"s (for "# of unique values") are now replaced with the relevant values for each column, matching the corresponding description-value pair. This achieves the desired functionality, and the pivot table now displays the data as intended.

This example is likely more complex than the typical use case, but it provides a comprehensive widget script example. Multiple parts of this script can be used in isolation to suit specific needs.

The Pivot 2.0 JavaScript API is a powerful tool for creating highly customized data visualizations. By leveraging its capabilities, developers can customize pivot tables, enhancing the utility and readability of the data visualization. Whether for simple or complex use cases, the flexibility offered by the Pivot 2.0 Javascript API enables a wide range of customizations to meet various data visualization requirements.

Advanced Pivot Widget Scripting - Combining Custom JAQL and the Pivot 2.0 API

Share your experience in the comments!