Crystal Reports Linking Subreports with Null Date Fields

Technical Questions

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

Topic: Linking Subreports with Null Date Fields

Author	Message
mkc0108 Newbie Joined: 21 Apr 2009 Location: United States Online Status: Offline Posts: 4	Topic: Linking Subreports with Null Date Fields Posted: 21 Apr 2009 at 1:31pm
	I am working on a report which requires me to group my data by date. It's using a subreport to pull data from an order table and link it to data from a shipped table. I need to link the reports using the dates so they group correctly to get a daily pulse of orders and shipments. However, some days seem to have no shipments but do have orders. The orders do not show up because the date does not exist in the shipment table. Any ideas on a work around?
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Apr 2009 at 1:54pm
	Still not sure exactly how you have this set up or where they are being excluded... can you use an OR statement or a left join here to include them? Edited by DBlank - 21 Apr 2009 at 1:55pm
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 22 Apr 2009 at 6:41am
	Could you place the orders in the main report and the shipped orders in the subreport? This way the orders will show regardless of shipments. Are there days where there no orders but there are shipments? I guess the other unasked question...is it possible that were no shipments on any given day, or should every day have a shipment.
	IP Logged

mkc0108 Newbie Joined: 21 Apr 2009 Location: United States Online Status: Offline Posts: 4	Posted: 22 Apr 2009 at 6:47am
	You're correct...that would take care of this particular issue, however there are some cases where the opposite is true and there are shipments and no orders. I have ran the orders and shipment reports seperately, so I'm know there are orders for that day. I can turn it around, but I'll face the same issue when their are shipments and no orders. Thank you for your response...I'm having quite the quandry here.
	IP Logged

mkc0108 Newbie Joined: 21 Apr 2009 Location: United States Online Status: Offline Posts: 4	Posted: 22 Apr 2009 at 6:48am
	DBlank: I didn't see an option when linking the subreport to choose the type of link/join. Is there an option for that?
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 22 Apr 2009 at 6:56am
	OK, since there are days where one side does have output, if you are working from a view or stored proc, you would want to use a full outer join (I think that this it since right or left won't always return a result) If you are linking straight to the tables in Crystal, right click on the link and select the full outer join option. Hope this works for you.
	IP Logged

mkc0108 Newbie Joined: 21 Apr 2009 Location: United States Online Status: Offline Posts: 4	Posted: 22 Apr 2009 at 7:01am
	I am using crystal to connect to my datasources. However, I can't choose the link/join in which use when I link the main report to the subreport. I do not see where it gives me an option to do so. I am using Crystal 11.
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 22 Apr 2009 at 7:12am
	Can you do this in the subreport? the main report links to the subreport by what, a date? what else is on the main report? I am not a big fan of subreports as the hit database for every call, so if you main report has 100 rows, there are 101 calls to the database, which can be a big performance hit. So if there is a way to incorporate the orders and the shipments into the main report, it should run faster and the ability to perform and full outer join is available. If not, you might think of linking the subreport based on a date (which might be a parameter) or a range of dates, instead of a date from the order table.
	IP Logged

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