Progress
Report Builder
User’s Guide
Using Aggregate Fields in Reports
After you define an aggregate field, you can insert it in the report layout as you would insert any other field. However, you must place a final pass aggregate on the band line that corresponds to the reset level to produce the desired calculation final pass. In contrast, you can place prepass aggregates anywhere on the layout.
Because Report Builder calculates aggregate fields cumulatively as it reads each record contributing to the total, by default the aggregate field’s final value is not available until the last record being totaled is read. However, if you specify the prepass processing option for the field, Report Builder reads all the records before outputting the report. Use the following table as a guide to placing final pass aggregate fields in your reports:
The following sections describe each of these placement options in more detail.
Grand Total for a Report
You typically place an aggregate field that calculates a grand total in a summary band line at the end of a report. For example, you can calculate the grand total of customer balances for a report. To do this, create an aggregate field, Grand-Total, that totals a customer balance field and has a reset value of “No-reset.” Then insert the Grand-Total field on the summary line.
Figure 5–1 shows an aggregate field that calculates the grand total of the customer balance field for a report.
Figure 5–1: Aggregate Field for Grand Total
![]()
The layout shown above produces the report excerpt shown in Figure 5–2.
Figure 5–2: Report with a Grand Total
![]()
Total for Each Page
You typically place an aggregate field that calculates a total for each page in the page footer band line so that it prints at the end of each page. For example, you can calculate the total of customer balances for each page of a report. To do this, create an aggregate field, Page-Total, that totals a customer balance field and has a reset value of “Per-page.” Then place the Page-Total field on the page footer line.
Figure 5–3 shows an aggregate field that calculates the total of customer balance fields for each page.
Figure 5–3: Aggregate Field for Page Total
![]()
The layout shown above produces the report excerpt shown in Figure 5–4.
Figure 5–4: Report with a Page Total
NOTE: Page totals of group aggregates include only groups that end on the page.Total for Each Group
You typically place an aggregate field that calculates a total for each group in the group footer band line defined at the same level as the field’s reset level. This ensures that it calculates the total for the specified group and prints at the end of each group.
For example, you can calculate the total for each order in an order report. To do this, create an aggregate field, Order-Total, that totals the amount of each item in the order and set the reset value for the group level that corresponds to the order number. If the second group level is based on the Order-Num field, then the reset value is “2) Order-Num.” Then, place the Order-Total field on the corresponding group footer band line, 2GF-Order-Num, so that it calculates the total for each group or order. Figure 5–5 shows an aggregate field on a group footer line that calculates the total for each order and resets for every order number.
Figure 5–5: Aggregate Field for Group Total
![]()
The layout above produces the report excerpt in Figure 5–6.
Figure 5–6: Report with Group Aggregates
![]()
Report Builder produces a total for each new group of records, that is, every time the value of the Order-Num field changes.
Running Total by Page
You can also place an aggregate field on a page footer band line to display the running accumulation of the aggregate value as Report Builder processes each page. For example, instead of calculating the total of the customer balances for each page, you can create an aggregate field to calculate the running total for each page of the report. To do this, create an aggregate field, Running-Total, that totals a customer balance field and has a reset value of “No-reset.” Then place the Running-Total field on the page footer band line.
Figure 5–7 shows an aggregate field that calculates the running total of customer balance fields and displays the value at the bottom of each page.
Figure 5–7: Aggregate Field for Running Page Total
![]()
The report layout shown above produces the report excerpt shown in Figure 5–8.
Figure 5–8: Report with a Running Page Total
NOTE: If you change the Processing setting to Pre-pass, Report Builder displays the final report total on each page, not the cumulative page total.Running Total by Group
You can also place an aggregate field on a group footer band line to display the running accumulation of the aggregate value as Report Builder processes each group. For example, instead of calculating the total of each order, you can create an aggregate field to calculate the running total for each order in the report. To do this, create an aggregate field, Running-Total, that totals the amount of each item in the order and set the reset value to “No-reset.” Then place the Running-Total field on a group footer band line that summarizes the information for each group or order.
Figure 5–9: shows an aggregate field that calculates the running total of all the orders and displays the value at the bottom of each group.
Figure 5–9: Aggregate Field for Running Total byGroup
The layout in Figure 5–9: produces the report excerpt in Figure 5–10.
Figure 5–10: Report with Running Group Total
NOTE: If you change the Processing setting to Pre-pass, Report Builder displays the final report total at the end of each group, not the cumulative group total.Running Total by Record
You can place an aggregate field on a record band line to display the running accumulation of the aggregate value as Report Builder processes each record. You can also calculate the running total of the amount owed.
For example, instead of calculating the total of each order, you can create an aggregate field to calculate the running total for each record in the report. To do this, create an aggregate field, Running-Total, that totals the amount of each item and set the reset value to “No-reset.” Then place the Running-Total field on a record band line.
Figure 5–11 shows an aggregate field that calculates the running total of all the items and displays the value next to each item.
Figure 5–11: Aggregate Field for Running Record Total
The report layout shown above produces the report excerpt shown in Figure 5–12.
Figure 5–12: Report with Running Record Total
![]()
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |