Output Processing: subtotals, dummy fields, aliases, and arrays by Kenneth Brody One of the more common problems reported to our Technical Support Department is a report that generates incorrect totals. Most of the time, the cause is a misunderstanding of how dummy fields work in subtotal sections of a report. In this article, I will discuss the ins and outs, the whys and wherefores, of how dummy fields work within output processing, specifically relating to subtotal breaks. Aggregate functions One of the simplest forms of using subtotal breaks is the use of aggregate functions. These are the min, max, avg, and tot functions. These functions calculate a value based on a set of data -- the set of records within the subtotal break -- rather than on a single piece of data. For example, the statement "aa=tot(price)" will calculate the total of all the prices for which the statement is executed. Note that the statement must be in the normal detail-level processing, not in the @wbrk processing, and that it must be executed for every record you want entered into the total. By then placing field aa on the report within the subtotal section, you will get the total price for that subtotal. By placing it in a different subtotal section, you will get the total for that section. See figures 1 and 2. === figure 1: Sample report format === +-------------------------------------------+ 04.01.01 | D E F I N E O U T P U T F O R M A T S | +-------------------------------------------+ 10 20 30 40 50 60 70 80 ....:....|....:....|....:....|....:....|....:....|....:....|....:....|....:....| H E A D I N G / T I T L E L I N E S Date: *@td Subtotals Page: <@pn D A T A L I N E S record <@rn price = *2 aa = *aa _____________________________T O T A L L I N E S______________________________ SUBTOTAL BY VENDOR ======================= SUBTOTAL BREAK: aa = *aa G R A N D T O T A L ======================= GRAND TOTAL: aa = *aa E N D O F F O R M File: ARTICLES--------------------------------------------------Format: SUBTOTAL Enter Selection > F10 -Help, U -Update, S -Select Format, F -Select File, C -Copy Format, H -Hardcopy, D -Delete Formats, X -Exit === end figure === === figure 2: Sample output from figure 1 == Date: 10/09/92 Subtotals Page: 1 record 1 price = 1.000 aa = 1.000 record 2 price = 2.000 aa = 2.000 record 3 price = 3.000 aa = 3.000 ======================= SUBTOTAL BREAK: aa = 6.000 record 4 price = 10.000 aa = 10.000 record 5 price = 20.000 aa = 20.000 record 6 price = 30.000 aa = 30.000 ======================= SUBTOTAL BREAK: aa = 60.000 record 7 price = 11.000 aa = 11.000 record 8 price = 13.000 aa = 13.000 record 9 price = 15.000 aa = 15.000 record 12 price = 14.000 aa = 14.000 record 13 price = 15.000 aa = 15.000 ======================= SUBTOTAL BREAK: aa = 68.000 record 10 price = 1234.000 aa = 1234.000 record 11 price = 4321.000 aa = 4321.000 ======================= SUBTOTAL BREAK: aa = 5555.000 ======================= GRAND TOTAL: aa = 5689.000 === end figure === Multiple break levels You may be wondering how the value of field aa "magically" changes, based on where you place it on your report. To answer that question, you need to understand how filePro handles dummy fields in output processing. There are two main rules to remember when using dummy fields in output processing: _ First, there is a separate copy of each dummy field defined in output processing for each break level, including grand totals. Note that fields defined in automatic processing still have only one copy. _ Second, each break-level copy of a dummy field is only cleared after its respective break is executed. The detail-level copy of non-global dummy fields are cleared on every record. That is, if you have a report with subtotal breaks on vendor and product, there will be four copies of each dummy field. The detail-level copy will be cleared after every record. The product-level copy will be cleared only after the product code changes. The vendor-level copy will likewise only be cleared after the vendor changes. Finally, the grand-total-level copy is cleared after the grand total (that is, never.) While this may seem like an unnecessary complexity, most of the time it actually simplifies the coding necessary to calculate totals. Consider the simple statement "aa=tot(price)". By placing aa on each subtotal section and the grand total section of a report, you will get the appropriate sub- or grand-total for that section of the report. Without the multiple copies, you would have to keep a separate total for each break level, in your @wbrk processing you would then move the appropriate totalling field into a different field to be printed, and then clear the totalling field (see figure 3). === figure 3: Automatic vs. manual break levels === Then: aa(10,.2) = tot(price) - or - Then: ab(10,.2,g) = ab + price ; ac(10,.2,g) = ac + price Then: ad(10,.2,g) = ad + price ; aa(10,.2) Then: end @wbrk1 Then: aa = ad ; ad = "" ; end @wbrk2 Then: aa = ac ; ac = "" ; end @wgt Then: aa = ab ; end === end figure === "Totalling totals" Most of the time, this "behind-the-scenes" functionality goes unnoticed, since everything works as you might expect. There are times, however, that you can forget that this is happenning, and this can lead to problems. The most common problem that occurs is forgetting to clear the field between records when totalling fields manually, . Consider the example in figure 4. The code looks simple enough: ab should contain the total of the prices for those items with a quantity greater than zero. In fact, if you were to place ab on the detail section of the report, everything looks fine. However, when you place ab on the subtotal section, you may get a different value for the total. Note that it is not necessary for field ab to be made global in order to get subtotals. However, by making it global, you can get a running total on the detail lines. The problem is that you have forgotten the second rule: the subtotal copies are cleared only after the corresponding subtotal break. In the case where quantity1 is not greater than zero, the subtotal copies of field aa still contain the value from the previous record. Therefore, when the subtotal copies of ab are calculated, the value from the previous record is totalled again. (See figure 5 for a detailed explanation.) The only time that this happens is when you have a statement in the form of "aa = aa + something" such as in line 3 of figure 4, and then use field aa to calculate a total, such as in line 4. If this is the case, you must make sure that the field is always assigned a value before adding to it, since this will cause all copies of the field to contain the same value. In this example, changing line 1 to read aa(10,.2)="0" would accomplish this. Had the assignment in line 2 been unconditional, there would have been no problem. Since the result is that ab totals aa, which when not cleared is itself a total, I usually call this problem "totalling totals." === figure 4: Totalling totals === 1 If: Then: aa(10,.2) 2 If: quantity1 gt "0" Then: aa = price1*quantity1 3 If: quantity2 gt "0" Then: aa = aa + price2*quantity2 4 If: Then: ab(10,.2,g) = tot(aa) - to fix - 1 If: Then: aa(10,.2) = "0" === end figure === === figure 5: Sample data for figure 4 == record quant1 price1 quant2 price2 desired value ====== ====== ====== ====== ====== ============= 1 10 5 1 6 (10*5)+(1*6) = 56 2 0 0 5 7 (5*7) = 35 3 5 6 2 4 (5*6)+(2*4) = 38 Total: 56+35+38 = 129 aa aa ab ab quant1 price1 quant2 price2 detail break detail break ====== ====== ====== ====== ====== ====== ====== ====== 0 0 0 0 record 1: 10 5 1 6 0 0 0 0 line 2 is true: store 50 into field aa 50 50 line 3 is true: add 6 to field aa 56 56 line 4 is true: add field aa to field ab 56 56 record 2: (detail level of non-global field aa is cleared) (note that break level aa retains previous value) 0 0 5 7 0 56 56 56 line 2 is false: field aa unchanged 0 56 line 3 is true: add 35 to field aa (note that break level aa is not incorrect) 35 91 line 4 is true: add field aa to field ab 91 147 record 3: (detail level of non-global field aa is cleared) (note that break level aa retains previous value) 5 6 2 4 0 91 91 147 line 2 is true: store 30 into field aa 30 30 line 3 is true: add 8 to field aa 38 38 line 4 is true: add field aa to field ab 129 185 Because the break level field aa was never cleared while processing record 2, its previous value of 56 was totaled twice, resulting in a difference of 56 between the actual and calculated totals. Had aa not been cleared in record 3, the 35 from record 2 would have been totaled a second time, and the 56 from record 1 would have been totaled a third time. === end figure === Arrays The next most common problem caused by forgetting the rules is similar to the totalling of totals problem, except that instead of using the field as part of a total, you use it as an array subscript. The cause and reasons are the same, but the symptoms are quite different, and even more confusing to someone who has forgotten the rules. Consider the code in figure 6. This code is supposed to keep track of the totals for the values for each month, which are stored in 12 separate fields, starting with field 20. Stepping through the debugger on the second record, you examine field z before executing line 3 and see that its value is 1. You then step through line 3, only to be given an error that the subscript of 13 is out of range. Why is filePro trying to use a subscript of 13 when you just examined field zz and saw that its value is 1? Although the symptoms are different than the first example, this situation is actually another example of the totalling of totals problem. Because field zz is never explicitly given a value, the break-level copy retains its value from the previous record. After processing the first record, this value will be 12. Line 2 then increments it to 13, although the detail-level copy is correctly incremented to 1. When examining fields in the debugger during normal output processing, the debugger displays the value of the detail-level copy. When the detail-level copy of the totals array is calculated (because the array is aliased to dummy fields, it inherits the property of multiple break levels) the detail-level copy of zz is used. However, when the break-level copy is calculated, the break-level copy of zz, which is now 13, is used the in calculation. This is the value which is displayed in the error message. By making the changes shown in the bottom of figure 6, you force an explicit value into all copies of zz, and you eliminate the problem. === figure 6: Totalling totals redux === -- wrong -- 1 Then: dim totals(12)(10,.2):aa ; dim months(12):20 loop: 2 Then: zz(2,.0) = zz + "1" 3 Then: totals(zz) = totals(zz) + months(zz) 4 If: zz lt "12" Then: goto loop -- right -- 1 Then: dim totals(12)(10,.2):aa ; dim months(12):20 2 Then: zz(2,.0) = "1" loop: 3 Then: totals(zz) = totals(zz) + months(zz) 4 If: zz lt "12" Then: zz = zz + "1" ; goto loop === end figure === === figure 7: What is happening in figure 6 === zz zz detail break ====== ====== 0 0 record 1: line 2: add 1 to zz: 1 1 line 3: totals[1] = totals[1] + months[1] line 4: true line 2: add 1 to zz: 2 2 line 3: totals[2] = totals[2] + months[2] line 4: true ..and so on... line 2: add 1 to zz: 12 12 line 3: totals[12] = totals[12] + months[12] line 4: false record 2: (detail level of zz is cleared, break level retains value) 0 12 line 2: add 1 to zz: 1 13 line 3: For detail level totals[1] = totals[1] + months[1] For break level: totals[13] = totals[13] + months[13] *** error *** === end figure === Aliases There is one last thing to keep in mind when writing output processing: aliases. Because you are able to set an alias to represent any field, and change which field at any time (unlike aliased arrays, which cannot change which fields they are aliased to), they do not have the property of multiple copies, even when aliased to a dummy field. See figure 8 for an example. Notice that field aa, which was not referenced through an alias, functions as expected. However, field ab, which was referenced only through the alias, is blank on the subtotal line. This is because the alias only referenced the detail-level copy of ab, so the break-level copies of ab were never assigned a value. === figure 8: Aliases === 1 If: Then: aa(5,.0,g) ; ab(5,.0,g) ; alias:ab 2 If: Then: aa = aa + "1" ; alias = alias + "1" Record: 10 aa is 1 ab is 1 Record: 11 aa is 2 ab is 2 ======================================================= Subtotal aa is 2 ab is ======================================================= Record: 7 aa is 3 ab is 3 Record: 8 aa is 4 ab is 4 Record: 9 aa is 5 ab is 5 Record: 12 aa is 6 ab is 6 Record: 13 aa is 7 ab is 7 ======================================================= Subtotal aa is 7 ab is ======================================================= Record: 4 aa is 8 ab is 8 Record: 5 aa is 9 ab is 9 Record: 6 aa is 10 ab is 10 ======================================================= Subtotal aa is 10 ab is ======================================================= Record: 1 aa is 11 ab is 11 Record: 2 aa is 12 ab is 12 Record: 3 aa is 13 ab is 13 ======================================================= Subtotal aa is 13 ab is ======================================================= Grand total aa is 13 ab is ======================================================= === end figure === Summary One of filePro's unique features is its ability to keep track of seperate totals for each break level without any special programming on your part. Because this feature works "behind the scenes," it is possible to forget that it is going on, or to not fully understand the details of exactly how filePro accomplishes this. By understanding how dummy fields, aggregate functions, subtotal break levels, arrays and aliases work together in output processing, writing complex reports will be less frustrating and time consuming. Barring typographical errors, your reports will generate correct results the first time you run them.