Progress
Report Builder
Tutorial
Creating Fields to Total the Sales for Each Item
To create the fields to total each item per sales representative, you must first create a calculated field for each representative, then an aggregate field for each representative.
Calculated Fields
The first part of the computation is handled by calculated fields that test the value of the Sales–Rep field and return the value in the Extended–Price field for each item sold by that sales representative. As Report Builder reads each record in the database, it checks the sales representative associated with the order. If the value in the Sales–Rep field matches the one specified in the calculated field expression, it returns the value in the Extended–Price field.
The value in the Extended–Price field is the price paid for the quantity of the item ordered. You do not use the Price field, because it is the price for only one of the items, not the total price for the quantity ordered.
You must create a calculated field for each sales representative so you can use the fields in the aggregate fields that complete the computation of the item total for each sales representative.
Follow these steps to create a calculated field for each sales representative:
- Create a calculated field, ARM–Sales, with this expression:
IIF(Salesrep.Sales–Rep = ”ARM”, Extended–Price, 0)
This expression instructs Report Builder to return the value in the Extended–Price field only if the Sales–Rep field contains “ARM”; otherwise it returns zero.
- Create a second calculated field named BKO–Sales. However, instead of entering the expression, copy it from the ARM–Sales field and select OK. To do this, choose the Calc Expression button, then select the ARM–Sales field. The expression for the ARM–Sales field appears in the Expression box for the field you are creating.
- Change the sales representative’s initials in the expression to BKO, then choose OK. Report Builder creates the new BKO–Sales field and returns to the Calculated Fields dialog box.
- Create calculated fields for each of the remaining sales representatives in the same manner, then return to the report layout. The remaining initials are JCO, MAR, MTW, PAM, PMB, SLA, and SMT.
The following Calculated Fields dialog box shows the Recno_rb field and the complete list of sales representative fields:
![]()
Make sure that you created all nine calculated fields.
Aggregate Total Fields
Now that you have fields that identify the items sold by each sales representative, create the aggregate fields that calculate the total amount of each item each representative has sold.
Follow these steps to create an aggregate field for each sales representative:
- Create an aggregate field, ARM–Item–Total, using the settings listed in the following table:
Option Setting Type Total Field ARM–Sales Reset 1) Item–Name Accumulation Every Composite Processing Final PassBy calculating a total based on the ARM–Sales field that resets after every item number, this field performs the task of going through each order and calculating how much of each item ARM sold.
- Create aggregate fields for each of the remaining sales representatives using the appropriate calculated field, then return to the report layout. The remaining initials are BKO, JCO, MAR, MTW, PAM, PMB, SLA, and SMT.
The following Aggregate Fields dialog box shows the complete list of sales representative item total fields:
![]()
Make sure that you created a field for each sales representative.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |