Calculating an Aggregate Function

• Aug 03, 2020
• 3 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, which consists of four parts.

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

   • <Collection> - Specifies a collection against which an aggregated value should be calculated. It can be the relationship name in a case of a master-detail relationship, or the name of a collection property exposed by the target class. For example, [CategoriesProducts][[CategoryId]>5].Count(). Empty brackets [] indicate the root collection.
   • <Condition> - Specifies a condition defining which records should participate in calculating an aggregate function. To obtain an aggregated value against all records, delete this logical clause along with square brackets (for instance, [].Count()).
   • <Aggregate> - Specifies one of the available aggregate functions listed in the Aggregate enumeration.
   • <Expression> - Specifies an expression evaluating values to be used to perform calculation. For example, [][[CategoryID] > 5].Sum([UnitPrice]*[Quantity]). The Count function does not require field values to count the records, so leave the round brackets empty for this function.

   You can refer to the currently processed group using the Parent Relationship Traversal Operator (‘^’). This allows you to calculate aggregates within groups using expressions like the following: [][[^.CategoryID] == [CategoryID]].Sum([UnitPrice]).

   For more information, see Criteria Language Syntax and Expression Operators, Functions, and Constants.

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.