Retrieving Constant Data from One Record 1

[rmduser]

I have a CR 7.0 report to generate a payment certificate. The report has a cost distribution subreport which uses a application-generated local .db table. The .db table has totals for 3 accounts plus the first record always has a total that must be distributed proportionally into the 3 other totals.

I'm trying to create a formula to give the adjusted totals.

Can anyone help me figure out how to select the data? I can't seem to use a SQL query on this report (presumably because the data doesn't come from an ODBC database).

 

[kenhamady]

What type of table is the subreport reading?
How many records are in it, just 4? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[rmduser]

Yes, the source database is only 4 records with 4 fields: Contract No., CertificateID, Acct No. and Total.

The "general" Acct No., whose total I want distributed, is a string identified by 5 zeros at the front on the string.

 

[kenhamady]

Bring all 4 records into the report.
Create a conditional field called OH to identify the one to be prorated:

If {acct} = "00000xxxx"
then {Total}
else 0

Create a conditional field called NotOH for the others:

If {acct} <> &quot;00000xxxx&quot;
then {Total}
else 0

Then put the following formula field on the details of your report, which will prorate your OH into the 3 needed portions:

{@notOH}/Sum ({@notOH}) * Sum ({@OH})


Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[rmduser]

Thanks very much, Ken. It works perfectly!