Crystal will not create a group in a report unless there is at least one record for that group in the report’s dataset.  This also affects Cross-tabs and Charts, where you won’t see a row, column or bar unless there is a corresponding record.  But, it is not unusual for users to request that missing groups show up with a zero.  There are several ways to do this and the approach to use depends on a number of factors. Everyone’s first thought is to use an outer join, but I have found that the outer join solution isn’t compatible with the filtering and grouping required in most reports.

One of my favorite workarounds is the “padding” method. Say that you need a report to list total sales for each salesman in a single month.  You want each salesmen listed – even those with no sales in the month.  You could “pad” the data by expanding the criteria to include normally irrelevant records.  So if  the transaction table includes both sales and calls, and you are confident that every salesman has at least one call per month, then we could select the records for BOTH sales and calls and include them in the dataset.  This would ensure that every salesman has at least one record in the report.  To make sure that these extra records don’t affect the totals, you would write a formula to use for totals:

if {Type} = “Sale”
then {Amount} else 0

If you subtotal this formula by salesman you will have a group for every salesman, but a total of only the sales.  If you are printing the details you could suppress the call details and only show the sales details.  The advantage of this method is that both charts and cross-tabs can also include the no-sale groups with a total of zero.

Another way to pad your data is to pull in a much larger time period than you need.  So if you have a Date Range parameter you could write a selection formula that says:

{Date} in Minimum ({?Date Range}) – 90 to Maximum ({?Date Range})

This would add 90 extra days of data to your report, making it more likely that every salesman will be represented by a sale.  Then you use the following formula for your totals:

if {Date} = {?Date Range}
then {Amount} else 0

The risk is finding the right amount of padding.  You need enough data to fill in all the gaps, but not so much that you hamper the performance of the report.

There are other methods that I will discuss in later posts.  If you like to learn techniques like this, then you should check out my 3 volumes of Expert Techniques.