How to display group header though no records returned

[TMJ]

Crystal v8.5.3.975 running on SQL database using ODBC connection.

I've created a report (no sub report) using 3 tables.

I have also created a few groups and conditionally suppressed the headers so each one shows a count of records for that group.

Trouble is that if there are no records for that particular group when the report is run then there is no count and therefore no header for that group.

I want it to show the header and say "no records" if that is the case.

I have looked through previous postings and tried left outer joins but this does not work and wonder if there is another way around this problem.

Does anyone have any suggestions please.

 

[IanWaterman]

You can not do this in group header, you can create a report footer with the message no records returned.

Conditionally suppress this footer if record count is greater than 0.

This footer will always print, and then disappear if you do have records.

Ian

 

[IdoMillet]

I don't believe Ian's approach would work because with no records, the group simply doesn't exist.

One approach is to figure out why the outer join didn't work for you. MOre details are needed but I suspect you need to add an "...OR IsNull() " condition to the record selection criterion if you have a condition on a field from the outer side of the join.

Another approach is to ensure each group is represented by appending dummy records via a UNION statement.

Another approach is to use a main report with 1 record per group and place the detail as a linked subreport.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I second Ido's first approach.

The thing to remember is that Crystal shows existing data, since your query doesn't return some group, you can't display that group.

The Left Outer from the table containing the group names is key, and getting SQL Server to return all groups via Crystal can be tricky in CR 8.5.

Try posting what's in your Report->Edit Selection Formula->Record, and what's in the Database->Show SQL Query

-k

 

[IanWaterman]

Well it works for me, when a report returns no records you still get report headers and footers. I appreciate that you will not get a group header/footer.

If you have a report footer which is suppressed when data is found you can use this to display a message such as "No Records Found"

Ian

 

[Madawc]

You can do running totals for possible groups, showing them if the total is zero. This does mean you have to cover every possible group value, of course.

If you can change the database, you could have another table for all possible groups.

Madawc Williams
East Anglia, Great Britain

 

[GJParker]

Ian W

Your approach is fine if all you want do is display 'NO RECORDS RETURNED' for the whole report, but you can't use this for individual groups

If the group has no records how do you know it exists ?

Ido has the right approach

Gary Parker
MIS Data Analyst
Manchester, England

 

[TMJ]

Thank you all for your help.

I have not had too much time to try all the options yet but certainly will do and let you know how I get on.

I believe the problem concerning the joins is that the 1st table contains a long description for the 2nd table (so I believe is not key to selection). The 2nd table contains the data item that the record selection is on and the 3rd table does not contain a field directly related to this data item so I have linked table 2 and 3 using a data item that is identical in both tables.

I will try out all suggestions though.