Value sorting in ad hoc report

Sorting determines the order in which elements appear in a report. Sorting organizes data by values, and it can preserve the hierarchy structure or display elements as a flat list. Because sorting changes the order of visible rows or columns, sorting directly affects how report data is displayed and analyzed.

When you interact with the data grid, only one sorting or ranking criterion can exist per axis.

When a new value sorting criterion is applied, any existing sorting or ranking criterion on the same axis is automatically removed.

Multiple sorting criteria can exist on the same axis when AI generates a report or when you opens a report from Application Studio. In such cases, the report result depends on the order of criteria in the report definition.

Value sorting applied to a column creates a sorting criterion on the row axis, using the selected column elements as its custom data area.

Value sorting applied to a row creates a sorting criterion on the column axis, using the selected row elements as its custom data area.

Dimensions that are not placed on the axis where the criterion is applied are resolved from the current report context. Changes to slicer configuration or slicer selections do not invalidate sorting criteria that are applied on axes. Changes to element selections on the same and opposite axis do not invalidate sorting that is applied to a value row or column.

Applying value sorting can either preserve the hierarchy structure or reorder elements independently of the hierarchy.

Value sorting applies to visible elements that are selected on the axis where the sorting criterion is defined.

Invalid sorting criteria

Sorting criteria are bound to a specific data context on report axes. Changes to the report definition can invalidate existing criteria. Changes to the report layout, for example moving or swapping dimensions between axes, can invalidate existing criteria. When a sorting criterion becomes invalid, it is automatically removed either after a report interaction change or upon confirming the Report Designer dialog.

Sorting modes

Without break hierarchy:

  • Ascending and descending preserve the existing hierarchy structure.
  • Sibling elements are sorted within each hierarchy level.
  • Parent-child relationships remain unchanged.
  • You can continue to expand and collapse hierarchy nodes.

With break hierarchy:

  • Break ascending and break descending ignore hierarchy-level modes.
  • The report displays sorted visible elements as a flat list.
  • Elements cannot be expanded to view descendants.

Sorting availability

You can apply sorting from contextual actions that are available on element headers in both the row axis and the column axis.

For multiple dimensions on the column or row axis, sorting is available only for element headers in the innermost dimension that reference a single-value column or row.

The Sort group in the Manage Report tab contains:
  • The Sort menu.
  • The Clear Sorting button.

Sorting is not available when:

  • You select multiple row-axis or column-axis element headers.
  • The active cell is an empty cell.
  • The active cell contains a value.

Sorting options

The Sort menu contains these options:

  • Ascending (Without Break Hierarchy)
  • Descending (Without Break Hierarchy)
  • Ascending (With Break Hierarchy)
  • Descending (With Break Hierarchy)
  • No Sorting

State indicator

The state indicator is applied in the Sort menu.

The currently applied sorting option is disabled in the drop-down menu. You can select another available option to apply a different sorting method or remove sorting from the report definition.

  • If the axis does not contain a sorting criterion, No Sorting is selected by default.
  • If the axis contains a sorting criterion:
    • When you select an element where sorting exists, the state indicator shows the applied sorting type. You can select a different sorting option.
    • When you select an element that is not part of the existing sorting context, No Sorting is selected. You can select a sorting option to replace the existing sorting criterion.

Spreadsheet and ribbon interaction

When you left-click an element header in the cell, the Manage Report tab shows the same contextual actions for the selected element header.

The ribbon contains a new Order section that provides ranking and sorting options.

The ribbon contains a Sort button.

Click Sort, to show the Sort menu.

Removing sorting criteria from the report definition

You can remove sorting criteria in the following ways:

  • Select an element where the criterion exists, and then select No Sorting from the contextual menu. This option applies when one criterion exists per axis.
  • Click Clear Sorting in the ribbon. When you select an axis that contains sorting criteria, the button becomes available. Clicking the button removes all sorting criteria from the axis.