Can two separate reports be combined?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=143
Printed Date: 26 May 2024 at 9:38pm
Topic: Can two separate reports be combined?
Posted By: jodyh59
Subject: Can two separate reports be combined?
Date Posted: 22 Jan 2007 at 9:02am
|
I have very little knowledge of Crystal and pretty much just muddle through it, so please bear with me. I have two crystal reports, one based on an Access database and one based on a MAS200 database. I need to combine the information from these two reports into one. The report based on the Access database has calculations based on CARs issued to a vendor and how they've affected the company. The report based on the MAS200 database shows the number of shipments a vendor made to us and the total number of parts. I need to combine these into a "Vendor Scorecard", plugging the correct numbers into the correct places. The MAS200 database and the Access database have one field (vendor #) that will link. It's possible I may be able to add another field (vendor type) to the Access database to provide a second link. In my effort to provide as much information as possible in order to get as much help as possible, I've pasted in the SQL queries for each of these reports. Would I be better off to try to create this in Access? I wanted to use Crystal so it could be accessed through MAS200. Any help will be greatly appreciated. Thank you.
Jody
 Report #1 (MAS200 Database)
SELECT POM_RecptHistoryLine."PurchaseOrderNumber", POM_RecptHistoryLine."ItemNumber", POM_RecptHistoryLine."ReceiptQty", POP_RecptHistoryHeader."ReceiptDate", POP_RecptHistoryHeader."VendorNumber", POP_RecptHistoryHeader."VendorName" FROM "POM_RecptHistoryLine" POM_RecptHistoryLine, "POP_RecptHistoryHeader" POP_RecptHistoryHeader WHERE POM_RecptHistoryLine."PurchaseOrderNumber" = POP_RecptHistoryHeader."PurchaseOrderNumber" AND POM_RecptHistoryLine."ReceiptType" = POP_RecptHistoryHeader."ReceiptType" AND POM_RecptHistoryLine."ReceiptNumber" = POP_RecptHistoryHeader."ReceiptNumber" AND POM_RecptHistoryLine."RequiredDate" = POP_RecptHistoryHeader."RequiredDate" AND POM_RecptHistoryLine."UseTax" = POP_RecptHistoryHeader."UseTax" AND POM_RecptHistoryLine."WarehouseCode" = POP_RecptHistoryHeader."WarehouseCode" AND POM_RecptHistoryLine."ReceiptAmount" = POP_RecptHistoryHeader."ReceiptAmount" AND POP_RecptHistoryHeader."ReceiptDate" >= {d '2007-01-01'} AND POP_RecptHistoryHeader."ReceiptDate" <= {d '2007-01-17'} ORDER BY POP_RecptHistoryHeader."VendorNumber" ASC, POM_RecptHistoryLine."ItemNumber" ASC Report #2 (Access Database) SELECT A2001_CORRECTIVE_ACTION_TABLE.`CAR #`, A2001_CORRECTIVE_ACTION_TABLE.`DISRUPTION TYPE`, A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED`, A2001_CORRECTIVE_ACTION_TABLE.`PROBLEM TYPE`, A2001_CORRECTIVE_ACTION_TABLE.`SupplierID`, SUPPLIER_ID.`SUPPLIER NO` FROM `2001 CORRECTIVE ACTION TABLE` A2001_CORRECTIVE_ACTION_TABLE INNER JOIN `SUPPLIER ID` SUPPLIER_ID ON A2001_CORRECTIVE_ACTION_TABLE.`SUPPLIER NO` = SUPPLIER_ID.`SUPPLIER NO` WHERE A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED` >= {ts '2006-07-01 00:00:00.00'} AND A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED` < {ts '2006-09-30 12:00:01.00'} ORDER BY SUPPLIER_ID.`SUPPLIER NO` ASC, A2001_CORRECTIVE_ACTION_TABLE.`PROBLEM TYPE` ASC, A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED` ASC |
Replies:
Posted By: blueridge
Date Posted: 23 Jan 2007 at 9:50am
| I think you are able to do what you want. Just add both data sources in the Database Expert and make sure that the Vendor # fields from both sources are linked (on the 'Links' tab in the Database Expert). |
Posted By: veremue
Date Posted: 06 Feb 2007 at 6:25am
|
help me out on this issue too. is it possible to link tbles from different dtabases e.g access and pervasive sql ------------- eddy veremu |
Posted By: jodyh59
Date Posted: 06 Feb 2007 at 6:37am
| Thank you. I was able to successfully link the reports using shared variables. It was a little confusing going back and forth between the reports to share the variables and then use them in the formulas, but it worked out great! Thanks, again. |