Using Aggregate Functions

To use the aggregate functions, add a calculated field to a section header or footer section, and assign the expression to the field's Text property.

For example, the "Employee Sales by Country" report in the sample NWind.xml file contains several aggregate fields. The report groups records by Country and by Employee.

The SalespersonTotal field in the footer section of the Employee group contains the following expression:

Sum([SaleAmount])

Because the field is in the Employee group footer, the expression returns the total sales for the current employee.

The CountryTotal and GrandTotal fields contain exactly the same expression. However, because these fields are in the Country group footer and report footer, the expression returns the total sales for the current country and for the entire recordset.

You may need to refer to a higher-level aggregate from within a group. For example, in the "Employee Sales by Country" report, there is a field that shows sales in the current country as a percentage of the grand total. Since all aggregates calculated within a country group refer to the current country, the report cannot calculate this directly. Instead, the PercentOfGrandTotal field uses the following expression:

[CountryTotal]/[GrandTotal]

CountryTotal and GrandTotal are fields located in the Country and report footer sections. Therefore, CountryTotal holds the total for the current country and GrandTotal holds the total for the whole recordset.

It is important to realize that evaluating aggregate functions is time-consuming, since it requires the control to traverse the recordset. Because of this, you should try to use aggregate functions in a few calculated fields only. Other fields can then read the aggregate value directly from these fields, rather than evaluating the aggregate expression again.

For example, the "Employee Sales by Country" report in the NorthWind database has a detail field that shows each sale as a percentage of the country's total sales. This field contains the following expression:

[SaleAmount]/[CountryTotal]

SaleAmount is a reference to a recordset field that varies for each detail record. CountryTotal is a reference to a report field that contains an aggregate function. When the control evaluates this expression, it gets the aggregate value directly from the report field, and does not recalculate the aggregate.