• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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
0
amerretz
Asked:
amerretz
  • 12
  • 11
  • 5
  • +2
1 Solution
 
mlmccCommented:
What join are you using?

mlmcc
0
 
RickJCommented:
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
 
amerretzAuthor Commented:
I link the 2 tables via the "itemcode" field
0
Cloud Class® Course: CompTIA 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.

 
dylanyeeCommented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
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

0
 
RickJCommented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
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.
0
 
amerretzAuthor Commented:
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
 
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.

Thx
M

0
 
amerretzAuthor Commented:

Here is a link to some creenshots from v9 and v11

http://www.geocities.com/kippsyboy/cr911.html
0
 
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, ...in your REPORT VIEW v9... what tables are those two columns from? The ITEM CODE and the STORE CODE?

M
0
 
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?
thx
M
0
 
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

0
 
MIKESoftware Solutions ConsultantCommented:
Also, why are you recreating? Can't you just open the report in XI and save in XI????
0
 
amerretzAuthor Commented:
Still the same problem when reopening.... I hope you can see my problem now.
0
 
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.
M
0
 
amerretzAuthor Commented:
The only common field for both tables it "ITEMCODE"
0
 
amerretzAuthor Commented:
I can give you item code for both tables in report view
0
 
MIKESoftware Solutions ConsultantCommented:
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
 
amerretzAuthor Commented:
How can I get to that ...it is a greyed out  selection for me.
0
 
MIKESoftware Solutions ConsultantCommented:
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
 
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
0
 
MIKESoftware Solutions ConsultantCommented:
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
 
RickJCommented:
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
 
RickJCommented:
Any luck with this?
0
 
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.
0
 
RickJCommented:
I hope you solve your problem.
Not much fun being charged for a bug to be fixed :(
Thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 11
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now