How to Use an Aggregate Function in Calculated Fields

• Mar 20, 2023
• 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.

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):

   

2. Group the report by the CategoryID field:

   

3. The Report Wizard creates the following 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.

   

   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:

   

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