Print Page | Close Window

Force header even when detail does not exist?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5479
Printed Date: 25 Apr 2024 at 10:11pm


Topic: Force header even when detail does not exist?
Posted By: john0990
Subject: Force header even when detail does not exist?
Date 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



Replies:
Posted By: hilfy
Date 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
 


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: saclifford
Date 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?


Posted By: hilfy
Date 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



-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: saclifford
Date 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?


Posted By: hilfy
Date 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


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics



Print Page | Close Window