Crystal Reports Running Totals problem.

[Former Member]

I am designing  a report in which a Running Balance column adds itself to Debit and subtracts the answer from Credit column on every row. Here in this example I have started calculating from 1st of January. In this case, my Running Balance column will have zero.

Now my client wants to have a look at records from 17 January to the end. In that case, my cumulative balance will NOT be zero at first but instead it will calculate cumulative balance for all the dates before 17 January, and present it as the starting balance for 17th of January. I am stuck at this point. How to add all the record before 17th of January and show their Total in a single row. I will be grateful for any help. I am attaching a tabular description below.

Sr No.	Date	Credit	Debit	Running Balance
1	01-01-12	0	10000	10000
2	01-01-12	5000	0	5000
3	01-01-12	0	10000	25000
4	01-01-12	2500	5000	28500
5	05-01-12	2000	10000	36500
6	05-01-12	5000	0	31500
7	17-01-12	2000	4000	33500
8	17-01-12	1000	0	32500
9	18-01-12	0	5000	37500
10	19-01-12	0	5000	42500
			Cumulative	42500

[former_member260594]

Hello Salman,

If the date this is breaking on is going to be constant or fairly constant then another approach might be to use Specified Order in your grouping.

So add a group on your date field,

- In the group expert go to options

- change the order from the default ascending order to specified order

- add a new named group where the date field is < than Jan 17

- add another named group where the date field is <= Jan 17

This will group everything prior to Jan 17 into 1 instance and Jan 17 and newer in another. You should then be able to get your total prior to Jan 17.

Graham

[ido_millet]

Use a subreport and pass the starting balance to the main report via a shared variable.

[Former Member]

But Ido, how can I be sure that the sub-report will be evaluated before the main report?

[ido_millet]

Place the subreport in a section above the section where you need the result from the subreport.  For example, in the report header.

[Former Member]

In that case Ido, I am only getting the first calculation of running balance and not the last. That is to say, in the sub-report, it correctly calculates the running balance for dates prior to the required date, but the it only shows the running balance for 1st of January and not 16th of January. I have tried to experiment with OnLastRecord but it doesn't help.

[Former Member]

Hi,

As per Ido's suggestion, this is achievable using sub report. I will post the steps once you are able to clarify on the calculation of Running Balance..

for e.g: look at record number 3:

3

01-01-12

0

10000

25000

Should it be 5000(Previous R Bal) + 10000(Current Debit) - 0(Current Credit) = 15000(Current R bal) instead of 25000

Since, in record number 2 (given below) it was:

2

01-01-12

5000

0

5000

10000(Previous R Bal) + 0(Current Debit) - 5000(Current Credit) = 5000 (Current R Bal)

Also there is slight confusion at record number 4:

4

01-01-12

2500

5000

28500

since 25000 + 5000 - 2500 = 27500 (OR its just a typo)

Thanks,

Prathamesh

[Former Member]

Hi,

Please download "Crystal Reports Running Totals problem.zip" from http://www.sendspace.com/file/cmyoue

Description:

1. Created Start Date parameter >> linked it to sub report default parameter >> changed the record selection of sub report to {Sheet1_.Date} < {?Pm-?Start Date}

2. Created Running Balance formulas in both- main and sub reports. Used shared variable to collect last value from sub report and pass it to main report Running Balance formula.

Thanks,

Prathamesh