Pivot widget-based anomaly/threshold warning [Linux]

Step-by-step Guide

1. Navigate to your preferred Pivot Table widget and open the script editing screen.

2. Next, enter the following script:

// Configuration: columns and their thresholds
const thresholdCols = [
  { idx: 0, threshold: 500, columnName: 'Cost' },
  { idx: 3, threshold: 100, columnName: 'Brand ID' },
];

// This will store all [rowIdx, colIdx] pairs to highlight
let warningCells = [];

function fetchAllDataAndShowWarnings(cubeName, jaqlQuery) {
  const $internalHttp = prism.$injector.has("base.factories.internalHttp") ?
  prism.$injector.get("base.factories.internalHttp") : null;

  $internalHttp({ 
	  url: `/api/datasources/${cubeName}/jaql`,
	  method: "POST",
	  data: jaqlQuery,
	  contentType: "application/json",
	  dataType: "json"
	}, true)
    .then(result => {
	// Initialize warning counts and cell positions
    let warningCounts = {};
    thresholdCols.forEach(colConfig => warningCounts[colConfig.idx] = 0);
    warningCells = []; // Reset

    if (result && result.values) {
      result.values.forEach((row, rowIdx) => {
        thresholdCols.forEach(colConfig => {
          const cell = row[colConfig.idx];
          const val = parseFloat(cell.data);
          if (!isNaN(val) && val > colConfig.threshold) {
            warningCounts[colConfig.idx]++;
            warningCells.push({ rowIdx, colIdx: colConfig.idx });
          }
        });
      });
    }

    // Build warning details
    const warningDetails = thresholdCols
      .filter(colConfig => warningCounts[colConfig.idx] > 0)
      .map(colConfig =>
        `${warningCounts[colConfig.idx]} value${warningCounts[colConfig.idx] > 1 ? "s" : ""} exceed ${colConfig.threshold} in "${colConfig.columnName}"`
      );

    showCentralWarningBanner(warningDetails, Object.values(warningCounts).reduce((a,b)=>a+b,0));
    highlightWarningCells();
    })
    .catch(err => console.error("HTTP error:", err));
}

function showCentralWarningBanner(warningDetails, totalWarnings) {
  let widgetElement = document.querySelector('[widgetid="' + widget.oid + '"]');
  if (widgetElement) {
    widgetElement.style.height = "auto";
    let widgetContainer = widgetElement.querySelector('.widget-body');
    if (widgetContainer) {
      let existingBanner = widgetContainer.querySelector('.pivot-warning-banner');
      if (existingBanner) existingBanner.remove();

      if (totalWarnings > 0) {
        const banner = document.createElement('div');
        banner.className = 'pivot-warning-banner';
        banner.style.display = 'flex';
        banner.style.justifyContent = 'center';
        banner.style.alignItems = 'center';
        banner.style.background = '#fff3cd';
        banner.style.color = '#856404';
        banner.style.padding = '8px 12px';
        banner.style.border = '1px solid #ffeeba';
        banner.style.borderRadius = '5px';
        banner.style.marginBottom = '8px';
        banner.style.fontSize = '15px';
        banner.style.width = '100%';
        banner.style.position = 'relative';

        banner.innerHTML = `
          <span style="font-size:20px; font-weight:bold; margin-right:6px;cursor:pointer;" class="warning-detail-icon" title="Show details">⚠ ${totalWarnings} Warning${totalWarnings === 1 ? "" : "s"}</span>
        `;

        // Tooltip
        const tooltip = document.createElement('div');
        tooltip.className = 'pivot-warning-tooltip';
        tooltip.style.display = 'none';
        tooltip.style.position = 'absolute';
        tooltip.style.left = '120px';
        tooltip.style.top = '36px';
        tooltip.style.background = '#ffffe0';
        tooltip.style.color = '#856404';
        tooltip.style.border = '1px solid #ffeeba';
        tooltip.style.borderRadius = '5px';
        tooltip.style.padding = '8px 14px';
        tooltip.style.fontSize = '14px';
        tooltip.style.zIndex = 9999;
        tooltip.style.maxWidth = '320px';
        tooltip.style.boxShadow = '0 4px 8px rgba(0,0,0,0.06)';
        tooltip.innerHTML = warningDetails.length > 0 ? warningDetails.join('<br>') : 'No threshold exceeded.';

        banner.appendChild(tooltip);
        banner.querySelector('.warning-detail-icon').addEventListener('mouseenter', () => {
          tooltip.style.display = 'block';
        });
        banner.querySelector('.warning-detail-icon').addEventListener('mouseleave', () => {
          tooltip.style.display = 'none';
        });
        banner.addEventListener('mouseleave', () => {
          tooltip.style.display = 'none';
        });

        widgetContainer.insertBefore(banner, widgetContainer.firstChild);
      }
    }
  }
}

// Highlight warning cells in the visible pivot table
function highlightWarningCells() {
  let widgetElement = document.querySelector('[widgetid="' + widget.oid + '"]');
  if (!widgetElement) return;

  // For each threshold column
  thresholdCols.forEach(colConfig => {
    // Select all cells with the correct col index
    const selector = `.table-grid__cell--col-${colConfig.idx}`;
    let cells = widgetElement.querySelectorAll(selector);

    cells.forEach(cellElem => {
      // Get the cell text and parse as float
      const val = parseFloat(cellElem.innerText.replace(/[^\d.-]/g,""));
      if (!isNaN(val) && val > colConfig.threshold) {
        cellElem.style.background = 'LightSalmon';
        cellElem.style.fontWeight = 'bold';
      } else {
        cellElem.style.background = '';
        cellElem.style.fontWeight = '';
      }
    });
  });
}

// Run your API-driven warning calc when widget is ready
widget.on("ready", () => {
  const cubeName = widget.datasource.title;
  const jaqlQueryObj = prism.debugging.GetJaql(widget);
  fetchAllDataAndShowWarnings(cubeName, jaqlQueryObj);
});

3. In your widget, you will see a display similar to the following:

4. At the top of your pivot table, you'll find a notification showing how many values exceed the thresholds you've set for each column. By hovering over the notification, you can view the exact number of values for each column. These values are also highlighted within your table for easy reference.

References/Related Content

The REST API reference guide can be accessed here:

https://developer.sisense.com/guides/restApi/v0/?platform=linux&spec=L2025.2#/query-rest-controller/executeQueryStream

Token-related documentation:

https://docs.sisense.com/main/SisenseLinux/sisense-bearer-tokens.htm

Conclusion

This script provides a straightforward method to monitor and highlight data threshold violations in your Sisense Pivot Table widgets. By automatically generating warnings and visually marking affected cells, it enhances data visibility and helps users quickly identify outliers. We recommend thoroughly testing this solution within your environment to ensure compatibility with your Sisense instance and data models.

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.