Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Can two separate reports be combined? Post Reply Post New Topic
Author Message
jodyh59
Newbie
Newbie
Avatar

Joined: 22 Jan 2007
Location: United States
Online Status: Offline
Posts: 2
Quote jodyh59 Replybullet Topic: Can two separate reports be combined?
    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. 
 
JodyConfused
 
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
IP IP Logged
blueridge
Newbie
Newbie


Joined: 21 Nov 2006
Online Status: Offline
Posts: 7
Quote blueridge Replybullet 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).
IP IP Logged
veremue
Newbie
Newbie
Avatar

Joined: 06 Feb 2007
Location: Zimbabwe
Online Status: Offline
Posts: 4
Quote veremue Replybullet 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
IP IP Logged
jodyh59
Newbie
Newbie
Avatar

Joined: 22 Jan 2007
Location: United States
Online Status: Offline
Posts: 2
Quote jodyh59 Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.