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.
Do the following to create a report:
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):
Group the report by the CategoryID field:
The Report Wizard creates the following layout:
Create calculated fields listed in the following table:
Name Expression Description calc Revenue [Unit
Price]*[Units In Stock] Calculates revenue per product. calc Revenue Percentage [calc
Revenue] / [calc Total Revenue By Category] Calculates the revenue percentage of each product in a particular category. calc Aggregated Value [][[Category
ID] == [^. Category ID] And Not[Discontinued] And [calc Revenue] >= 500]. Count() Calculates the number of products in a category that are not discontinued and have revenue greater than $500. calc Total Revenue By Category [][[Category
ID] == [^. Category ID]]. Sum([calc Revenue]) 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.
Modify the report layout. Add labels and table cells bound to the previously created calculated fields as shown in the following image:
Configure the format of the calcRevenuePercentage table cell to display percentages.
Preview the report.