You may find that your reports are taking some time to generate into Microsoft® Excel® or you have “excess” data being pulled into the Sage Intelligence Excel template. To speed up your report generation process or to have the data summarized before it generates into Excel, you can make use of ‘Aggregate Functions’ and ‘Aggregate Filters’ in the Sage Intelligence Report Manager.
Aggregation is a method of summarizing or grouping data in a report in Sage Intelligence Reporting. When running a report on a dataset with a large volume of transactions, an Aggregate Function could be used to summarize similar transactions for the same customer, month and product. Once this aggregation is done, an Aggregate Filter could be used to retrieve only customers whose total sales are over a certain amount.
Types of Aggregation used in Sage Intelligence Reporting:
There are two types of aggregation that can be used from within the Report Manager.
1. An Aggregate Function defined on a column/field.
For example, applying an aggregate to a Total Sales line.
2. An aggregate filter defined for a report.
For example, Total Sales over 250.
Example of an Aggregate Function
An Aggregate Function is assigned to the property of a column to summarize the unique lines of the report. As an example, consider the following unfiltered raw data:
If an Aggregate Function of SUM is added to the Sale Amount column, the aggregation would result in:
In this example, the aggregate has added together similar lines, thus summarizing the data by Sale Amount per customer, per product, per month and per salesman.
TIP: When using Aggregate Functions, it is best to have a report that has fewer fields, as it is summarizing the data based on the number of lines returned by the query.
The result of using aggregation is that the report itself is more compact and so runs faster. While it is true that many times Sage Intelligence reports are summarized later using Microsoft Excel functionality such as PivotTables, the use of Aggregation Functions and Filters affects the query that is used to get the report, thus making it a more efficient query, and therefore faster to produce or run.
