Skip to main content

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

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