Linking data between two types of data sources

[SBLatta]

Help!

I'm trying to create a report which compares the ending inventory of two user specfied months. I have that part working perfectly. Every month, we take a snapshot of our ending inventory data and append it in an SQL database. Now, the users want the report grouped by a field that isn't included in the snapshot and can't be easily added, so I need to link the Itemkey field in my report to the Itemkey field in our accounting software's item master file and pull out the Productkey from there that matches. I tried doing it as a separate query linked with a left outer join, but it crashes Crystal every single time I try to pull data. I'm hoping I can add the field to my SQL command, but I'm not getting the syntax right using a fully qualified name.

My query as currently written, pulling only from the SQL server datasource:


SELECT "Inv_Adj"."Countdate", "Inv_Adj"."Itemkey", "Inv_Adj"."Location",
"Inv_Adj"."Freezonhand", "Inv_Adj"."Unitcost", "Inv_Adj"."UOM",
"Inv_Adj"."SiteID", "Inv_Adj"."Newonhand"
FROM "CompanyA_CORP"."dbo"."Inv_Adj" "Inv_Adj"
WHERE "Inv_Adj"."SiteID"='Plant1'
ORDER BY "Inv_Adj"."SiteID", "Inv_Adj"."Location", "Inv_Adj"."Itemkey",
"Inv_Adj"."Countdate" DESC

That works fine. When I try to add the ProductKey field from another datasource, it fails to run:


SELECT "Inv_Adj"."Countdate", "Inv_Adj"."Itemkey", "Inv_Adj"."Location",
"Inv_Adj"."Freezonhand", "Inv_Adj"."Unitcost", "Inv_Adj"."UOM",
"Inv_Adj"."SiteID", "Inv_Adj"."Newonhand", "pfwCompanyA"."Productkey"
FROM "Ortec_CORP"."dbo"."Inv_Adj" "Inv_Adj" LEFT OUTER JOIN [odbc;dsn=pfwCompanyA;database=pfwCompanyA]."INMAST" ON
"Inv_Adj"."Itemkey" = "INMAST"."Itemkey"
WHERE "Inv_Adj"."SiteID"='Plant1'
ORDER BY "Inv_Adj"."SiteID", "Inv_Adj"."Location", "Inv_Adj"."Itemkey",
"Inv_Adj"."Countdate" DESC

The second data source is an odbc connection on a completely different server from the SQL database. The dsn is correct, and I can query it by itself all day long. I just can't seem to find a way to link the two data tables together in a way they works and doesn't cause Crystal to crash to desktop.

 

[Turkbear]

Hi,
Can you create a view in your SqlServer database for the accounting data you need from the other database?

Also, I do not recall seeing this structure in a CR created query:

LEFT OUTER JOIN [odbc;dsn=pfwCompanyA;database=pfwCompanyA]."INMAST" ON "Inv_Adj"."Itemkey" = "INMAST"."Itemkey"

Usually the table is added to the database explorer and linked there.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SBLatta]

Yeah, I added that line by hand. As I said in my original post, I tried initially to do it by adding the second table in database explorer and linking it, but attempting to do it that way causes CR to crash to desktop on data refresh every single time.

I would prefer to do it that way if I could find a way to do it without it crashing.