Aggregation in reports
Totals are an obvious need in most of the reports. SSRS brings in easy and powerful aggregation capabilities. This recipe will discuss a couple of aggregation methods and how they can be applied at different levels.
How to do it…
Reports can be aggregated as follows:
- In this recipe, two aggregations will be implemented:
- Total value of transactions per customer.
- Count of the total number of transactions per customer.
- To define the total value of the transaction, navigate to the Data | AmountMst node.
- Set the Aggregation Function property to Sum.
- To display the count of records implement the following steps. Go to the Groupings | AccountNum node in the table data region.
- Right-click on Add and select Field.
- Set the following properties:
- Preview the report and notice that the aggregated values appear with the customer details. The transactions column indicate the number of records, while the other is the total value of the transactions.
How it works…
Aggregation provides the ability to calculate data based on various functions such as Count, Sum, Avg, Min, and Max. These are applicable to a data region or a dataset. The results of the aggregation can be displayed based on the data region.
When a certain field is to be displayed aggregated in the entire report, then aggregation can be configured in the property of the field in the dataset. Each field has a property called Aggregate Function, which must be configured in this case. If the aggregation is only for the specific data region, then it must be defined in the report control in the data region.
Aggregate function results can be displayed as a summary in the header/footer for the table and list data regions. While in the matrix report, the columns and rows can be aggregated to display a grand total.