passing values from sub to main report 2

[shahinrep]

dear all i have table sales with (inv number , invoice total ) second table CRVocher( Invno, Payment)

i create report for the sales

inv# Invamount
---- --------

and i create query to sum(Payment) with subreport
and i link them
every thing is ok


the quastion how can i make subtract between the sales amount and my subreport sunm of payment

i mean

sales inv number 1 amount 15000 $ <-----main report

CrVouchers

Invno 1 sum od payments = 1000$ <----sub report

i need the report with this


inv no amount paid balance
------- ------ ---- -------
1 15000 1000 14000


note that
paid is sub report linked with the main through the inv no and its query



useing crystal report 7
vb6 program
access database

any advice thanx for all

 

[HowardHammerman]

First, are you sure you need a sub-report? Why not just link the voucher table to the sales table in the main report? You would then have multiple detail lines per sale but you can supress the detail section snd simply calculate the subtotal at the invoice group level.
---------
However, if you do use a sub-report here is how you do it:

1) In the sub-report create a summary which is the total of all payments at the grand total level.
2) Create a formula field in the sub-report with the following expression:
shared numbervar srtot := sum({crvoucher.payment})

note: the above assumes that {crvoucher.payment} is numeric. If it is a currency type use "currencyvar" rather than "numbervar". Don't forget the ":=". The colon is very important.

3) Place the formula field in the report footer section of the sub-report. Supress ALL the sections including the report footer section. There is no need for any grouping in the sub-report.

4) In the main report create two formuals:

Formula #1, Name: reset
Expression:
shared numbervar srtot := 0

Formula #2, Name: payments
Expression:
shared numbervar artot

5) Place formula #1 in a group header. You can change the color to white so it does not show.

6) Place formula #2 in the detail or group footer section for each invoice.

7) Modify you formula to show to balance to use @payments,
that is formula #2.

I hope this helps.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[shahinrep]

thank you so much but i did all the things you told me but always payments = 0

 

[lbass]

Assuming you have a group on invoice number in the main report, place the reset formula in GH_a, place the subreport in GH_b, and place your invoice fields in the GH_c section. Format GH_b to underlay following sections so that the payment field aligns with the main report fields in GH_c. Then add a formula {@balance} to be placed in the GH_c:

whileprintingrecords;
numbervar bal;
shared numbervar srtot;
{table.invamount}-srtot

The key thing is that the reset formula must be in a section above the one containing the sub, and the shared variable used in the calculation must be in a section below the one containing the sub.

You should also change the reset formula to:

whileprintingrecords;
numbervar bal;
shared numbervar srtot;
if not inrepeatedgroupheader then (
bal := 0;
srtot := 0
);

-LB

 

[HowardHammerman]

Once again Lbass has come to our assistance.

Good work!

Is it always necessary to use "whileprintingrecords" ?
I have heard both opinions.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

Shared variables always occur "whileprintingrecords", but for global variables like bal, you would need to specify whileprintingrecords so that the values accrue across records.

-LB