Question dealing with a report

[BrandonBarbuto]

I hope this is the right forum to go to, here's the technical information behind the rreport i'm trying to make.

Tables :
Communication
Communication_type
Subject
Permit
Permit_Type
Facility

Example of the report would be :

Draft indirect permits were sent to 0 facilities.
Draft direct permits were sent to 3 industries :
KB Toys
EB Games
Gamestop

The first word "Draft" corralates to Subject.Subjet_name.

"indirect" or "direct" would be the permit_type

"permits were sent to" is just text

The sum of facilities is based off of how many times communication.facility_name is listed in that table. DistinctCount. Counted based on if they have the right subject (draft) and permit type (indirect).

The list of facility names comes from the facility table.

The tables link up like so :

Communication.subject - Subject.Subject
Communication.Communication_type - Comunication_type.comm_type
Communication.facilityID - facility.facility_ID
Communication.facilityId - Permit.facilityID
Permit.Permit_type - permit_type.permit_type

also, communication type has to be "CL"



The problem i'm running into is that I have to run the report for 5 different subjects specifically. If there isn't a matching permit_type for a subject, i'm not getting the desired effect on the report which would be :

"Draft indirect permits were sent to 0 facilities."

Instead because there's no match, its giving me nothing, which makes sense.

Problem is, this is my second week with Crystal reports and I don't know how to get around this, anyone have any ideas? Your help is greatly appreciated.

 

[Turkbear]

Hi, Using the Visual Linking Expert, be sure that the link to permit_type from Permit is a Left Outer Join..

Then test for a NULL in the permit_type.permit_type field and create the text accordingly...

 

[BrandonBarbuto]

Thank you for the quick response. I should have mentioned that I have tried left outer joining all the tables.

I can get the report looking great, up until the point I specify :

Communication_type.Communication_type = "CL"

Then it stops showing subjects that I want to show 0 values for because there's no matching record in the communication table with a communication type of "CL". I understand why its not showing me th rows, I don't understand how to get around this.

Hope that clears up what my issue is a bit more.

 

[Turkbear]

Hi,
It does that because when no value exists the selection formula fails.You are, in essence, mandating that only data that matches is returned, if no match, then no records.
Check the Sql generated and see if the Outer Join is applied to that condition as well as to the table joins..
( In Oracle ( pre-9.2) it would lok something like
Communication_type.Communication_type = "CL"(+)
)

 

[kskid]

With {Communication} -> LOJ -> {Communication_type}

Try the following

(
isnull(Communication_type.communication_type) or
{communication_type.communication_type} = "CL"
)

 

[BrandonBarbuto]

Thank you very much for this response wichitakid! Now it grabs all the columns I need. I had done some quirky thing with subreports and such in the meantime but now i'm going to go back and make it pretty and probably work faster.