I recently was rereading some Tek-Tips.com FAQs and spotted a FAQ by synapsevampire that I didn’t remember. It had to do with creating a combined list of detail values that can print as a single string. I have a formula for this on my site, but it happens WhilePrintingRecords, which means you can only display the combined values at the bottom of the report. With this method you populate an array during the WhileReadingRecords step of report processing. Because this array is complete before printing starts it can be displayed anywhere.
It was a clever technique, so I did some experiments to find some other real-world applications. This week I found a great one. You can use it to print an accurate sum of a column of values that has duplicates (a ‘distinct sum’) and display the sum at the beginning of the report. I always teach my advanced students to solve this problem with a running total, but this requires that the duplicates be grouped together and that the total be displayed at the end of the report. With this new method neither of these restrictions apply. The only restriction is the size of the array, which can’t be over 1000 elements in versions 9 – 12.
It requires only two formulas that are below. Put both of these formulas in your report header and replace the two fields inside the ‘build’ formula the appropriate fields in your report. The field in the “Amt” line is the field that you want to total. The field in the “Key” line is the field that determines if this amount is a duplicate – the customer, product, etc that is associated with the amount. You don’t need to change the second formula at all.
//Build
WhileReadingRecords;
Local StringVar Key := "" & ;
Local NumberVar Amt := ;
StringVar array Keys;
NumberVar Array Amts;
if not(Key in Keys) then
( redim preserve Keys [count(Keys)+1];
redim preserve Amts[count(Keys)+1];
Keys[count(Keys)]:= Key;
Amts[count(Keys)]:= Amt );
Amts [1]
//Display
WhilePrintingRecords;
NumberVar Array Amts;
Sum (Amts)
I am still experimenting with other uses and will probably include some examples in volume 5 of my Expert Techniques series. If you would like to understand how these formulas work you could read the section on array variables in my Expert’s Guide to Formulas, or you could call me for a private lesson on using arrays.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)
Subscribe RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.