Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Linking problem. Post Reply Post New Topic
Author Message
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Topic: Linking problem.
     Posted: 19 Feb 2010 at 12:17pm
I have an isue that seems easy but I have not been able to fix it.
 
I have a table with equipment records the report needs to show all records. I can create a group and it works great. There is a second table that has cost information. I do a left hand join and again everything works fine. I display all equipment records even if there is no records in the cost table.
 
There is a date field in the cost table and the report needs to be restricted by that. I create a Parameter and attach it to the date field in the costs table. Ok works  great except now any equipment records without costs not longer show up. I need them all to show up, it seems like I should be able to do it but I cannot seem to get it to work.
 
Any help with be apreaciated.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Feb 2010 at 1:16pm
The param turns your outerjoin into an inner join because the logic of your select statement inherently exlcudes them.
If the missing records all have NO mathcing values  in the cast table then you can alter your select statement to include NULLs. But if their is one matching row this trick will not work.
Example:
isnull(table.datefield) or table.datefield>?param
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 19 Feb 2010 at 2:06pm
Thank you. I will give that a try.
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 19 Feb 2010 at 2:12pm
That did the trick. Thank you.
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 22 Feb 2010 at 11:35am
I am afraid it did not quite work. I have three types of records I guess. The not null takes care of Equipment that has no records in the costs table. Records with costs within the date range display fine but pieces of equipment that have records in the costs tables with no records within the date reange do not show up. I need all records to show up. Can I do this?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Feb 2010 at 11:53am
That was what I was afraid of.
If you can create a Stored procedure (SQL) and create the sp parameter in it then you can do it that way. You just write your sp as an outerjoin using an 'and' clause with the date paramter.
Or the easier 'crystal only' way would be to not use the parameter as a select criteria but rathar as a suppression criteria (likely on the details section).
Make sense?


Edited by DBlank - 22 Feb 2010 at 11:55am
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 22 Feb 2010 at 12:03pm
I played with the suppression on the details section but that was before is null. I will look at it again. Thanks you for your help.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Feb 2010 at 12:07pm

For clarification , do not limit your data at all in the select expert (or at least not based on the param or ISNULL.

The ISNULL trick only works if there are no matching records from tableA to tableB.
If you do not limit the data at all in the select expert the suppression should work.
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.