Aggregating over groups of records
Go Up to Using Maintained Aggregates
By default, maintained aggregates are calculated so that they summarize all the records in the client dataset. However, you can specify that you want to summarize over the records in a group instead. This lets you provide intermediate summaries such as subtotals for groups of records that share a common field value. Before you can specify a maintained aggregate over a group of records, you must use an index that supports the appropriate grouping.
Once you have an index that groups the data in the way you want it summarized, specify the IndexName and GroupingLevel properties of the aggregate to indicate what index it uses, and which group or subgroup on that index defines the records it summarizes.
For example, consider the following fragment from an orders table that is grouped by SalesRep and, within SalesRep, by Customer:
SalesRep | Customer | OrderNo | Amount |
---|---|---|---|
1 |
1 |
5 |
100 |
1 |
1 |
2 |
50 |
1 |
2 |
3 |
200 |
1 |
2 |
6 |
75 |
2 |
1 |
1 |
10 |
2 |
3 |
4 |
200 |
The following code sets up a maintained aggregate that indicates the total amount for each sales representative:
Delphi:
Agg.Expression := 'Sum(Amount)';
Agg.IndexName := 'SalesCust';
Agg.GroupingLevel := 1;
Agg.AggregateName := 'Total for Rep';
C++:
Agg->Expression = "Sum(Amount)";
Agg->IndexName = "SalesCust";
Agg->GroupingLevel = 1;
Agg->AggregateName = "Total for Rep";
To add an aggregate that summarizes for each customer within a given sales representative, create a maintained aggregate with level 2.
Maintained aggregates that summarize over a group of records are associated with a specific index. The Aggregates property can include aggregates that use different indexes. However, only the aggregates that summarize over the entire dataset and those that use the current index are valid. Changing the current index changes which aggregates are valid. To determine which aggregates are valid at any time, use the ActiveAggs property.