Totaling a Totaled Field in a Report

by Bill Warner and Cal Locklin

A fairly common requirement in a report based on a set is to have a group break on the linking field, and to have totals for that group.  For example, in an application with JOB_NO as the linking field, we could design a report to show totals for each job.  If SALEAMT is a field in the one-many child (ITEMS), we could easily see the total sales for each job by first creating a group break on JOB_NO, and then creating a calc field in the JOB_NO group to show the total:

SALEAMT_T1 = TOTAL(ITEMS->SALEAMT,GRP->JOB_NO) 1

(As many of you know, this field can be created for you when you drag the field onto the report.  The Genie will prompt you for the result you want for the field.)

Then, at the end of the report, we could insert a calc field to return the grand total:

SALEAMT_TG = TOTAL(CALC->SALEAMT_T1,GRP->GRAND)2,3

In essence, our grand total is a total of a totaled field (SALEAMT_T1).

However, there are times when the amount in the group is not simply the total for one field.  This total might be the result of several fields from several of the tables in the set.  For example, the total could be the sum of the values in SALEAMT plus a value from a field (SURCHG_AMT) from a one-one child table (CUST).  In this case, the amount in the group could be defined as follows:

TOTAMT_T1 = TOTAL(ITEMS->SALEAMT,GRP->JOB_NO) + CUST->SURCHG_AMT

This is still a straightforward calculation for the group total.  However, retrieving the grand total is not quite so simple.  The calc expression that we might think would work:

TOTAMT_TG = TOTAL(TOTAMT_T1,GRP->GRAND)

would in fact give us the wrong result!  This is because Alpha will go to the first record for a given JOB_NO in the parent, get the value for SURCHG_AMT in CUST, and then the value for SALEAMT in the first record in ITEMS.  If there is a second record in ITEMS, Alpha will then get the value for SURCHG_AMT from CUST again, and then the value for SALEAMT in the second child record.  The more one-many child records there are, the more times the value from CUST will be added to the total.

Obviously, this is not what we want - we only need the value from CUST once.  But how do we tell Alpha this?  Actually, it's rather simple:

TOTAMT_TG = TOTAL(TOTAMT_T1,GRP->GRAND,GRP->JOB_NO)

By adding the last parameter (GRP->JOB_NO) in the expression, we are telling Alpha to return the value from CUST only one time for each value of JOB_NO, and then get a total from all child records from ITEMS.


I am using a naming convention for the calc field that will help me identify the field later.  The suffix _T1 on the field name tells me that it is a field that returns the total of that field for group 1.  Likewise, the suffix _T2 would apply to totals in group 2, etc., and _TG would apply to the grand total in the page footer.

2 Note that the GRP->GRAND is not necessary - TOTAL(CALC->SALEAMT_T1) will return the same value.

3 This could also be written:

SALEAMT_TG = TOTAL(ITEMS->SALEAMT,GRP->GRAND)

where we are getting a grand total of the field in the one-many child.  I showed it as a total of a calc field to more clearly illustrate the concept.