VFP8 Reports, Calculate Running Balance 1

[mikemck7]

I need to build an account statement report in VFP8 from two tables; CUST and TRNS. The table structures are as follows:

CUST: CUSTID (C), NAME (C), BALN (N)

TRNS: CUSTID (C), DATE (D), CHRG (N), PYMT (N)

The tables are related on the key field CUSTID. BALN contains the balance as of the previous report period.

The detail band contains DATE, CHRG, PYMT and a field for the running balance calculation, so that the output looks something like this:
[tt]
DATE DESCRIPTION CHG PMT BAL
11/01/05 Previous Balance 504.54
11/17/05 Payment Recvd 370.54 134.00
11/30/05 Monthly Fee 360.00 494.00
[/tt]
How do I get the running balance field calculated properly? Thanks.


Mike

 

[crewchiefpro6]

Create report variables to hold the sum of all payments, then a report variable to hold the sum of all charges. You can create an additional variable to hold the subtraction.

Make sure they sum() the field contents and make sure they reset on each customer band.

We even create variables to hold the number payments and charges and we average them also.

If you need specific help with this setup then let us know. If you can not get it then I will post some of what we do.

Don




Don Higgins

http://www.crewchiefpro.com

 

[mikemck7]

Don,
Sounds like what you suggest is for getting the sum of all charges and payments but what I really need to know is how to get the incremented/decremented amount for the balance column of each line item.

Using my example, I need to pick up the previous balance of $504.54 from CUST.BAL then I need to subtract from it the payment amount in TRNS.PMT, display the new balance of $134.00 then add TRNS.CHG to display $494.00 and so on...

In other words, how do I get the variable for the accumulating balance to increase/decrease as each detail line is printed?
Mike

 

[alvechurchdata]

In other words, how do I get the variable for the accumulating balance to increase/decrease as each detail line is printed?

I think you're going to have to write some code to preprocess the data. You can use Report Variables to remember the value from the previous line and make it available for subtraction but it's a flakey solution. It's much safer to run a SCAN loop beforehand and get all the calculations done before you give it to the Report Writer.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[TamarGranor]

Actually, I think a report variable should do exactly what he wants. Set it to reset on customer. Initial value is Cust.Baln and Value to store is:

itself + Chrg - Pymt

Should do the trick.

Tamar

 

[mikemck7]

Set it to reset on customer

Tamar,
This was the missing link for me. Its now working the way I want it to. Thank you very much.
Mike