Crystal Reports with Dynamics GP data Report Errors

[Former Member]

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]

Thank you for your help and the link for the service pack.

I will be installing the service pack tonight.

I have a question regarding what you suggested. I'm in no means a knowledgeable database guru.

I have done a little work with 2005 SQL Server Management Studio but not comfortable writing script. Can you give a little additional information? Such as how do I write the desired sql at the database end and check whether the results are as per requirement.

If I'm satisfied with the result,the same sql can be used in the 'Add Command' once I'm connected to the desired database is the Add Command can be found in the SQL Management Studio?

Once it's executed how would I test it by changing the type of connectivity and how do I use a native or OLE DB connection?

Thank you again for any help you can give me with this.

Cindy

[Former Member]

I have similar error:

Database connector error; u2018if tables are already linked then the join type cannot be changedu2019

Aftre I tried to add new table to my report. I'm using ODBC and can't use any other connection... can it be bacause of version? I have CR XI version 11.5.8.826.

Thanks,

Vitaliy

[former_member11696]

Hello,

"Iu2019m trying to replicate our sales orders from Dynamics"

If you can get the SQL that report uses then you can use it. One way is to start up SQL Profiler and enable tracing. You can get the SQL that app uses.

Beyond this you are asking for assistance way beyond the scope of Forums and what you can do is find a Database Administrator or someone who understands the SQL language to help you or hire some Profession Services to write these reports for you.

You could purchase a case on line and have a dedicated Engineer help you but they own't create the reprot for you just help you figure it out. Depends on the complexity etc.

Thank you

Don

[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