Top/Bottom ranking and summary sorting

Rank the first row field

Native topBottom and portable report ranking select members of the first row field at root scope. Native configuration addresses the zero-based valueIndex; portable reports use a stable measureId, so repeated source fields remain unambiguous.

const ranking = {
  direction: 'top' as const, count: 1,
  measureId: 'total', otherLabel: 'Other',
};
// report.ranking = ranking;
// Native equivalent:
const topBottom = { direction: 'top' as const, count: 1, valueIndex: 0, otherLabel: 'Other' };

For A [10], B [20,20,20], C [5], D [15], Top 1 by SUM keeps B=60, and Other=30. Strict Other AVG is 10, because its three omitted facts are reaggregated. It is not an average or sum of the displayed groups. Grand strict AVG is 15 over all six facts. Without otherLabel, omitted members are omitted from the display. The executable example asserts the Other sum and average.

Calculated scores use the complete filtered peers before selection/Other. Display calculations then recompute on the selected members plus source-reaggregated Other. Thus a ranking score and the final displayed contextual value can differ. Support includes calculations/formulas requiring complete context; it is not bucket-local sorting of partially loaded aggregates.

An optional typed column: { kind, path } chooses a ranking score coordinate: leaf, subtotal, collapsed or grandTotal. Omission scores across columns. Paths must match active column depth/kind; this ranking coordinate is distinct from the summary-sort profile below. Use a noncolliding Other label, especially for the SQL reference, which rejects collisions with genuine authorized members. Original physical values and immutable fact keys retain drill lineage even when the ranked field is measured or appears on the opposite axis.

Summary sort scope

await controller.replaceQuery({
  sort: [{ selector: 'region', bySummary: 'average', desc: true }],
});

Here average is a configured stable measure ID, not a caption or an aggregate callback name. Each sibling prefix scores its own complete aggregate against the opposite grand total, before axis windows. An AVG prefix uses all its facts; DISTINCT never adds child distinct counts. Calculated/formula measures use their calculated result. WHERE and ranking establish the cube; HAVING selects output without redefining score denominators. Subtrees, subtotals and grand placement remain intact in ordered export.

Option	Type/default	Meaning and limitation
selector	Physical string/number or { kind: 'field', fieldId }; required	Must resolve to one active row/column axis field
bySummary	Optional stable measure result ID	Omission sorts members; unknown/ambiguous measures reject
desc	Boolean; false	Descending when true
Query sort omitted	Existing defaults	Retains grouped direction defaults
Query sort: []	Explicit empty list	Clears grouped direction defaults on both axes and uses source-order fallback
Remote summarySort	{ version: 1, oppositeAxis: 'grandTotal' } plus sort: true	Explicit opt-in for complete-prefix sorting

Score ties/unspecified levels follow local authorized source occurrence order or SQL authorized fact-key order. Align those orders when comparing engines. The SQL reference orders null scores first ascending/last descending and compares member strings by UTF-16 code units.

Selected opposite-axis tuple/path sorting is deferred. The native server action Sort rows by grand total targets the first physical row field and requires a row axis. Clicking a particular column header does not make that column tuple the scoring scope. Local native clicked-header sorting still follows Core and is separate from canonical local/headless query ordering.

Recovery and server admission

A server must advertise and implement this profile; a Boolean sort capability on an older engine retains its older contract. Unknown measures, invalid axis selectors and malformed opted profiles refuse before transport. Keep the existing committed view, choose an available measure/axis or remove bySummary, then retry. See runtime errors.

The SQLite analytical profile supports root row ranking and complete-prefix summary sort, including both axes, source-reaggregated Other and full export. Its flat baseline does not. Customer adapters must qualify these independently; a flag is not authorization or SQL execution. See adapters and security.