Crystal 11 report information not displaying.

I am using crystal reports 11 with a btreive connection to accounting software data.

I create a new blank report and create a betreive connection to my ddf files. I then select the two tables I wish to work with one being "inventory" and the second being "MultiStore Tr". I link the tables via a common "itemcode" field.

Now I can see these tables listed under database feilds in the field explorer. The problem lies when I select a field from each of the two tables and then run the report. Nothing is displayed exept the field names, but no data. If I just use fields from one table then it all works ok but as soon as I add in another field from the second table then the report does not display any information.

I have tried this same report in version 9 of crystal and it all seems to work fine. Can anyone please suggest why this is happening and how to resolve it.

Thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What join are you using?

Check the linking of the two tables, it may need to be defined diferently i.e Left Outer Join.
Check the linking in the Crystal 9 report (Right click on link >> Linking options...) and make it the same in 11.
amerretzAuthor Commented:
I link the 2 tables via the "itemcode" field
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

amerretz, you have to define what type of link between 2 tables. below is the join type for CR

LEFT OUTER JOIN : select all the records from main table and matched records from link table:
INNER JOIN : select records only from the matched records from both table.

For your case, first you have to know which is the main table, is it "inventory" or "MultiStore Tr", then when perform table linking, define which table is left join to the other one.

MIKESoftware Solutions ConsultantCommented:

I would also recommend that you always setup the table links "manually" when creating all new reports. Crystal will auto link tables according to how "IT" thinks they can be linkied, but I've found that it is much safer to just verify/set the table links in each and every report yourself, so that you wil be certain of your reporting data. You'll find that LEFT OUTER JOIN will be the most common type used, but again as the experts are is dependent on the tables and your speciific report.

amerretzAuthor Commented:
I am not sure what you mean by "LEFT OUTER JOIN" can you please explain this term. I have also verified that in both table fields which are being linked, that the data for "ItemCode" is the same data in both table fields. I have also tested a manual link with to two field which should not be linked (e.g. itemcode - table1 with barcode - table2) and in fact the data does show up when adding in two fields, one from each table. But as you know this is not the correct procedure. Linking must occur between a common field which both tables contain. e.g itemcode - table1 with itemcode - table2

Check dylanyee's explanation.
To change the linking you need to use the Database expert from the database menu.
Click on the links tab, then right click on a link between the tables to get to the linking option, set the link type there. (This is for CR9, not sure about 11)
MIKESoftware Solutions ConsultantCommented:

Table linking is same in CR XI.

You'll need to take some time and become very familiar with table linking, as this is the backbone of all relational database reporting.

If left outer join does not work, then there is probably something going on with the actual data elements that you are trying to link. Are they "really" the exact same fields? (ie. maybe one has a leading zero before the ID??)

ALSO, when choosing left outer joing, go ahead and choose ENFORCE BOTH in the enforce join box. ( I think this is only available in XI)

MIKESoftware Solutions ConsultantCommented:

These options should be chosed for CR XI left my opinion.

amerretzAuthor Commented:
When checking the link options in CR9 it is linked with an Inner Join which is not enforced. It works normally. I have set it up the same in CRXI and it does not display.
amerretzAuthor Commented:

I use these settings and it has improved the report. At least now I can view the left tables feilds (inventory) while have the right table fields (Multistoe tr) present in the same report. Still any field from MultiStore Tr are not viable but fields from Inventory are not.
MIKESoftware Solutions ConsultantCommented:
IF  INVENTORY is your MAIN table, then what this is telling is that those items are NOT present in the Multistore TR table. This should be what you need to know?

Not sure of the end result you are trying to accomplish.

Maybe you should post the exact look of the final report that you are trying to accomplish.


amerretzAuthor Commented:

Here is a link to some creenshots from v9 and v11
MIKESoftware Solutions ConsultantCommented:
IN a previous post you said that you tried LEFT OUTER JOIN and could see items from BOTH tables. Did this not fix your problem? Again, I believe this is the fix to your problem.

If not, just so we understand, your REPORT VIEW v9... what tables are those two columns from? The ITEM CODE and the STORE CODE?

MIKESoftware Solutions ConsultantCommented:
Additional suggestion, place BOTH ITEM CODES from BOTH TABLES on the report.

We need to know what you are trying to accomplish or analyze with this report, too?
amerretzAuthor Commented:
one field is from inventory table and the other field is from multistore trn (you can see it in the picture)

I will add another picture of JOIN TYPE = LEFT OUTER, ENFORCED JOIN= ENFORCE BOTH, and LINK TYPE = "=" in version 11

MIKESoftware Solutions ConsultantCommented:
Also, why are you recreating? Can't you just open the report in XI and save in XI????
amerretzAuthor Commented:
Still the same problem when reopening.... I hope you can see my problem now.
MIKESoftware Solutions ConsultantCommented:
I need a REPORT VIEW of BOTH tables separately.

One showing ITEM CODE and STORE CODE...From....INVENTORY...and one showing ITEM CODE and STORE CODE...from MULTISTORE table.

Something strange is going on here. When I see these, I'll be able to diagnose.

Also, are you sure you have SAME CRITERIA....dates.....etc.... for both reports? V9 and 11?

Waiting on reply.
amerretzAuthor Commented:
The only common field for both tables it "ITEMCODE"
amerretzAuthor Commented:
I can give you item code for both tables in report view
MIKESoftware Solutions ConsultantCommented: me the SQL QUERY pls.... goto....DATABASE >> SHOW SQL QUERY. Copy and past that here please. Post the one for v9 and the one for v11.

This will help alot.
amerretzAuthor Commented:
How can I get to that is a greyed out  selection for me.
MIKESoftware Solutions ConsultantCommented:
yes, but just highlight and post...

Highlight with mouse holding left mouse button down..then right click your highlighted area..and CUT...then come here ...and right click the  blank area...and PASTE.
amerretzAuthor Commented:
I mean I cant click on the "show SQL query" button .... the button is greyed out

Do I need to be in some type of viewing menu to see this???? sorry but I am no pro at this crystal reporting
MIKESoftware Solutions ConsultantCommented:
I need to signoff...but if you can please post the ITEM CODE from BOTH tables....SEPARATELY. So that's 2 from INVENTORY...showing ITEM CODES....for whatever slice of data you can.... and report from MULTISTORE...showing the exact...same slice of data. (dates...or whatever you are using...)

There is something going on with the contents of the tables..I think.

I'lll check out in the AM.
This might be worth a look.

Make sure you are running the latest updates, then try again.
Any luck with this?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amerretzAuthor Commented:
I have just paid business objects $240 to help solve their error for me.

Thanks for all your help Rick J. Points will be assigned to you for your prolonged effort.
I hope you solve your problem.
Not much fun being charged for a bug to be fixed :(
Thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.