Create Running Balance Calc

[icerubes]

[highlight #EF2929][/highlight]Using Crystal 11, ODBC connection to Pervasise Database.

I have created a report that lists transactions for each GL account between user nominated dates.

e.g.
Parameters - Start date and End Date
#Group1 - Account type (Income, Expense etc)
#Group2 - GL Account - PPI Opening Balance (formula) 4837.18

Details
Date Ref Memo DR (formula) CR (formula)
4/7/2013 982573 LASKY ALLOC PPI - 265.00(DR) [highlight #EF2929]4572.18[/highlight] running balance
4/7/2013 982702 LASKY ALLOC PPI -2259.09(DR) [highlight #EF2929]2313.09[/highlight] running balance
12/7/2013 982478 BOWERMAN 24200 720.50(CR) [highlight #EF2929]3033.59[/highlight] running balance
17/7/2013 982505 24215 DEPOSIT 364.46(CR) [highlight #EF2929]3398.05[/highlight] running balance

Closing Balance (formula) 3398.05

It has the opening balance and closing balances calculating via formula ok but I would like to create a running balance total.
e.g. Opening balance 4837.18 - 265 = 4572.18, next line (last balance not opening balance) 4572.18 -2259.09 = 2313.09, next line (last balance not opening balance) 2313.09 + 720.50 = 3033.59

Simply not sure how to construct formula to take last balance line after using the initial opening balance. Appreciate your assistance in this regard.

Existing formula's below. Thanks in advance.


@Opening balance formula = if ({Date} < {?Start Date}) then {Amount} else 0

@Closing balance formula = if ({Date} >={?Start Date} and {Date} <= {?End Date}) then {Amount} else 0

@Debit tran formula = if( {COA type} in ["ACC PAY", "C LIAB", "L LIAB"]) and {Amount} <0 then {Amount} else
if ({COA type} in ["ACC REC","BANK", "C ASSET", "F ASSET", "EQUITY"]) and {Amount} >0 then {Amount}

@Credit tran formula = if( {COA type} in ["ACC PAY", "C LIAB", "L LIAB"]) and {Amount} >0 then {Amount} else
if ({COA type} in ["ACC REC","BANK", "C ASSET", "F ASSET", "EQUITY"]) and {Amount} <0 then {Amount}

 

[pmax9999]

The simplest way would probably be a Variable to calculate display the running balance. You will need 2 formulas, something like the following.

In the Group Header, use something like:
[Code {@VarRESET}]
WhilePrintingRecord;
Global NumberVar RB := {@OpeningBalance};
[/Code]

In the Details section, use something like:
[Code {@VarSET}]
WhilePrintingRecord;
Global NumberVar RB;

RB := RB + {@CreditFormula} - {@DebitFormula};

RB
[/Code]

Hope this helps.

Pete