How to: Display Aggregate Values in a Data Region

Article
11/29/2021

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

There are several aggregate functions, such as Count, Sum, Min, and Max, that are used to calculate aggregate values for a data region in a report. For a complete list of functions and their descriptions, see Aggregate Functions. The following table describes how aggregate values display in the various data region types.

Data region	Description
Table and list data regions	You can display grand total aggregate values in a summary section at the bottom of the list or table data region. For a grouping in a list or table data region, you can display aggregate values as subtotals in group headers or group footers.
Matrix data regions	You can display aggregate values for row and column groupings in a matrix data region. For a row grouping, aggregate values are displayed as subtotals in a row within the matrix. For a column grouping, aggregate values are displayed as subtotals in a column within the matrix.
Chart data regions	For chart data regions, aggregate values display as data in a chart. For example, if you want your chart to display a breakout of the total amount of customer transactions per customer group, you can use the Sum aggregate function to calculate a single value for each customer group. The data that displays the in chart can also be aggregated in the dataset query. In that case, it may not be necessary to assign an aggregate function to a field.

Table and list data regions

You can display grand total aggregate values in a summary section at the bottom of the list or table data region. For a grouping in a list or table data region, you can display aggregate values as subtotals in group headers or group footers.

Matrix data regions

You can display aggregate values for row and column groupings in a matrix data region. For a row grouping, aggregate values are displayed as subtotals in a row within the matrix. For a column grouping, aggregate values are displayed as subtotals in a column within the matrix.

Chart data regions

For chart data regions, aggregate values display as data in a chart. For example, if you want your chart to display a breakout of the total amount of customer transactions per customer group, you can use the Sum aggregate function to calculate a single value for each customer group.

The data that displays the in chart can also be aggregated in the dataset query. In that case, it may not be necessary to assign an aggregate function to a field.

You can assign an aggregate function to a field within a dataset or within a report design. If a dataset is used in more than one report design and the same aggregate function is needed for each design, it is best to assign the aggregate function to the field in the dataset. If a different aggregate function is needed for the field in each of the report designs, then it is best to assign the aggregate function within the report designs. The following procedures explain how to assign an aggregate function to a field and how to display grand totals and subtotals in data regions.

To assign an aggregate function to a field in a dataset

In Model Editor, expand the node for the dataset that contains the field that you want to assign an aggregate function to.
Select the field.
In the Properties window, select an aggregate function from the drop-down menu for the Aggregate Function property.

To assign an aggregate function to a field in a report design

In Model Editor, expand the node for the report that you want to work with.
Expand the Designs node for the report, and then expand the node for auto design.
Expand the node for the data region, and then expand the Data node.
Select the field that you want to add an aggregate function to.
In the Properties window, expand the Aggregation node.
Select an aggregate function from the drop-down menu for the Aggregate Function property.

To display grand totals in list and table data regions

In Model Editor, expand the node for the table or list data region, and then expand the Data node.
Select the field for which you want to display a grand total.
In the Properties window, expand the Aggregation node.
Select an aggregate function for the Aggregate Function property, specify a caption to display using the Grand Total Caption property, and set the Render Grand Total property to True.

To display subtotals for groupings in list and table data regions

In Model Editor, expand the node for the table or list data region, and then expand the Groupings node.
Select the node for the grouping.
In the Properties window, expand the Instance Aggregates node.
Set the Render Location property to GroupFooter or GroupHeader depending upon where you want the subtotal to display. For footer aggregates, specify a caption using the Footer Aggregates Line Label property.

To display subtotals for row or category groupings in matrix data regions

In Model Editor, expand the node for the matrix data region, and then expand the Row Groupings or Column Groupings node depending upon the grouping type.
Select the node for the row or column grouping.
In the Properties window, set the Display Subtotals property to True.