Skip to main content

How to Use an Aggregate Function in Calculated Fields

  • 2 minutes to read

This tutorial describes how to create a report that uses an aggregate function in a calculated field. In this example, the report displays products that are not discontinued and have a total unit value greater than $500.

Calculated Fields with Aggregate Functions Report Preview

Do the following to create a report:

  1. Launch the Report Wizard and create a new table report bound to the Products table of the sample Northwind database (the nwind.db file is included with the XtraReports installation):

    Report Wizard Products Table

  2. Group the report by the CategoryID field:

    Report Wizard Group by Category

  3. The Report Wizard creates the following layout:

    Initial Report Layout

  4. Create calculated fields listed in the following table:

    Name Expression Description
    calcRevenue [UnitPrice]*[UnitsInStock] Calculates revenue per product.
    calcRevenuePercentage [calcRevenue] / [calcTotalRevenueByCategory] Calculates the revenue percentage of each product in a particular category.
    calcAggregatedValue [][[CategoryID] == [^.CategoryID] And Not[Discontinued] And [calcRevenue] >= 500].Count() Calculates the number of products in a category that are not discontinued and have revenue greater than $500.
    calcTotalRevenueByCategory [][[CategoryID] == [^.CategoryID]].Sum([calcRevenue]) Calculates the sum of product revenues in a category.

    For information about aggregate function syntax, review the following help topic: Aggregate Functions.

    Calculated Fields in Field List

    Review the following help topic for more information: Calculated Fields.

  5. Modify the report layout. Add labels and table cells bound to the previously created calculated fields as shown in the following image:

    Calculated Fields Scheme

  6. Configure the format of the calcRevenuePercentage table cell to display percentages.

  7. Preview the report.

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