Aggregate functions calculate values across multiple rows of data within a report band — totals, counts, averages, and more. Unlike script functions, aggregate functions are used directly inside text object expressions enclosed in square brackets, and are evaluated automatically by the report engine as it processes each record.
Aggregate functions are typically placed in GroupFooter, MasterFooter, or ReportSummary bands to display subtotals and grand totals.
Common Syntax
[FUNCTION(Expression [, BandName [, Flags]])]| Parameter | Type | Description |
|---|---|---|
| Expression | Expression | The field or expression to aggregate, e.g. [Orders.Total] |
| BandName | String | Optional. The name of the data band to aggregate over. Leave blank to use the current group. |
| Flags | Integer | Optional. Controls reset and running total behaviour — see the flag table below. |
| Flag Value | Behaviour |
|---|---|
| 0 | Default — resets at each group break |
| 1 | Resets at each new page |
| 2 | Running total — does not reset at group breaks |
| 3 | Running total that also resets on each new page |
AVG
Returns the average (arithmetic mean) of all non-null values of an expression across the rows in the current group or band.
[AVG(Expression)]// Average order value in a group footer
[AVG([Orders.Total])]
// Average with currency formatting
[FormatCurrencySafe(AVG([Orders.Total]))]
// Average unit price across all line items
[FormatCurrencySafe(AVG([LineItems.UnitPrice]))]
// Average days to ship across all orders
[FormatNumberSafe(AVG([Orders.DaysToShip]), 1)] days
COUNT
Returns the number of non-null values of an expression across the rows in the current group or band. Use this to count records, line items, or any other repeating data.
[COUNT(Expression)]// Count orders in a customer group footer
[COUNT([Orders.OrderID])]
// Count line items on an invoice
[COUNT([LineItems.LineID])]
// Build a label
[IntToStr(COUNT([Orders.OrderID])) + ' orders']
// Count with pluralisation
[Pluralize(COUNT([Orders.OrderID]), 'order', 'orders')]
FIRST
Returns the first value encountered for an expression within the current group or band. Useful for displaying the opening value of a sequence, such as the first date or first transaction in a group.
[FIRST(Expression)]// First order date in a customer group
[FormatDateSafe(FIRST([Orders.OrderDate]), 'mm/dd/yyyy')]
// First transaction reference in a period group
[FIRST([Journal.Reference])]
// Show the opening balance at the start of a group
[FormatCurrencySafe(FIRST([Account.Balance]))]
LAST
Returns the last value encountered for an expression within the current group or band. Useful for displaying the closing value of a sequence, such as the most recent date or final balance.
[LAST(Expression)]// Most recent order date in a customer group
[FormatDateSafe(LAST([Orders.OrderDate]), 'mm/dd/yyyy')]
// Closing balance at the end of a period
[FormatCurrencySafe(LAST([Account.Balance]))]
// Last transaction reference in a group
[LAST([Journal.Reference])]
MAX
Returns the maximum (highest) value of an expression across all rows in the current group or band.
[MAX(Expression)]// Largest order in a customer group
[FormatCurrencySafe(MAX([Orders.Total]))]
// Most recent date in a group
[FormatDateSafe(MAX([Orders.OrderDate]), 'mm/dd/yyyy')]
// Highest quantity ordered across all line items
[MAX([LineItems.Quantity])]
// Highest invoice amount in the report
[FormatCurrencySafe(MAX([Invoice.Amount]))]
MIN
Returns the minimum (lowest) value of an expression across all rows in the current group or band.
[MIN(Expression)]// Smallest order in a customer group
[FormatCurrencySafe(MIN([Orders.Total]))]
// Earliest date in a group
[FormatDateSafe(MIN([Orders.OrderDate]), 'mm/dd/yyyy')]
// Lowest unit price across all line items
[FormatCurrencySafe(MIN([LineItems.UnitPrice]))]
// Minimum stock level across all items
[MIN([Item.StockOnHand])]
SUM
Returns the sum of all non-null values of an expression across the rows in the current group or band. This is the most commonly used aggregate function.
[SUM(Expression)]// Total all orders in a group footer
[FormatCurrencySafe(SUM([Orders.Total]))]
// Total quantity across all line items
[SUM([LineItems.Quantity])]
// Grand total in a report summary band
[FormatCurrencySafe(SUM([Orders.Total]))]
// Running total that carries across groups (flag = 2)
[FormatCurrencySafe(SUM([Orders.Total],,2))]
// Running total that resets on each new page (flag = 1)
[FormatCurrencySafe(SUM([Orders.Total],,1))]
// Sum of a calculated value
[FormatCurrencySafe(SUM([LineItems.Quantity] * [LineItems.UnitPrice]))]
Aggregate Examples in Context
The following shows how aggregate functions are typically combined in a report with groups.
// In a GroupHeader band — show the group label
[Customer.CompanyName]
// In a MasterData band — show each row
[Orders.OrderDate] [Orders.Reference] [FormatCurrencySafe([Orders.Total])]
// In a GroupFooter band — show group totals
Orders: [COUNT([Orders.OrderID])]
Total: [FormatCurrencySafe(SUM([Orders.Total]))]
Avg: [FormatCurrencySafe(AVG([Orders.Total]))]
// In a ReportSummary band — show grand totals
Total Orders: [COUNT([Orders.OrderID])]
Grand Total: [FormatCurrencySafe(SUM([Orders.Total]))]
See also: Math Functions | Formatting Functions | Conversion Functions