groping totals from previous group to the current group

[inspi]

Oracle 10g and CR 10.

I have a report where in I have grouped the report first by Audit date and then by departments

There is another date in the table called record date.

“Total from previous day” is actually the total that is present from prior business day

“New items” – count of formula (if recordddate = auditdate the 1 else 0) in GF2

“Cleared items” are the one cleared for the audit date - “Total for today” is count of items for the audit. If I run the report today then total for 8-3-2007

For yesterday the data will be

8-2-2007
Department Prev.day New Cleared Total
Dept 1 966 115 -187 894
Dept 2 421 59 -1 479
Dept 3 167 56 -17 206

8-3-2007
Department Prev.day New Cleared Total
Dept 1 894 27 -42 879
Dept 2 479 34 -29 484
Dept 3 206 0 -3 203


I need to some how get the group totals of 8-2-2007 to the next grouping that’s is “Total from previous day(8-2-2007)” as cleared items has to be calculated depending on the previous days totals.

Is there any way to do this?

 

[lbass]

Do you intend to set this report up only for today's date and the previous day's date? Or are would there be any number of dates, with each one referencing the previous one?

-LB

 

[inspi]

There be many number of dates, with each one referencing the previous one

 

[lbass]

I can't work on this now, but I'll take a look at it tomorrow.

-LB

 

[inspi]

Any luck on this yet?

 

[lbass]

I spent quite a bit of time trying to do this with arrays, and was unsuccessful. Maybe someone else could tell you (and me) how to do it with arrays.

An alternative is to use a subreport that captures the data from the most recent date prior to the group date. But how would you get the initial balance? Do you have some method in place for that?

-LB

 

[inspi]

Using subreport I was able to get the previous day total successfull into the main report. But it wont allow me to get the "Cleared Items" as it is the difference of (todays items)-(prev items+new items).

 

[lbass]

Please answer my question about the initial amount.

-LB

 

[inspi]

I didnt have any problem getting the initial balance, I created a formula @prev
if DayOfWeek ((({Table.AUDIT_DATE}) - 1)) = 1 then ({Table.AUDIT_DATE} - 3) else {Table.AUDIT_DATE}-1

Then I linked the main report to the subreport using
{Table.TITLE} = {?Pm-Table.TITLE} and
{Table.AUDIT_DATE} = {?Pm-@prev}.

By this the report fetched the total for previous business day successfully

 

[lbass]

I think you should do the calculations in the subreport where the sub is linked to the main report on department, and on date. In the subreport record selection area, limit the records to the link date and the previous date.

-LB

 

[inspi]

Thanks LB. I couldnt do it in crystal reports. But I created a procedure and inserted a subquery to handle this.
Thanks once again.