Pivot and Table Widget Scripting for Replacing Values with Colored Arrows

[JeremyFriedel]

Pivot and Table Widget Scripting for Replacing Values with Colored Arrows

Sisense table and pivot widgets offer extensive customization options for data presentation, allowing developers to tailor widget content to specific needs, as discussed in detail in previous articles. While the Pivot 2.0 API includes a built-in transformPivot function for data transformation, Table widgets typically require direct DOM manipulation. This article provides a step-by-step guide for replacing numeric (or other) values in both widget types with arrow characters via scripting. It also demonstrates how to assign colors or apply conditional logic to these transformations. Examples include the use of Unicode arrow characters, though any other characters or combinations of characters can of course be utilized.

Sisense natively includes many different ways of customizing the design of table and pivot widgets, and include native functionality such as conditional coloring, and bar columns within cells, but scripting allows for even more customization to be possible.

Pivot 2.0 API Approach

The Pivot 2.0 API provides the transformPivot function, which enables dynamic modification of cell content before rendering. Below is an example script that replaces numeric values with arrow characters and colors the arrows using the cell’s style properties. The script evaluates each cell’s value and determines the appropriate arrow and color from a predefined mapping.

Script: Mapping Values to Arrows and Colors

widget.on('initialized', function (ev, se) {
    // Define the target column index
    var columnsIndex = 3; 

    // Define colors for different values
    var colorGreen = 'green';
    var colorRed = 'red';
    var colorOrange = 'orange';

    // Mapping of values to arrow characters and colors
    var values_to_modify = {
        1: { arrow: '▲', color: colorGreen },  // Up arrow for value 1
        2: { arrow: '▼', color: colorRed },    // Down arrow for value 2
        3: { arrow: '▶', color: colorOrange }, // Right arrow for value 3
        'null': { arrow: '▶', color: colorOrange }, // Default arrow for null values
        '': { arrow: '▶', color: colorOrange },     // Default arrow for empty strings
        default: { arrow: '▶', color: colorOrange } // Default for all other values
    };

    // Apply the transformation to each cell in the Pivot widget
    widget.transformPivot(
        { type: ['member', 'value'] }, // Specify cell types to transform
        function (metadata, cell) {
            // Only modify cells in the target column and ignore header rows
            if (metadata.colIndex === (columnsIndex - 1) && metadata.rowIndex !== 0) {
                var originalValue = cell.value;
                var originalValueStr = (originalValue === null) ? 'null' : String(originalValue);

                // Select the arrow and color configuration for the value
                var arrowConfig = values_to_modify[originalValueStr] || values_to_modify.default;

                // Update cell content and style
                cell.content = arrowConfig.arrow;
                cell.style.color = arrowConfig.color;
            }
        }
    );
});

Pivot Table Arrows

Script: Using Conditional Logic

For scenarios requiring dynamic evaluation (e.g., based on thresholds), conditional logic can replace dictionary-based mappings.

widget.on('initialized', function (ev, se) {
    // Define the target column and colors
    var columnsIndex = 3;
    var colorGreen = 'green';
    var colorRed = 'red';
    var colorOrange = 'orange';

    // Apply the transformation to each cell in the Pivot widget
    widget.transformPivot(
        { type: ['value', 'member'] },
        function (metadata, cell) {
            // Modify cells in the target column and skip header rows
            if (metadata.colIndex === (columnsIndex - 1) && metadata.rowIndex !== 0) {
                var originalValue = cell.value;

                // Handle non-numeric or null values
                var numericValue = (originalValue === null || originalValue === '') ? NaN : Number(originalValue);

                // Determine the arrow and color based on value thresholds
                var arrowChar, arrowColor;

                if (!isNaN(numericValue)) {
                    if (numericValue > 2) {
                        arrowChar = '▲'; // Up arrow for values greater than 2
                        arrowColor = colorGreen;
                    } else if (numericValue === 2) {
                        arrowChar = '▼'; // Down arrow for values equal to 2
                        arrowColor = colorRed;
                    } else {
                        arrowChar = '▶'; // Right arrow for values less than 2
                        arrowColor = colorOrange;
                    }
                } else {
                    arrowChar = '▶'; // Default arrow for non-numeric values
                    arrowColor = colorOrange;
                }

                // Update cell content and style
                cell.content = arrowChar;
                cell.style.color = arrowColor;
            }
        }
    );
});

Table Widget DOM Manipulation

Since Table widgets lack a transformPivot equivalent, DOM manipulation is required. Scripts typically operate on the domready event, allowing developers to modify rendered cells directly.

Script: Replacing Values via DOM Manipulation

widget.on('domready', function (se, ev) {
    // Define the target column and colors
    let columnsIndex = 3; 
    let colorGreen = 'green';
    let colorRed = 'red';
    let colorOrange = 'orange';

    // Mapping of values to arrow characters and colors
    let values_to_modify = {
        1: { arrow: '▲', color: colorGreen },  // Up arrow for value 1
        2: { arrow: '▼', color: colorRed },    // Down arrow for value 2
        3: { arrow: '▶', color: colorOrange }, // Right arrow for value 3
        'null': { arrow: '▶', color: colorOrange }, // Default arrow for null values
        '': { arrow: '▶', color: colorOrange },     // Default arrow for empty strings
        default: { arrow: '▶', color: colorOrange } // Default for all other values
    };

    // Locate the table element to observe
    const elementToObserve = $('table tbody', element)[0];

    // Function to update cell values based on mapping
    function updateCellValues() {
        $(elementToObserve).find('tr').each(function (index, row) {
            const cell = $(row).find('td:nth-child(' + columnsIndex + ')');
            const originalValue = cell.text().trim();

            // Avoid reapplying changes to already updated cells
            if (!cell.hasClass('updated')) {
                // Select the arrow and color configuration for the value
                let arrowConfig = values_to_modify[originalValue] || values_to_modify.default;

                // Update cell content and style
                cell.text(arrowConfig.arrow);
                cell.css('color', arrowConfig.color);
                cell.addClass('updated'); // Mark cell as updated
            }
        });
    }

    // Delay to ensure the DOM is fully rendered before making changes
    setTimeout(updateCellValues, 10);
});

Using Unicode Arrows

The arrow characters in these examples (▲, ▼, ▶) are standard glyphs that render consistently across browsers. Other arrows, such as ↗ or ←, can be substituted if different directions are preferred. Unicode arrows can be used (such as 🔺, 🔻, ⬇️, ⬆️) but they do not respond to font coloring.

Unicode Arrow Characters

Summary

Pivot 2.0 lets you change cell values before the widget renders using transformPivot, while Table widgets can be modified DOM manipulation using the domready event. Dictionary-based or conditional logic can be used to decide which arrow glyph and color to display, and Unicode characters can also be used.

This article is an additional example of the power of Sisense widget scripting in modifying the look and feel of Sisense widgets.