Crystal Reports / linking tables / Reporting Methods

j.belanger

Does anyone else use Crystal reports with Navision?
I can't seem to link two items in two different tables together. ie. No. and Item No_
my usual method of reporting is by Jet Reports, but i'm a bit annoyed with the hidden rows and columns that can't be removed without interacting with the finished reports.

What does everyone else use for reporting that doesn't require knowing how to code?

Savatage

if you've already selected the tables you want then all you have to do is click
Database->Database Expert->Click the links TAB

You will see each table in it's own box.
I usually click CLEAR LINKS because it automakes links that aren't already correct.

Then I simply click on a field (No.) hold the mouse button down.
move to the other table, find the field you want to link (Item No.) and let go

A link should now be showing (red line)

j.belanger

I've done everything you mentioned and still coming up with the same issue.
See attached images.

I'm using Crystal Reports 9
I've also tried 10 and 11 with the same issues.
If i take the mapping out the report just runs and runs.
What's supposed to happen in a nutshell is

List All part No.
also list the Cross-Reference No. that go with the part No.

I can get this to work using Access and Jet reports, but crystal keeps giving me errors.

Any suggestions?

Anyone have a copy of ExSION i can try?

Savatage

take a step back it's not about the links it appears to be about the connection

Are you using SQL or Native? Native you setup n/odbc or sql you use the sql odbc driver.

I personally use Database Expert->create a new connnection-> ODBC (RDO)
are you using OLE DB (ADO)??

j.belanger

native db
I"m using the OLE DB (ADO) connection as shown in the attached photo.
that connection is using the Microsoft OLE DB Provider for ODBC Drivers

Savatage

Native Server needs n/odbc - you can find it on the product cd
did you set up n/odbc in administrative tools & if you did did you try using the other ODBC like I use?

j.belanger

Just doesn't seem like a connection issue as i ca create a simple report using the following fields / tables

Item Table > No_ > Description > Quantity on hand

Report runs perfect. it's when i try to add the Cross-Reference # field next to the No. field to give me each cross # that goes with the item No_

Savatage

why do you need the item table for that the "cross reference table" contains the "item No" & "the cross reference #"

Just group by item no. are you using n/odbc - you haven't answered that.
We've been using the ODBC (RDO) connection type for years without issue

j.belanger

I am as far i I know
If i wasn't using the attain odbc would the report work at all?
See attached photo

Savatage

j.belanger wrote:

If i wasn't using the attain odbc would the report work at all?

I don't know what would happen I try to follow the directions 8)
Next would be to look at your c/odbc options & then try creating a report using the other connection odbc (rdo) and see it that make a difference.

I can only point out suggestions since I am not there and making alot of assumptions.

The orig post shows you message as ADO error code - so I would try the other type of connection AWAY from ADO
& it says SQL State that's why I was wondering if you were using sql

j.belanger

I think i may have figured it out with one of the suggestions you made about the Item No. and Cross-Reference No_ in the same table. looks like using the No_ from the Item table and the Cross-Reference No_ from the Item_Cross_Reference table was confusing Crystal reports. try it and let me know if it gives you issues also. I do have the report working correctly now.

Any suggestions on filtering? Grouping i believe Crystal call it.
ie. showing only qty's >0 stuff like that

Thanks for your help up till now...

Savatage

if you're going to link the item table to the cross reference table then you need to link

Item.No to ItemCrossReference.ItemNo not to the CrossReference field - they are probablay different sizes and types causing a meltdown

Here is the c/odbc manual just to keep handy if you don't have one..
http://savatage99.googlepages.com/w1w1codbc.pdf

filtering is done using Report->Select Expert
groupinf is done using Report->Group Expert

Good luck - glad you got it working