Calculate an Aggregate Function

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.

  1. Create a new or open an existing data-bound report. This tutorial starts with the following report layout:

  2. Create a new calculated field and set the field name to "AggregateField".

  3. Click the Edit button for the calculated field and click the Expression property's ellipsis button.

  4. In the invoked Expression Editor, double click the [CategoriesProducts] field and choose Functions | Aggregate. Then, double click the Count() function and insert the following text into 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 example, [].Count()).
    • <Aggregate> - Specifies one of the available aggregate functions.
    • <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 Expression Syntax.

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

  6. Add three Labels to the Detail Band and customize their content as shown in the following image:

The report is now ready. Switch to Print Preview to see the result.