Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Force header even when detail does not exist? Post Reply Post New Topic
Author Message
john0990
Newbie
Newbie


Joined: 10 Feb 2009
Location: United States
Online Status: Offline
Posts: 1
Quote john0990 Replybullet Topic: Force header even when detail does not exist?
    Posted: 10 Feb 2009 at 12:47pm
I am working on a report based on 2 tables (header and detail) which are joined with a LEFT JOIN.

Both header and detail fields exist in the Detail section; I do not want to put the header fields in a Group Header/Footer and detail fields in the Detail section.

My question is, how can I force at least 1 record to print for every header, whether it finds a detail record or not?

Thanks!
John
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 10 Feb 2009 at 2:28pm

I would still use a Group header if possible but only show it when there are no detail records.  For the group header section use something like this in the suppression formula:

not IsNull({detail.ID})
 
-Dell
 
IP IP Logged
saclifford
Newbie
Newbie


Joined: 11 Feb 2009
Online Status: Offline
Posts: 5
Quote saclifford Replybullet Posted: 11 Feb 2009 at 1:38pm
I have a similar question.  My report has groups in a specified order and each group header shows a detail record count; the groups that have no detail records currently do not display a header on the report.  How do I get them to display with a count of 0 if they have no detail records?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 11 Feb 2009 at 2:12pm

Are your header and detail tables joined with a Left Outer Join?  If not, you need to make sure that the link is FROM the header TO the detail - not the other way around.  Right-click on the link and select Link Options, then select Left Outer Join and click on OK.  This will get you all of the records from the header table, whether or not they have a record in the detail table.

The other piece of this has to do with your selection criteria.  If you have fields from the detail table in your selection criteria, you'll need to edit the selection formula to look like the following:

IsNull({detail.key_field}) OR
(
  {detail.field1} = 'Blah' and
  {detail.field2} = 123
)

Be sure to use the parentheses.  Also, the check for the null value MUST happen prior to the conditions that contain the detail fields.  In the database, comparisons against a null value result in null, NOT true or false.  Since evaluation of a series of boolean statements ends as soon as a specified condition is not met and null does not meet the condition at any time (even in a "not equal to" comparison!) it will never get to the check for null if you put it at the end of the statement.

-Dell

IP IP Logged
saclifford
Newbie
Newbie


Joined: 11 Feb 2009
Online Status: Offline
Posts: 5
Quote saclifford Replybullet Posted: 12 Feb 2009 at 7:40am
Thanks!  I should have mentioned in my original post that I don't have individual tables joined in Crystal; I have a single SQL command.  Any way to force the headers using a command or do I have break out my command into individual tables?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 12 Feb 2009 at 7:59am
Is the SQL in your command returning the header data when there is no detail data?  If the header data isn't available, then I suspect that's where your issue is - you'll need to use outer joins in the SQL. 
 
If the header data is there, what are your groups?  If you're grouping on a field that is in the detail data, then that may be causing the problem.  Try my group header suggestion above, but put the data in the lowest level group that comes from the header data.
 
-Dell
IP IP Logged
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.