Crystal Reports Link loop

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Link loop
	Page of 3 Next >>

Author	Message
elie234 Groupie Joined: 21 Aug 2009 Online Status: Offline Posts: 68	Topic: Link loop Posted: 11 Sep 2009 at 9:30am
	I am doing a report where I have had to link to a table twice (x->y->z and x->z). While CR lets me do this I am getting back too few records than I should be. Any ideas on why that might be so? My hunch was that it had something to do with the link order, but I have experimented with that to no avail.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Sep 2009 at 9:38am
	are these all inner joins?
	IP Logged

elie234 Groupie Joined: 21 Aug 2009 Online Status: Offline Posts: 68	Posted: 11 Sep 2009 at 9:41am
	Yes.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Sep 2009 at 9:45am
	You may want to try some outer joins to expand you data set. Maybe x to y and z to y? You may also end up needing to create a view , stored proc or use a command to get the data set you need. I recall seeing your post on this a while ago but can't recall the specifics. Do you have some sample data that from the tables including rows that are not appearing that you want to appear? Edited by DBlank - 11 Sep 2009 at 9:46am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Sep 2009 at 9:49am
	Couple of gotcha's on joins... 1. if you do not enforce the joins in the set up you must use at least 1 field from each table in the report (anywhere even if not displayed) for the join to "activate". 2. Select statements can also impact joins and may change an outer join to an inner join.
	IP Logged

elie234 Groupie Joined: 21 Aug 2009 Online Status: Offline Posts: 68	Posted: 11 Sep 2009 at 9:50am
	Thanks for the advice! When I try the outer joins I get an error that if the tables are already linked then the join type can't change.
	IP Logged

elie234 Groupie Joined: 21 Aug 2009 Online Status: Offline Posts: 68	Posted: 11 Sep 2009 at 9:59am
	If I were to use a command what should I put in the command? The fields from y and z?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Sep 2009 at 10:11am
	Maybe.. can't really say unless I know the tables teh contnet then linksand what you want to get out of them. The theory is that using a command you can write a SQL statement that has a bit more control on the join and selections. Which table has the "missing records" in it? If you can track where the exclusion is occuring you can focus altering your process to account for that. To change these to outer joins you may need to remove all of the links and start your join process from scratch. (at the vary least romve your join from z to X) Please keep in mind that I am guessing at this. Without the tables and you desired data output that is the best I can do...Maybe this....????? x outer left join to y y outer right join to z z inner join to x
	IP Logged

elie234 Groupie Joined: 21 Aug 2009 Online Status: Offline Posts: 68	Posted: 11 Sep 2009 at 11:46am
	Thanks DBlank! I went with a command for the y and z tables and was then able to left join from x and I got the data I needed. I now see more clearly what the issue was which leads me to another question. I am reporting on manufacturing production of different machines. Table Z deals with estimated production and Table X and Y deal with actual production. Two of the machines (call them A,B) are identical. The issue was that in Table Z there is only estimated production for machine A even though in actual production some of that production will be routed to machine B. I would like to be able to compare actual to estimated production for Machine B, which would mean somehow considering Machine B to be Machine A. Any ideas on how to do that? Would a formula work for that?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Sep 2009 at 12:00pm
	Not sure I exactly undertand the set up but one idea is to do a case statement in your command to convert "B" int "A" and then use that converted field for the join? Will that work or give you an idea? Edited by DBlank - 11 Sep 2009 at 12:01pm
	IP Logged

Page of 3 Next >>

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.031 seconds.