Calculate based on previous group sum and current group sum 3

[nandand]

Hello Experts,

SQL Server, CR 8.0, ODBC

I'm creating a summary executive report for accounts receivable trends. The report is grouped by month (using a date/time field) with the sums based on formulas that are in the detail section (which is hidden).

Report currently looks like this:

Month Sales Cash Balance
Jan XXX XXX XXX
Feb etc...
Mar etc...

I would like to add a column for Sales Two Months Prior and then be able to perform a calculation on that field. So the March line would include the January sales sum. Is there any hope?

 

[synapsevampire]

You could use the 3 formula method to maintain a value for 2 months ago for each grouping:

Group Header formula:
whileprintingrecords;
numbervar CurMonth:=0;

Details:
whileprintingrecords;
numbervar CurMonth:=CurMonth+{table.sales});

Insert a second Group footer section and in the 1st group footer use the following to use the 2 months ago data:

whileprintingrecords;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder(month({table.field}),2) = 0 then
LastEvenMonths
else
LastOddMonths

In the second group footer use the following to reset it:
Group Footer formula:
whileprintingrecords;
numbervar CurMonth;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder(month({table.field}),2) = 0 then
LastEvenMonths := CurMonth
else
LastOddMonths := LastOddMonths

If this doesn't resolve, please elaborate, and try to use example data and expected output rather than showing a mock up of the report.

-k

 

[lbass]

SV--I learned something (*), but I think your last formula should be:

whileprintingrecords;
numbervar CurMonth;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder(month({table.field}),2) = 0 then
LastEvenMonths := CurMonth
else
LastOddMonths := CurMonth

-LB

 

[nandand]

Thanks synapsevampire and lbass. What you've given me makes sense. I'll give it a try tomorrow and give feedback. Thank you so much for the responses.

 

[synapsevampire]

Ooops, right LB, thanks for straightening it out.

Perhaps I should test or at least proofread

-k

 

[nandand]

Your solution works. Thank you so much.