Two Unique Tables/Single Report 2

[mawh]

I use CR XI. My data sources are Foxpro tables accessed via ODBC. I have two unique tables which do not link.
One is the AP check file; the other is the cash receipts file. I need to do a report in which I sum the total of the checks issued for a period and the total of the cash received for the same period. Then I need to do another calculation based on the result of the two formulas. Because I need to do an additional calculation, it seems a subreport is not appropriate. Any suggestions?

 

[lbass]

You can use a subreport for one and then set up the subreport value that you will use in the calculation as a shared variable, e.g.:

whileprintingrecords;
shared numbervar subamt := sum({table.amt});

Place the shared variable formula in the subreport footer. Place the subreport in a report header section. Then use the report footer for the calculation (the shared variable can only be referenced in a section below the one in which the subreport executes. To do the calculation, create a formula like:

whileprintingrecords;
shared numbervar subamt;
subamt - sum({maintable.amt});

If your data types are currencies, change the variable from numbervar to currencyvar.

-LB

 

[IdoMillet]

Another option is to create a command as the data source with a UNION ALL select statement. By including a column to indicate the type of record ("check" or "cash"), you can easily create conditional totals to sum each separately.

SELECT check_date, check_amount, "check" as TYPE from CHECKS
UNION ALL
SELECT cash_date, cash_amount, "cash" from CASH_RECEIPTS

This may not only perform slightly faster but also form the basis for other reporting needs...

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[mawh]

Ido -
I tried as you suggested, here is my command statement:

SELECT `apchck01`.`checkdate`, `apchck01`.`aprpay`,"CHECK" as TYPE
FROM `apchck01` `apchck01`
UNION ALL
SELECT `arcash01`.`dtepaid`, `arcash01`.`paidamt`,"CASH" as TYPE
FROM `arcash01` `arcash01`

However, when I go to the field explorer to choose my fields, only the fields from apchck01 (checkdate and aprpay) are available. None of the fields from arcash01 show in the list.

Maybe you can see if I have done something wrong. I appreciate your help.

 

[synapsevampire]

mawh: The field names are all names the same thing now because it's ONE dataset.

I suggest that you modify it slightly:

SELECT `apchck01` 'MyDataSource' ,
`apchck01`.`checkdate`, `apchck01`.`aprpay`,"CHECK" as TYPE
FROM `apchck01` `apchck01`
UNION ALL
SELECT `arcash01` 'MyDataSource' , `arcash01`.`dtepaid`, `arcash01`.`paidamt`,"CASH" as TYPE
FROM `arcash01` `arcash01`

Now you have an additional column called MyDataSource which states where the data came from.

-k

 

[IdoMillet]

Hi K,

That was the purpose of the TYPE column.

Cheers,
- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Ahhh, missed that part, Ido, my apologies.

-k