Maintain Ranking Formula Accuracy with Widget Controller – QBeeQ

Overview

Pivot widgets that use ranking formulas (Rank, RankAsc, RankDesc, etc.) require a dimension to rank against. This dimension reference is stored in the formula's JAQL at design time. When a Widget Controller is used to let end users change the active dimension on a pivot, the formula's stored reference does not update automatically — it continues to point at the original design-time dimension, which may no longer be present on the widget. As a result, the ranking formula fails or returns no usable values.

This article describes a widget script that resolves this by updating the formula's dimension reference dynamically, immediately before each query is sent.

Use Cases

Apply this script when:

A pivot widget combines a ranking formula with a Widget Controller that changes the row or column dimension

A measure or widget-filter formula's logic depends on a dimension that a controller is permitted to change

Dashboard or widget behavior requires ranking output to remain accurate regardless of which dimension a user has selected

Prerequisites

Edit access to the widget script (Widget Options menu > Edit Script)

The panel index of the ranking formula's panel item, as sent in the widget's query

The panel index of the panel item controlled by the Widget Controller

Both indices are specific to each widget's panel configuration (rows, columns, values, and filters) and must be identified before implementation. See Identifying Panel Indices below.

Script

widget.on("beforequery", (e, args) => {
  // Locate the ranking formula's context object, which holds its dimension reference(s).
  // Replace 12 with the panel index of the formula panel item for this widget.
  const formulaContext = args.query.metadata[12].jaql.context;
  const contextKey = Object.keys(formulaContext)[0];
  const formulaReference = formulaContext[contextKey];

  // Update the reference to match the dimension currently driven by the Widget Controller.
  // Replace 0 with the panel index of the controller-driven panel item for this widget.
  const activeDimension = args.query.metadata[0].jaql;
  formulaReference.dim = activeDimension.dim;
  formulaReference.column = activeDimension.column;
  formulaReference.table = activeDimension.table;
});

How It Works

Step	Description
Trigger	`widget.on("beforequery", ...)` runs before every query the widget sends, including queries triggered by a controller selection change.
Locate the formula reference	Ranking formulas store their referenced field(s) in a `context` object within their JAQL, keyed by an internal placeholder ID. `args.query.metadata` is the array of panel items included in the outgoing query.
Update the reference	The script copies the `dim`, `column`, and `table` properties from the controller-driven panel item onto the formula's stored reference, so the ranking calculation evaluates against the currently selected dimension rather than the one set at design time.

Identifying Panel Indices

The indices used in args.query.metadata[12] and args.query.metadata[0] are not fixed values. They reflect the position of each panel item in the specific widget's query payload, which depends on that widget's panel configuration.

To determine the correct indices for a given widget:

Add console.log(args.query.metadata); inside the beforequery handler.

Open the widget and trigger a query (e.g., load the dashboard or change the controller selection).

Open the browser console and inspect the logged array to identify:

The index of the panel item containing the ranking formula

The index of the panel item driven by the Widget Controller

Replace the placeholder indices in the script with the values identified.

Remove the console.log line before deploying to production.

Limitations and Considerations

Panel indices must be re-verified if panel items are added, removed, or reordered on the widget.

This script modifies the query metadata directly; it does not alter the saved widget configuration.

Validate behavior after any Sisense version upgrade, as changes to internal JAQL or panel structures could affect this script.