Customized Crystal 8.5 Report

[cis12232004]

I am attempting to customize a report in crystal 8.5 but everytime I connect to the SQL7 DB and add the new table my report closes out. I tried to manually add the SQL statement to add the table and field as well but I got an error. I am not well versed in how crystal responsds to SQL so can someone please help. The SQL I manually entered is:

SELECT
Inventory."Descr", Inventory."InvtId", Inventory."invttype", Inventory."Source", Inventory."ValMthd",
ItemSite."QtyOnHand", ItemSite."TotCost",
Snote."sNoteText", POReqDet."AlternateID"
FROM
{ oj ("CableApp"."dbo"."Inventory" Inventory LEFT OUTER JOIN "CableApp"."dbo"."Snote" Snote ON
Inventory."NoteID" = Snote."nID")
LEFT OUTER JOIN "CableApp"."dbo"."ItemSite" ItemSite ON
Inventory."InvtId" = ItemSite."InvtId"} and
{ oj ("CableApp"."dbo"."Inventory" Inventory LEFT OUTER JOIN "CableApp"."dbo"."AlternateID" AlternateID ON
Inventory."Descr" = POReqDet."Descr")
ORDER BY
Inventory."InvtId" ASC

I want to have access to the POReqDet table and the AlternateID field.

Thanks for your help.

 

[lbass]

Is the POReqDet table from a different datasource? If it is from the datasource, then try linking the tables in the linking expert so that the SQL query is automatically generated.

The following clause in your manually generated query doesn't make sense, as the POReqDet."Descr" field is not in either of the tables you are joining:

{ oj ("CableApp"."dbo"."Inventory" Inventory LEFT OUTER JOIN "CableApp"."dbo"."AlternateID" AlternateID ON
Inventory."Descr" = POReqDet."Descr")

-LB

 

[cis12232004]

LB, when I go into Report Expert and try to link to table to the existing report I keep getting an error and crystal report closes. Any ideas what could be causing this? I have used report expert to customize other reports without any problems. By the way, that is the reason I attempted to manually enter the SQL to see if that would generate the data that I am seeking.

 

[GJParker]

cis

should this not be

{ oj ("CableApp"."dbo"."Inventory" Inventory LEFT OUTER JOIN "CableApp"."dbo"."POReqDet" POReqDet ON
Inventory."Descr" = POReqDet."Descr")

ratehr than just saying I got an error, please post the exact error(s)

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

You didn't answer my question about whether the table was from a different datasource. Also, Gary picked up on the same problem that I did in your manually generated SQL.

-LB

 

[cis12232004]

Thanks gentlemen but I finally figured it out. I generated the table using SQL, exported it to Crystal, made some adjustments and then put it in the solomon user folder for use. Worked like a charm.

 

[synapsevampire]

A table can't be exported to Crystal. Please consider answering questions and posting meaningful technical information in future posts as your question was unclear, and your solution impossible.

I considered giving you a star for the least useful post of the day.

btw, in CR 8.5 you'd have to use an ADO/RDO type of connection to paste in SQL, in CR 9 and above you can use a Command object. CR 8.5 and below only allow you to modify part of the SQL (check the Database->Show SQl Query and read the text in the window that pops up).

-k

 

[cis12232004]

Thank you for your comments but my report is working as it should.