Grouping and Aggregates

Grouping, Sorting, and Aggregates

Grouping organises report rows by a shared value — for example, grouping orders by customer or sales by month. Each group can have a header and footer row for labels and subtotals.

Adding a group in the designer

1. Insert a Table item onto the report body (right-click → Insert → Table).
2. Bind the table to a dataset and map fields to the detail row cells.
3. Right-click the detail row handle on the left side of the table and choose Add Group Above or Add Group Below.
4. In the Grouping and Sorting Properties dialog:
   • Set the Group expression — e.g. =Fields!CategoryName.Value or {CategoryName}
   • Optionally set a Sort expression to control order within the group
5. Check Add group header and/or Add group footer as needed.
6. The designer inserts new header/footer rows bound to the group.

Group header and footer

The header row typically displays the group value; the footer row typically displays subtotals.

Header cell expression — show the grouping value:

={CategoryName}

Footer cell expression — subtotal for this group:

=Sum({LineTotal})

Footer cell expression — count of detail rows in this group:

=Count({OrderId})

Nested groups

Groups can be nested. Add a second group to the same table — for example, group by CategoryName (outer) then by ProductName (inner). Each level gets its own header and footer rows.

Sorting

Sorting is configured per-group in the Grouping and Sorting Properties dialog:

• Sort expression — the value to sort on, e.g. {OrderDate}
• Direction — Ascending or Descending

You can add multiple sort keys; they are applied in the order listed.

To sort detail rows within a group (not the groups themselves), right-click the detail row and choose Properties → Sorting.

Aggregate functions in expressions

Aggregate functions automatically scope to the innermost group containing the expression. To override the scope, pass the group or dataset name as the second argument.

' Subtotal for the current group (automatically scoped)
=Sum({Amount})

' Grand total across the whole dataset regardless of current group
=Sum({Amount}, "SalesDataSet")

' Percentage of group total vs grand total
=Sum({Amount}) / Sum({Amount}, "SalesDataSet")

' Row count for current group
=Count({OrderId})

' Count of distinct customers in this group
=CountDistinct({CustomerId})

' Average order value for this group
=Avg({OrderTotal})

' Highest and lowest values
=Max({UnitPrice})
=Min({UnitPrice})

Running totals

Use the RunningValue function to accumulate a value across rows:

' Running sum of Amount, resetting at each CategoryName group boundary
=RunningValue({Amount}, Sum, "CategoryGroup")

' Running count of rows across the entire dataset
=RunningValue({OrderId}, Count, "OrdersDataSet")

Grand totals in the table footer

Add a footer row to the outermost group (or the table itself) and use the dataset name as scope:

=Sum({Amount}, "OrdersDataSet")          ' grand total
=Count({OrderId}, "OrdersDataSet")       ' total row count
=Avg({OrderTotal}, "OrdersDataSet")      ' overall average

Filtering groups

To exclude groups based on a condition, set a Filter on the group in the Grouping Properties dialog. For example, to show only groups where the total exceeds 1000:

• Expression: =Sum({Amount})
• Operator: >
• Value: 1000

Example: Sales by category with subtotals

Cell	Expression
Category header	={CategoryName}
Product name (detail)	={ProductName}
Line total (detail)	=Format({LineTotal}, "C2")
Category subtotal (footer)	="Subtotal: " & Format(Sum({LineTotal}), "C2")
Category row count (footer)	=Count({OrderId}) & " orders"
Grand total (table footer)	="Grand total: " & Format(Sum({LineTotal}, "SalesDataSet"), "C2")

See also Expressions and Formulas for the full function reference.