Calculate an Aggregate Function

• Jun 01, 2022
• 2 minutes to read

Tip

Online Example: How to use aggregate functions in Calculated Field expressions

This tutorial describes the steps required to create a report with an aggregate function. In this example, products that are not discontinued and have a total unit value greater than $500 will be displayed.

Do the following to calculate an aggregate function:

1. To create a table report in this tutorial, start with a report that is bound to the “Categories” and “Products” tables of the sample Northwind database (the nwind.mdb file is included with the XtraReports installation).

   Tip

   See Bind a Report to Multiple Data Tables to learn more about binding a report to multiple data tables.

   This tutorial starts with the following report layout:

   

2. Create a new calculated field and in the Properties window set the field name to “AggregateField”.

3. Click the ellipsis button in the Expression section of Properties window. The Expression Editor dialog will appear.

   

4. In this dialog, double click the [CategoriesProducts] field and choose Functions | Aggregate. Then, double click the Count() function, and insert the following text in the empty square brackets.

   “Not[Discontinued]And[UnitTotal] >= 500”.

   

   To construct a valid aggregate expression, use the following format:

   [<Collection>][<Condition>].<Aggregate>(<Expression>)

   • <Collection> - Specifies a collection to calculate an aggregated value against. It can be the relationship name for a master-detail relationship, or a collection property’s name exposed by the target class. For example, [CategoriesProducts][[CategoryId]>5].Count(). Empty brackets [] indicate the root collection.
   • <Condition> - Specifies a condition that defines which records to use for the aggregate function calculation. To calculate an aggregated value against all records, delete this logical clause and its square brackets (for example, [].Count()).
   • <Aggregate> - Specifies one of the available aggregate functions listed in the Aggregate enumeration.
   • <Expression> - Specifies the expression to use. For example, [][[CategoryID] > 5].Sum([UnitPrice]*[Quantity]). The Count function does not require field values to count the records (the round brackets can be empty for this function).

   Use the Parent Relationship Traversal Operator (‘^’) to refer to the processed group (for instance, [][[^.CategoryID] == [CategoryID]].Sum([UnitPrice])). This allows you to calculate aggregates within groups.

   For more information, see Expression Constants, Operators, and Functions.

5. Click OK to close the dialog and save the expression.

6. Next, add three XRLabel objects to the Detail Band and customize their content, as shown in the image below.