Using Crystal Do Loops for Repeating Groups

Proper database design specifies that tables should not have repeating groups. The real world, however, often does not comply with these rules. Here is a way to deal with the situation.

The Setting

I was consulting with the comptroller of a large non-profit organization in Washington, DC. The firm was using DataPro accounting. A key table in DataPro presented the monthly balances for each general ledger account in the following format:

Field Name Type Description
ACCT_NO Character Account number
AMT01 Number Balance for the first month
AMT02 Number Balance for the second month
àà.
AMT12 Number Balance for the twelfth month

My assignment was to create a report that prompted the user for the numeric value of the last month in the report and prepare a report showing the sum of the monthly balances "to date" for each account.

The Solution

I was able to do it in Crystal Version 8.5 using the following expression:

numbervar kount ; //line 1
numbervar actual := 0; //line 2
numberVar array amts := [
{COMPGL01.AMT01},{COMPGL01.AMT02},{COMPGL01.AMT03},
{COMPGL01.AMT04},{COMPGL01.AMT05},{COMPGL01.AMT06},
{COMPGL01.AMT07},{COMPGL01.AMT08},{COMPGL01.AMT09},
{COMPGL01.AMT10},{COMPGL01.AMT11},{COMPGL01.AMT12}]; //line 3
FOR kount := 1 TO {?Get Month} STEP 1 DO //line 4
(actual := actual + amts[kount]); //line 5
actual //line 6




Explanation


Line Explanation
1 A variable of type number is created. This will be used in the FOR..TO..DO loop.
2 Another number variable is created and initiated as zero. This will be used to accumulate the values.
3 We create the numeric array AMTS and enter the twelve values that can possibly be accumulated.
4 The FOR..TO..DO loop is specified. The STEP clause is not necessary since the default step is 1. The loop will work on the next line which is part of the statement. Notice that there is no semi colon at the end of this line. The loop starts at one for each record and continues until the number entered by the user. The loop increments automatically.
5 The statement that the FOR..TO..DO loop will operate on. In this case, there is only one operation. If there were more, they would be separated by semi colons within the parentheses. Each time through, we add the value in the element of the array specified by the subscript kount to the value already accumulated.
6 The total is reported out.



This is an excellent example of the power of this new operator.