Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Report List Post Reply Post New Topic
Author Message
ronsandler
Newbie
Newbie
Avatar

Joined: 12 Dec 2007
Location: United States
Online Status: Offline
Posts: 8
Quote ronsandler Replybullet Topic: Report List
     Posted: 17 Dec 2007 at 6:37am
I'm trying to create a report that list my facilities ( Gas stations, theaters, homes etc.) on one line that will main constant. When I list the inspection, the inspection dates in the detail section ONLY the facilities with inspections are listed and the rest of my facilities are gone. Can anyone advise me how to keep all facilities to be listed on the report and also list the inspection date for a facility that has been inspected and leave blank area next to the facility that has NOT been inspected. I had some good ideasecently from the forum but they would not work. see below

Facility Permit Number       Facility Name
       
11-66-00001                  Bob's Gas Station
Inspection period 10/01/2007-12/31/2007 - 10/06/2007

11-66-00002                  Mike's Station
Inspection period 10/01/2007-12/31/2007 - No Date

HELP PLEASE, Ron   
Ron
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 17 Dec 2007 at 11:29am
I'm thinking that what you need is an outer join.  Check under "join types" in the help files, or in Brian's book.  That should give you a starting point.

As a warning, combining selection criteria with an outer join needs to be done with care.  For instance, suppose you only want inspection periods within a certain date range.  If you simply put that in your selection criteria, it will void the outer join.  This is because the outer join is returning NULL values for all the records in which there is not an inspection on file at all.  When you say that you only want records with an inspection date within the date range, the records with no inspection date don't meet that criteria, and are thrown back out.

There are a few possible fixes for this.  One is to allow for NULL values in your selection criteria.  Go into your Selection Criteria.  Go to wherever you reference a field from the Inspection table.  Click on Show Formula.  If the bit of the criteria looks like:
     {Inspection.PeriodDate} BETWEEN ?startdate AND ?enddate
then change it to look like:
    (({Inspection.PeriodDate} BETWEEN ?startdate AND ?enddate) OR IsNull({Inspection.PeriodDate}))

There is a basic problem with this method, though.  It will not return any facilities for which there is an inspection on file (and, hence, the PeriodDate is not NULL), but the PeriodDate is not within the range.  Sometimes, that's entirely acceptable.  In this case, it likely isn't.

A second is to eliminate the selection criteria.  Bring down all the data, then put a conditional suppress on the details section to suppress any records that aren't in the date range (or whatever criteria you are using).  The main problem with this method is that you can end up bringing down a lot of data that you don't actually want, slowing down your report and the database.

A final one is to use a SQL Command to do your join entirely within SQL, without using Crystal's linking.  By doing this, you can do a conditional join, which might look like:


SELECT Facility.Permit, Facility.Name, Inspection.PeriodDate
FROM Facility
LEFT JOIN Inspection
ON (Facility.FacilityID = Inspection.FacilityID)
     AND (Inspection.PeriodDate BETWEEN ?startdate AND ?enddate)


That gives you the maximum flexibility, optimum performance, and most correct dataset.  The general drawback is that you need to use SQL, which you may not be familiar with.


IP IP Logged
ronsandler
Newbie
Newbie
Avatar

Joined: 12 Dec 2007
Location: United States
Online Status: Offline
Posts: 8
Quote ronsandler Replybullet Posted: 17 Dec 2007 at 12:12pm
Thanks, Ron
Ron
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