Using Aggregate Functions

ComponentOne Reports for WinForms Designer Edition: ComponentOne Reports for WinForms > Working with C1Report > Working with VBScript > Using Aggregate Functions

Aggregate functions are used to summarize data over the group being rendered. When used in a report header field, these expressions return aggregates over the entire dataset. When used in group headers or footers, they return the aggregate for the group.

All C1Report aggregate functions take two arguments:

• A string containing a VBScript expression to be aggregated over the group.

• An optional string containing a VBScript expression used as a filter (domain aggregate). The filter expression is evaluated before each value is aggregated. If the filter returns False, the value is skipped and is not included in the aggregate result.

C1Report defines the following aggregate functions:

Function	Description
Avg	Average value of the expression within the current group. For example, the following expressions calculate the average sales for the whole group and the average sales for a certain type of product: Avg(SalesAmount) Avg(SalesAmount, ProductType = 3)
Sum	Sum of all values in the group.
Count	Count of records in the group with non-null values. Use an asterisk for the expression to include all records. For example, the following expressions count the number of employees with valid (non-null) addresses and the total number of employees: Count(Employees.Address) Count(*)
CountDistinct	Count of records in the group with distinct non-null values.
Min, Max	Minimum and maximum values for the expression. For example: "Min Sale = " & Max(SaleAmount)
Range	Range between minimum and maximum values for the expression.
StDev, Var	Standard deviation and variance of the expression in the current group. The values are calculated using the sample (n-1) formulas, as in SQL and Microsoft Excel.
StDevP, VarP	Standard deviation and variance of the expression in the current group. These values are calculated using the population (n) formulas, as in SQL and Microsoft Excel.

To use the aggregate functions, add a calculated field to a 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, PercentOfCountryTotal, 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, which 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.

Sample Report Available

For the complete report, see report "Employee Sales by Country" in the Nwind.xml report definition file, which is available for download from the NorthWind sample on the ComponentOne HelpCentral Sample page.