Crystal Reports with Dynamics GP data Report Errors

[#1]

Hello,

I am using CR Professional, Product Type: Full; version 11.0.0895.

Microsoft Dynamics GP Professional 9.00.0281, database: SQL Server, ODBC Driver, and Microsoft SQL Server 2005. Iu2019m on Windows 2000 SP4 workstation. I have already connected to the Dynamics database through ODBC.

Iu2019m trying to replicate our sales orders from Dynamics over to Crystal Reports. I was helped earlier by linking tables. It appeared that it worked correctly but the data kept falling apart. I tried to create a new report with the following setup neither giving me the data I require.

First Report:

SOP60300.itemnmbr> SOP10200.itemnmbr Inner Join Not Enforced

SOP60300.itemnmbr> IV00101.itemnmbr Inner Join Not Enforced

SOP10200.soptype> SOP10100.soptype Inner Join Not Enforced

SOP10200.sopnumbe> SOP10100.sopnumbe Inner Join Not Enforced

SOP10200.itemnmbr> IV00101.itemnmbr Inner Join Not Enforced

SOP10200.itemdesc>IV00101.itemdesc Inner Join Not Enforced

SOP.10200.locncode>IV00101.locncode Inner Join Not Enforced

With this configuration the data is unreliable. Sales order will show duplicate data, will pull over the incorrect customer item numbers/descriptions and will merge several sales order together to show some other customeru2019s item numbers/description on the wrong sales order.

Start Over: selected the four tables cleared links

Second Report:

SOP10200.soptype> SOP10100.soptype/ Inner Join Enforced Both

SOP10200.itemnmbr> IV00101.itemnmbr/ Full Outer Join Enforced Both

SOP10200.itemdesc> IV00101.itemdesc / Full Outer Join Enforced Both

IV00101.itemnmbr> SOP60300.itemnmbr/ Full Outer Join Enforced Both

SOP10200.itemnmbr> SOP60300.custitemnmbr/ Full Outer Join Enforced Both

SOP10200.itemdesc>SOP60300.custitemdesc/ Full Outer Join Enforced Both

SOP60300.custnmbr> SOP10100.custnmbr/ Inner Join Enforced Both

IV00101.noteindx> SOP10100.noteindx/ Inner Join Not Enforced

SOP10100.locncode> IV00101.locncode/ Inner Join Not Enforced

Error u201CFailed to retrieve data from database.u201D

Error u201CDatabase Connector Error: u2018If tables are already linked then the join type cannot change.u201D

I need to know how to clean up the data either on the first report or how I can link the second report without getting the errors.

Please let me know if I you need any additional information for clarification.

Sincerely,

Cindy

[Former Member]

Hi Cindy,

Please test with following options:

1) Put the crystal reports on the highest level of service pack(SP 4).You are using Crystal Reports Release 1.Following is the link to download:

https://websmp230.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/bobj_download/main.htm

2) First write the desired sql at the database end,execute it there,and check whether the results are as per requirement.If you rae satisfied with the result,the same sql can be used in the 'Add Command' dialogue box which is an option (once a user connects to the desired database).

3)You may test by changing the type of connectivity(say use native or oledb instead of odbc).

Thanks