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.