Opening Balance with running totals on debits and credits 1

[kenleyc]

I am trying to create a Statement of Account report in Crystal 8.5.
I have the opening balance and need to add the Debits and subtract the Credits, showing the running balance.
I have selected only the records required based on a date range parameter.

Opening Balance 100.00
___________________________________________________________

Date Debit Credit Balance
08/05/2004 50.00 150.00
08/10/2004 75.00 75.00
___________________________________________________________

Ending Balance 75.00

The opening balance is coming in on the first record.
The closing balance is coming in on the last record.

How can I access the opening balance for calcualating the running balance in the detail section?

I believe a shared variable may work but not sure how to set this up.

Any ideas would be appreciated.
Thanks a Bunch

 

[Madawc]

A variable would work - it doesn't need to be shared unless you are using a subreport.

With Crystal 8.5, it is quicker to use running totals or summary totals. In your cases, right-click on both the credit , select Insert and then Running Total. Create @credit. Do the same for debit, to create @debit.

Do one more running total, @OpeningBal, that picks up the opening balance, just for the first record. Running totals include various controls and you should be able to specify that it is added just for the first record in a group, or just once per group.

For each line, the balance should be the sum of @OpeningBal, @credit and @debit. Do a little trial-and-error which will also teach you about Crystal. Maybe make an extra detail section below the section that the users will see on the final report. See what Crystal is doing with your work fields, which will highlight any errors or faults.

Madawc Williams (East Anglia)

 

[vidru]

Are the transaction details (Debits/Credits) in a subreport? That would be the only reason you'd need to use a Shared variable to access the Opening Balance.

Create a formula (@Amount) to get the amount of each transaction:
{Table.Debit} - {Table.Credit}

Create a running total field (#TransAmount) on the @Amount formula that evaluates for each record, and set the Reset accordingly.

Finally, create another formula (@Balance) to show the updated balance after each transaction:
{Table.OpeningBalance} + {#TransAmount}

If you are indeed dealing with a subreport, you can avoid using a Shared variable by linking the Opening Balance field to the subreport. Let Crystal create its default parameter (?Pm-Field), and uncheck 'Select data in subreport based on field'. The only modification you'd need to make to the above formulas would be the @Balance formula. Instead of {Table.OpeningBalance}, you'd use the Crystal-generated OpeningBalance parameter.

-dave

 

[mbarron]

Create the following formulas:

OpeningBalance - this one would go in the same band as your current opening balance field (it can either be suppressed or replace your current field):

whileprintingrecords;
numbervar balance:={your.opening.balance.field}

RunningTotalField - this one goes in the same band as your credit/debit fields:

whileprintingrecords;
numbervar balance:=balance - {credit.field}+{debit.field}








Mike