Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show group with no records (null group)

Status
Not open for further replies.

crystaldev1

Programmer
Nov 6, 2003
232
US
Hello. I'm using CR 9 and Sql Server 2005. I would like to show a group regardless if it has any record in there or not.

Group1 Apple
Group2 Orange
Group3 <NULL>

Group3 would not show up since there aren't any records. Please let me know if this is possible to show all groups. Thanks.
 
Hi,
What should it show? What are you trying to accomplish with a meaningless group?

You could create a formula the translates the NULL into some value and use that for the group like:
Code:
If IsNull({FieldIAmGroupingOn}) or Trim({FieldIAmGroupingOn}) = "" then "NULL" Else
{FieldIAmGroupingOn}

Group on that formula instead of the field.





[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Turkbear's solution is correct if you want a group for records will null values. If you want particular groups to appear even if there are no records for that group, it is a different problem.

The best solution is to create a table for each category, and then link it using a left-outer to link to the details. This depends on you being allowed to create a new table, or can easily get one created to your specifications.

Another solution is to include a wider range of date in your data, one that will include something for every vendor. Then suppress printing for those outside of the date range, showing just the group header or footer. This is an inefficient use of machine-time, but might be the simplest solution from your point of view.

A third way is to create running totals for all possible group IDs. Show them in the report footer when the total is zero. Suppose that records may be for apples, oranges, pears, bananas or guavas. You can group by type of fruit, but that does not show cases where there are no oranges. So you do a set of running totals (which are an option under the [Field Object] on the [Insert] menu). Each of them does a count of something, maybe account number. But each uses the formula option on the [Evaluate] section, and counts just for the one fruit.


You could also put each running total in a different section of the report footer. Suppress that section when the total is greater than zero, so only omitted groups are mentioned. It must be the report footer, because running totals are accumulated during the course of the run.

[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP [yinyang]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top