Progress
Report Builder
User’s Guide
Filtering on Aggregate Values
Report Builder allows you to filter records based on aggregate field values depending on whether your report includes pre-pass aggregates:
By default, Report Builder creates aggregate fields as running aggregates. This means that Report Builder calculates the field’s value cumulatively as it reads each record that contributes to the aggregate. However, if you specify a field as pre-pass, Report Builder calculates the final values for the aggregate before printing the report records. While you can create filters using running counts and totals with a No-reset level, these filters might not produce the information you want because only records that meet the filter conditions contribute to the aggregate. However, it is easier to predict the filter results if you filter on a prepass aggregate because Report Builder reads all records that contribute to the aggregate before applying the filter.
Filtering on Running Aggregates
You cannot filter on running aggregates if your report contains any prepass aggregates. In reports that do not contain any prepass aggregates, you can filter on running totals and counts with their reset level set to No-reset. However, since Report Builder tests the current record against the filter before computing the aggregate for that record, Report Builder applies the filter based on the aggregate value as of the previous record. In other words, since Report Builder does not calculate the aggregate for the current record before applying the filter, Report Builder decides whether to include the record based on the aggregate values of the previous record.
For example, when you filter on the running count aggregate field, Report Builder does not increment the count for the current record until after applying the filter. To use the filter to select the first three records, you must specify that the Count field value is less than 3 rather than less than or equal to 3.
To select the first three records using the Counter aggregate field, which counts the Name field, specify the following filter condition:
Without the filter, the report includes the following customers, sorted by their balance in descending order:
With the filter, Report Builder selects the following records:
Filtering on Prepass Aggregates
In reports that contain prepass aggregates, you can filter on any prepass aggregate that resets at the highest group level at which you have defined any prepass aggregate. You cannot filter on running aggregates or on other prepass aggregates in the report. For example, you cannot filter on a prepass group aggregate if the report also contains prepass aggregate with a No-reset setting.
For example, you can group orders by Name and Order-num and use the Order-Total aggregate field to calculate the total for each order. The Order-Total field is a prepassed field that resets for each order. You can create a filter that selects only those records where Order-Total is greater than $500. Because of the way Report Builder accumulates prepass aggregates, none of the running aggregates in this report would be available for filtering. In addition, the filters on the prepass Order-Total aggregate are invalid if the report contains any higher level prepass aggregates. An error message notifies you of invalid filters when you try to display or print such a report.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |