?
Solved

Crystal 11 report information not displaying.

Posted on 2006-04-02
30
Medium Priority
?
379 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:amerretz
  • 12
  • 11
  • 5
  • +2
30 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 16357349
What join are you using?

mlmcc
0
 
LVL 8

Expert Comment

by:RickJ
ID: 16357351
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.
0
 

Author Comment

by:amerretz
ID: 16357357
I link the 2 tables via the "itemcode" field
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 8

Expert Comment

by:dylanyee
ID: 16357383
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.

dylan
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357468
amerretz:

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 saying...it is dependent on the tables and your speciific report.

MV
0
 

Author Comment

by:amerretz
ID: 16357502
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

0
 
LVL 8

Expert Comment

by:RickJ
ID: 16357540
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)
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357643
Amerrets:

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)

M
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357651
Actually, JOIN TYPE = LEFT OUTER, ENFORCED JOIN= ENFORCE BOTH, and LINK TYPE = "="

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

M
0
 

Author Comment

by:amerretz
ID: 16357685
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.
0
 

Author Comment

by:amerretz
ID: 16357697
CRXIuser2005

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.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357719
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.

Thx
M

0
 

Author Comment

by:amerretz
ID: 16357819

Here is a link to some creenshots from v9 and v11

http://www.geocities.com/kippsyboy/cr911.html
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357867
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, ...in your REPORT VIEW v9... what tables are those two columns from? The ITEM CODE and the STORE CODE?

M
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357874
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?
thx
M
0
 

Author Comment

by:amerretz
ID: 16357878
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

0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357896
Also, why are you recreating? Can't you just open the report in XI and save in XI????
0
 

Author Comment

by:amerretz
ID: 16357901
Still the same problem when reopening.... I hope you can see my problem now.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357954
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.
M
0
 

Author Comment

by:amerretz
ID: 16357961
The only common field for both tables it "ITEMCODE"
0
 

Author Comment

by:amerretz
ID: 16357962
I can give you item code for both tables in report view
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16357997
Also...show 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.
Thx
0
 

Author Comment

by:amerretz
ID: 16358032
How can I get to that ...it is a greyed out  selection for me.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16358045
yes, but just highlight and post...

Highlight with mouse ....by holding left mouse button down..then right click your highlighted area..and CUT...then come here ...and right click the  blank area...and PASTE.
0
 

Author Comment

by:amerretz
ID: 16358053
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
0
 
LVL 17

Expert Comment

by:MIKE
ID: 16358090
I need to signoff...but if you can please post the ITEM CODE from BOTH tables....SEPARATELY. So that's 2 reports...one from INVENTORY...showing ITEM CODES....for whatever slice of data you can.... and then....one 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.
0
 
LVL 8

Expert Comment

by:RickJ
ID: 16358909
This might be worth a look.

http://support.businessobjects.com/library/kbase/articles/c2018641.asp

Make sure you are running the latest updates, then try again.
0
 
LVL 8

Accepted Solution

by:
RickJ earned 1500 total points
ID: 16397948
Any luck with this?
0
 

Author Comment

by:amerretz
ID: 16558535
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.
0
 
LVL 8

Expert Comment

by:RickJ
ID: 16558687
I hope you solve your problem.
Not much fun being charged for a bug to be fixed :(
Thanks for the points.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question