Crystal Reports: Widows and Orphans in Grouped Reports

This article, courtesy of Cindy Kredo (Access development/software solutions/training) www.cindykredo.com

Crystal Decisions Kbase Articles C2000973, C2000972 and C2008983 provide solutions to report widows and orphans in grouped reports.

Users often want both widow and orphan control. If they use the Crystal solution to prevent widowed group headers and widowed group footers, it works fine only when every group has more than one detail record.

Those groups that contain one detail record will get duplicated: the same record appears twice in the report.

One solution is to add a conditional format formula to the Group Footer A section that suppresses the section if the count of the records in the detail section (for that group) is equal to 1.

Group by field

On a somewhat related topic, you can group by field. In fact, you can group by the same field more than once. This could be useful if you have used Specified Order for the higher level group, or the report is TopN and you would like the "Others" group to show totals by the original field at the next level down.

However if you use that summary in a formula, interesting things happen. The summary is referred to in the formula as

There is nothing in that formula to distinguish which of the two levels summaries you want. So the formula will always display the higher level total. This means you can’t use the lower level summary in that form in the report.

If you want the lower level summary in a formula you will need to create a formula to calculate the original group field. Then create the lower level group by the formula rather than the field. You can use this summary in a formula. We have a sample report to demonstrate this available from our web site to illustrate this.

Grouping by a field more than once is available in Crystal Reports versions 9, 10 and XI. Crystal Reports 8.5 and earlier will not allow you to create a group if the field is already used for grouping. However, our formula field solution lets you group by a field more than once for any version of Crystal Reports. And the summaries don’t get confused when you use them in formulas.

Having problems grouping?

Then you need to spend a little time understanding the Group Expert and how it can help you.

The Group Expert is a quick way to see what fields you are using for groups in your report. It also provides an easy way to shuffle the groups around into the sequence you require. One feature we like (in CR9 and above) is you can "undo” after you have used “delete group.”

Steve Dowse from Business Objects in Sydney reminded us that (with Crystal Reports 9.0 and above) you can group on the same field more than once. This lets you use a field for specified order and then within that group you can group by individual values of the fields. Earlier version users need to create a formula that uses the group field. You then could group on the formula.

Overflow problems?

What if you have a large group that flows over to a subsequent page? How do you control the report so the headings re-appear on each page as the group continues?

When you create the group, select the option "Repeat Group Header on Each Page." This will cause the group header to appear on each new page. If you have forgotten to set this, you can change an existing group, and set it later when you need to.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.