The requested page is not available for the requested platform. You are viewing the content for Default platform.

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.

How to - Calculate an Aggregated Function_Preview

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:

    How to - Calculate an Aggregated Function

  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.

    How to -  Calculate an Aggregated Function with AggregateField1

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

    How to - Calculate an Aggregated function_ExpressionEditor

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

    How to -  Calculate an Aggregated Function with AggregateField