792.TXT
=====================================================================
SUMMING BREAK FOOTER TOTALS
=====================================================================
PRODUCT: R:BASE VERSION: 4.5++ or Higher
=====================================================================
CATALOG: Forms, Reports & Labels AREA : Reports
=====================================================================
Using the report section evaluation feature of R:BASE 4.5++, summing
break footer totals is now easy as pie. In the past, multiple levels
of totals were done by repeating the same totaling expression for a
different variable name and not resetting the variable. A total
itself was not usually summed. Special procedures were necessary
when summing totals to make sure that the result was correct. None
of that is necessary in 4.5++. An example is used to show the
different ways of calculating sub-totals.
For example, let's look at creating report variables to print total
sales by region, salesperson and then for the whole company. Three
variables are needed, one for each total. These expressions, often
tried first by users, won't work correctly.
vsalesprsn_tot = SUM OF sales_amount
vregion_tot = SUM OF .vsalesprsn_tot
vcompany_tot = SUM OF .vregion_tot
A SUM OF expression must be evaluated at the detail level, every row.
When you sum a sum, as in the expression for vregion_tot, data values
are added into the total more than once, so the result is too big.
Instead, three variables are defined that have the same definition,
but accumulate the data based on different break points.
vsalesprsn_tot = SUM OF sales_amount
vregion_tot = SUM OF sales_amount
vcompany_tot = SUM OF sales_amount
Each variable accumulates the totals for different groups of data
based on when the particular variable is reset. The variable
vsalesprsn_tot is reset at the breakpoint for salesperson (break 2),
the variable vregion_tot is reset at the breakpoint for region
(break 1), and the variable vcompany_tot is not reset at all. Each
sums the detail data, the column sales_amount, as each row is
processed. The correct totals print at the specified times in the
report.
Sometimes though, a sub-total or break total is not a simple SUM OF
colnumname expression, but is the result of other calculations. It
may not be possible to duplicate the expressions for a different
level of total as in the above example. A total might depend on a
value looked up from another table that changes with each break
point, such as a tax rate or discount rate. These totals require
a different method.
There are two methods used to accurately sum break footer totals.
Each method is described in detail below.
The 4.5++ method_works in 4.5++ only and uses the report section
evaluation feature to easily calculate footer totals. The
expression is defined and set to evaluate only at the footer.
This removes the extra values added in when the expression is
evaluated at the detail level, every row.
The alternative method_works with any version of R:BASE back to
3.1B. Uses more expressions and is slower than the 4.5++ method.
The 4.5++ Method
The simple case of summing a break footer total involves defining the
following expression and setting it to evaluate at the appropriate
break footer. The variable vSubTotal is the expression defined to
calculate the break total. The variable vGrandTotal is the sum of
the break totals.
1: CURRENCY : F1 : vGrandTotal = (.vGrandTotal + .vSubTotal)
If vSubTotal is calculated at break level2, then the variable
vGrandTotal would be set to evaluate at break level 2, but is printed
at break level 1, for example. VGrandTotal adds in the break level 2
total value only at the end of the break, not every row.
A more complex example using the same technique involves totaling a
break footer sum that is the result of a calculation where part of
the calculation is looked up. The report prints monthly bills by
account where the hourly rate varies depending on customer usage
during the month_the more hours used, the lower the hourly rate.
Before the account total can be calculated, the total hours need to
be summed and the rate looked up based on the total hours. The
individual account totals can be calculated fairly easily, but a
grand total of all accounts is also needed, and that can only be
determined by totaling the account totals since each account can use
a different rate.
Two tables are used_USAGE (with accounts, service dates, and hours
used), and RATES (with the rate schedule). Here's what the report
might look like; notice that each account is billed at a different
rate:
Account #: AA-0001
Hours Service Date
22 08/09/94
16 08/11/94
18 08/12/94
-----
56 hours at $13.00 per hour: $728.00
Account #: AA-0002
Hours Service Date
13 08/10/94
11 08/11/94
-----
24 hours at $14.00 per hour: $336.00
Account #: AA-0003
Hours Service Date
9 08/10/94
7 08/09/94
-----
16 hours at $14.50 per hour: $232.00
Total Billings: $1,296.00
Just four report expressions are used:
1: INTEGER : D : vSumHours = sum of HoursUsed
2: CURRENCY : F1 : vRate = RatePerHour in Rates
WHERE
MinHours < .vSumHours and
MaxHours > .vSumHours
3: CURRENCY : F1 : vAccTotal = (.vRate * .vSumHours)
4: CURRENCY : F1 : vTotalBill = (.vTotalBill +
.vAccTotal)
vSumHours_calculates the total hours per account. This variable
is reset at the account break point. Since the variable is a sum,
it must be evaluated at the detail level.
vRate_looks up the billing rate based on the total hours. This
variable only has the correct value after the total hours are
known, so it is set to calculate at the break footer.
vAccTotal_calculates the account total. At the break footer, the
variable multiplies the sum of the hours by the looked up rate.
vTotalBill_calculates the report total, the sum of all account
totals. This can't be calculated by taking the sum of hours times
a rate, since the rate is different for each account. The variable
must be a break footer calculation. This total is printed at the
report footer.
This technique can be used on any report to sum break footer totals.
In addition to an easier set of expressions, evaluating variables at
sections rather than every row can considerably improve report
performance. Just remember that when specifying variables to evaluate
at particular sections of a report, that report section must be
located in the report layout.
The All Versions Method
The all versions method works back to 3.1B. It requires more
expressions and is slower, but works regardless of the version of
R:BASE used. The simple case of totaling a break footer sum uses the
once-per-break technique described in the article "Once-per-Break
Operations" in the May/June 1993 Exchange, Fax document #719. Three
expressions are needed to accurately sum the break footer total,
vSubTotal:
1: INTEGER : vCount = (.vCount + 1)
2: INTEGER : vRowCount = (COUNT(*)) FROM rep_table WHERE
break_column=break_column
3: CURRENCY : vGrandTotal = (IFEQ(.vCount, .vRowCount, +
(.vGrandTotal +
.vSubTotal),.vGrandTotal))
vCount_counts the number of rows processed for the break.
This variable is reset at the break point and starts at one for
each break group.
vRowCount_calculates the total number of rows in the table for the
break group. When all the rows for a break have been processed,
the vCount variable and the vRowCount variable have the same value.
vGrandTotal_uses the logical function, IFEQ, to test the vCount and
vRowCount variables. Only when they are equal, at the break footer,
is the footer subtotal added to the grand total.
Using report section evaluation in 4.5++ can improve the performance
of these expressions; in versions prior to 4.5 ++, all the
expressions are evaluated for each row. The variable vRowCount can be
set to be evaluated at the break header. The variable vGrandTotal can
be evaluated at the break footer. The variable vCount must be
evaluated at the detail section. The break footer total, vSubTotal,
must be evaluated at the detail section. All sums that are
accumulating row by row must be evaluated row by row.
The same technique is used in the more complicated report that prints
monthly bills by account when the hourly rate varies depending on
customer usage during the month_the more hours used, the lower the
hourly rate. Six expressions are needed to do calculations. All the
expressions evaluate every row in versions prior to 4.5 ++.
1: INTEGER : vSumHours = sum of HoursUsed
2: CURRENCY : vRate = RatePerHour in Rates WHERE
MinHours < .vSumHours and
MaxHours > .vSumHours
3: INTEGER : vCount = (.vCount + 1)
4: INTEGER : vRowCount = (Count(*)) from usage where
account# = account#
5: CURRENCY : vAccTotal = (.vRate * .vSumHours)
6: CURRENCY : vTotalBill = (IFEQ(.vCount, .vRowCount,
.vTotalBill + .vAccTotal,.vTotalBill))
The break footer total, vAccTotal, is added to the grand total,
vTotalBill, only when the number of rows processed for the break
group equals the number of rows looked up for the break.
Use either of these methods to accurately sum break footer totals
regardless of the R:BASE version you are using. Use the 4.5++ method
if possible as the report processes significantly faster.