Pivot and Table Widget Scripting for Replacing Values with Colored Arrows

 

 

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

ALT Text: Table displaying data for different territories in Europe. The columns include "Region," "TerritoryID," and "Territory_text." The "Territory_text" column features various symbols: yellow and red arrows, indicating different statuses or classifications, alongside green triangles for other entries.

 

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

 

ALT Text: A table with three columns labeled "Region," "TerritoryID," and "Territory_t." The regions listed include "USA" and "Europe," and the TerritoryID values range from 1 to 3. The last column features colored arrows: a green up arrow, a red down arrow, and a yellow side arrow, indicating different statuses for each entry.

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
ALT text: A table displaying data with three columns: 'Region,' 'TerritoryID,' and 'Territory_trend.' The rows list regions including USA and Europe, along with corresponding TerritoryIDs (1, 2, 3) and trend indicators (up, down, or stable) represented by arrows and a triangle.

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.