Running totla formula

[amrnewman]

Dear all

I am using CRXI on a SQL2005 db.

I am attempting to produce a tenant rent statement that should show th eopening balance, credit and debit transactions with a running total and a closing balance.

The opening balance is fine and is a field called opening_bal. This works for the primary opening balance. his field is repeated with an opening balance for each weekly period where there is a bedit transaction (e.g. their rent charge). There is no open balance for credit transactions e.g. when they make a payment. Therefore my runniung balances do not add up. What I want is:

Opening Balance: £100.00 In credit

Transaction Debit Credit Balance
Period type

Week 1 Rent debit -£10.00 £90.00
Week 2 Rent debit -£10.00 £80.00
Week 2 Payment £100.00 £180.00
Week 3

Because there is no opening balance for a week 2 credit, I have no figure to add the £100.00 to as a running total.

Is there a way to produce this running total? The standard running totla does not work.

Not sure if I explained this very well, so all help gratefully received!

 

[CrystalizeCanada]

Assuming you have two fields - one for credits and one for debits I would first create a formula called @DebitAndCredits to make it easier to work with one formula rather than two fields.
FORMULA1
//DebitAndCredits formula
if isnull(debitfield) then creditfield else debitfield
Then you can create your own manual running total
FORMULA2:
//Manual Running total
//Assuming your fields are numbers and not currencies
whileprintingrecords;
numbervar rt;
numbvervar counter:=counter+1;
if counter=1 then rt:=openingbalfield+@debitAndCredits else
rt:=rt +@debitAndCredits
You may need to reset your variables if your report is showing more than one period or tenant
FORMULA3:
//Reset counter and running total
//this formula is place in the appropriate group header
whileprintingrecords;
numbervar rt:=0;
numbervar counter:=0;

Gordon BOCP
Crystalize

 

[amrnewman]

Hi - this looks good and sounds like it should work. I am not in office today or Weds, so I'll check Thurs and let you know how I get on. Thanks for the swift reply!

BW, Ade

 

[amrnewman]

Hi Gordon

Has taken me a little while to get back to this. I am pleased to say this has worked.

The final thing I need to do is to display the final RT amount as a closing balance. The manual running total is in the detail section, the reset is in group header two, and the closing balance is in group footer two.

I have tried dropping the formula in to the footer but of course it adds the previous debitcredit amount.

Any suggestions greatfully recieved.

A

(Forgotten so much Crystal now...)

 

[lbass]

Just place the following formula in GF2:

whileprintingrecords;
numbervar rt;

-LB

 

[amrnewman]

That simlpe huh! I feel really dumb now. Thanks - it worked.

BW, A