Display group with no data in Crystal Reports 12

Question

Hi, I am trying to group my data based on age. I use the following database select:

select * from (
select 0 range_start, 11 range_end, '0-10 days' date_description from dual union
select 11, 21, '11-20 days' from dual union  
select 21, 31, '21-30 days' from dual union  
select 31, 99999, '31+ days' from dual) date_helper
left outer join table
on table.date <= date_helper.range_start*-1 + sysdate 
and table.date > date_helper.range_end*-1 + sysdate 

I then make a group based on the date_description column. I am trying to make it display all groups, even when there are no records, that fall within that group. If there are no records, I want it to have a value of 0, and still print the group.

Thanks!!

Answer 1

(+1 for completeness of your question. Welcome to SO!)

If there are no records for a group, then obviously Crystal can't report it. I recommend creating a "helper" table in your datasource. Here is what I would do using some form of SQL:

1. Make a 'helper' table. It will have 1 column and will contain all the groups you want displayed. If the names of the groups are dynamic, you may want to use a select query or make-table query.

2. Right join from your helper table to your data-table. Send the combined data to Crystal.

3. In Crystal, use the helper table's column in your groupings and agebucket calculations.

Also, in your calculation, you should add a line: Else "No age";

Answer 2

Expanding on a comment on PowerUser's answer, if you're using a version of Crystal that allows you to enter your own SQL (instead of having to use Crystal's Database Expert), you can set up a subquery that acts as a helper table - something like:

select * from (
select 0 range_start, 11 range_end, '0-10 days' date_description from dual union
select 11, 21, '11-20 days' from dual union  
select 21, 31, '21-30 days' from dual union  
select 31, 99999, '31+ days' from dual) date_helper
left outer join 
(select sysdate-5 mydate from dual union all 
 select sysdate - 25 from dual) mytable
on mytable.mydate <= date_helper.range_start*-1 + sysdate 
and mytable.mydate > date_helper.range_end*-1 + sysdate 

(Oracle syntax - the precise syntax of the query will vary depending on which dialect of SQL you are using.)

EDIT: Changed from SQLServer to Oracle syntax.

FURTHER EDIT: Added some simple sample data.