# Aggregate Functions

Get started with, FlexReport comprehensive reporting tool that provides complete reporting solution - from building complex reports to previewing, exporting, and printing.

## Content

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 FlexReport 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.

FlexReport defines the following aggregate functions:

| **Function** | **Description** |
| -------- | ----------- |
| Avg | Average value of the expression within the current group. For example, the following expression calculates the average sales for the whole group and the average sales for a certain type of product: `Avg(SalesAmount)`<br />             `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)`<br />             `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. |
| Median | Returns median from the values in the group. |
| Mode | Returns mode from the values in the group. |

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.

For the complete report, see report "Employee Sales by Country" in the **Nwind.xml** report definition file, which is available in the **ComponentOne Samples** folder.