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