We had a look at how you can summarize your data using Aggregate Functions in the first part of this two-part Tips series. In this tip, we’re going to show you how you can use Aggregate Filters in the Sage Intelligence Report Manager to further summarize your data.
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, which were covered in the previous Tip.
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 Filter
An Aggregate Filter is assigned to the property of the report to filter and summarize the data coming onto the report. As an example, consider the following unfiltered raw data:
If an Aggregate Filter was applied on Sale Amount greater than or equal to $250.00, the aggregation would result in:
In this example, the aggregation has filtered the data to show only transactions and customers greater than or equal to $250. The output is not affected except that transactions or customers not meeting the minimum of $250 in TOTAL sales are excluded.
The result of using aggregation is that the report itself is more compact, thus running 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 thus faster to produce or run.
