Progress
Report Builder
Tutorial
Defining Aggregate and Calculated Fields
For this report, you must create both aggregate and calculated fields. Because the aggregate and calculated fields build upon one another, you must create them in the order specified in the following steps.
When you create aggregate fields that total values you already computed in other aggregate fields, it might seem logical to nest the fields or build them on one another. For example, if you create a total to compute the amount for each order, then compute the amount for all orders. However, to take advantage of the automatic accumulation frequency settings, you should avoid totalling other total fields or calculated fields that reference other total fields if possible. Also, if you do create aggregate fields that total other fields and you change the original total fields, the changes will affect all the aggregate or calculated fields that reference them.
In this report, there is more than one field that computes a total based on data in the Extended–Price field. The first field you define, Order–Total, totals the prices of the items in each order. In a later step, you create a field, Sales–Rep–Total, that computes the amount of sales for each sales representative. Although you already compute subtotals for each order with the Order–Total field, do not total the Order–Total field. Instead, calculate the Sales–Rep–Total directly from the Extended–Price field.
However, sometimes you cannot avoid totalling on a total. This happens when you compute the commission totals for the sales representatives. Because you can only determine the commission amount based on the order totals, the commission total aggregate must depend on the Order–Total field.
Follow these steps to define the aggregate and calculated fields:
- Create an aggregate, Order–Total, to compute the total amount for each order. Specify the settings listed in the following table:
Option Setting Type Total Field Extended–Price Reset 2) Order–num Accumulation Every Composite Processing Final PassWhen you defined Comm–Rate–UDF, you created an Ordertot parameter. The Order–Total aggregate field computes the actual value that will be used for this parameter.
- Create a calculated field named Comm–Amt to calculate the commission amount for each order. Use the following expression:
Order–Total * Comm–Rate(Order–Total)
This expression uses the value from the Order–Total aggregate field and multiplies it by the value computed by the Comm–Rate calculated field using the Comm–Rate–UDF user-defined function.
- Create an aggregate, Comm–Total, to compute the total amount of commissions for each sales representative. Specify the settings listed in the following table:
Option Setting Type Total Field Comm–Amt Reset 1) Sales–Rep Accumulation 2) Order–num Processing Final passYou set the Reset value to Sales–Rep so that Report Builder starts the aggregate at zero for each sales representative. Set the accumulation to the second group level so that you add the commission amount to the total once for each order. Note that for most aggregates Report Builder automatically determines the proper accumulation setting.
- Create an aggregate, Sales–Rep–Total, to compute the total amount of sales for each sales representative. Specify the settings listed in the following table:
Option Setting Type Total Field Extended–Price Reset 1) Sales–Rep Accumulation Every Composite Processing Final PassAgain, you set the Reset value to Sales–Rep so that Report Builder starts the aggregate at zero for each sales representative. Set the accumulation to “Every Composite” so that Report Builder adds the Extended–Price for each Order–Line record to the total.
- Create a calculated field, Comm–Bonus, to calculate whether a sales representative has earned a bonus based on the amount of his or her total sales. Use the following expression:
IIF(Sales–Rep–Total > 30000, 4000, 0)
This expression tests the value of the Sales–Rep–Total aggregate field to see whether it is greater than $30,000. If the value is greater than $30,000, then the sales representative earns a bonus of $4,000. If the value is less than $30,000, then the sales representative does not get a bonus.
- Create an aggregate, Grand–Total–Sales, to compute the total amount of sales for all the sales representatives. Specify the settings listed in the following table:
Option Setting Type Total Field Extended–Price Reset No–reset Accumulation Every Composite Processing Pre–passYou set the Reset value to No–reset because this field computes the grand total for the report. You must make Grand-Total-Sales a prepass aggregate because Report Builder will use it in a percent-of-total field that will be printed for each sales representative, before all the records for the other sales representatives have been printed. Again, set the accumulation to “Every Composite” so that Report Builder adds the Extended–Price for each Order–Line record to the total.
- Create a calculated field named Percent–of–Total to calculate the percentage of the total sales each sales representative contributed. Use the following expression:
Sales–Rep–Total / Grand–Total–Sales * 100
This expression divides the total for each sales representative by the total amount of all sales, then multiplies the product by 100 to make it a percentage.
- Create an aggregate, Grand–Total–Comm, to compute the total amount of commissions for all the sales representatives. Specify the settings listed in the following table:
Option Setting Type Total Field Comm–Total Reset No–reset Accumulation 1) Sales–Rep Processing Final PassYou set the Reset value to No–reset because this field computes the grand total for the report. The Accumulation frequency is Sales–Rep so that Report Builder adds the commission total for each sales representative to the grand total. Lastly, the Grand–Total–Sales field is a final pass aggregate because it computes the grand total for the report, which Report Builder displays at the end of the report.
When you finish creating the Grand–Total–Comm field, check the Aggregate Fields dialog box to make sure that you created all the necessary aggregate fields. The Aggregate Fields dialog box should look like this:
![]()
Then, check the Calculated Fields dialog box to make sure that you have created all the necessary calculated fields. The Calculated Fields dialog box should appear as follows:
![]()
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |