Loop Formula

[montrealdevils]

I'm Using
Crystal 9.0
SQL 2000

We sell service contracts with our product that vary in length of time of coverage. It is always sold in "monthly" increments. For example you can purchase a 12, 24, or 36 month contract, it can actually be any # of months it doesn't matter.

For forecasting I need to report how much of money we can defer each month over the life of the contract.

For example, we sell a 12 month contract for $12,000 on 8/1/04. I need the report to calculate 1/12 of $12,000 revenue for the next 12 months.

8/1/04 $1000
9/1/04 $1000
10/1/04 $1000
11/1/04 $1000
..... and so on until the number of months in the contract are complete.

The data is being pulled from our invoice table that gives me:

InvoiceNumber InvoiceDate Part # Qty Unit Price
235789 8/1/04 02-006-0 12 $1000

I'm hoping to be able to use a crosstab in the report footer to summarize the monthly totals by part #.

Thanks in advance for any advice you can provide!!

 

[synapsevampire]

Since the data isn't in monthly increments, a cross-tab probably won't work.

As your post indicates, you probably need a loop something like:

//crystal syntax:
whileprintingrecords;
numbervar MonthQty:={table.qty};
numbervar UnitPrice:={table.UnitPrice};
datevar InvoiceDate:= {table.invoicedate};
numbervar x;
stringvar Output:= ""
for x:= 1 to MonthQty do(
Output:= Output+totext(InvoiceDate)+" "+UnitPrice+chr(13);
InvoiceDate := dateadd("m",1,InvoiceDate);
);
Output

This formula would go in the details section assuming that your data example is from the details.

-k