views:

53

answers:

2

Hey guys, So I have this report that I am grouping into different age buckets. I want the count of an age bucket to be zero if there are no rows associated with this age bucket. So I did an outer join in my database select and that works fine. However, I need to add a group based on another column in my database.

When I add this group, the agebuckets that had no rows associated with them dissapear. I thought it might have been because the column that I was trying to group by was null for that row, so I added a row number to my select, and then grouped by that (I basically just need to group by each row and I can't just put it in the details... I can explain more about this if necessary). But after adding the row number the agebuckets that have no data are still null! When I remove this group that I added I get all age buckets.

Any ideas? Thanks!!

A: 

I had trouble understanding your question.

I do know that Crystal Reports' NULL support is lacking in some pretty fundamental ways. So I usually try not to depend on it.

One way to approach this problem is to hard-code age ranges in the database query, e.g.:

  SELECT p.person_type
         , SUM(CASE WHEN 
               p.age <= 2 
               THEN 1 ELSE 0 END) AS "0-2"
         , SUM(CASE WHEN 
               p.age BETWEEN 2 AND 18 
               THEN 1 ELSE 0 END) AS "3-17"
         , SUM(CASE WHEN 
               p.age >= 18 
               THEN 1 ELSE 0 END) AS "18_and_over"
    FROM person p
GROUP BY p.person_type

This way you are sure to get zeros where you want zeros.

I realize that this is not a direct answer to your question. Best of luck.

Adam Bernier
+1  A: 

It's because the outer join to age group is not also an outer join to whatever your other group is - you are only guaranteed to have one of each age group per data set, not one of each age group per [other group].

So if, for example, your other group is Region, you need a Cartesian / Cross join from your age range table to a Region table (so that you get every possible combination of age range and region), before outer joining to the rest of your dataset.

EDIT - based on the comments, a query like the following should work:

select date_helper.date_description, c.case_number, e.event_number
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
cross join case_table c
left outer join event_table e
on e.event_date <= date_helper.range_start*-1 + sysdate 
and e.event_date > date_helper.range_end*-1 + sysdate
and c.case_number = e.case_number

(assuming that it's the event_date that needs to be grouped into buckets.)

Mark Bannister
Hey Mark,You answer all my crystal questions! Is there any other solution besides a cross join? The data set is pretty large and a cross join may not be feasible.
ntsue
@ntsue, hi! Please can you include more details about the [other group], such as number of rows, derivation, etc? I would normally expect the cross-joined age range-[other group] set to be significantly smaller than the dataset it will be joined to. Further details might clarify whether alternative approaches (such as Adam's, or by using sub-reports) are more suitable.
Mark Bannister
@Mark, Hi. The other group is a primary key for my table. The reason I am grouping based on the primary key (case number) of a table instead of simply putting that in the details of my report is that I am using the case number to join to another table (where it is not a primary key). So the other table might have columns, "case number", and "event" where "case number" is not unique. I basically want to grab the last "event" associated with that case number and I have accomplished this by grouping by case number and displaying my information from the footer and not the details.
ntsue
@Mark, you have assisted me before with the age buckets, ( http://stackoverflow.com/questions/3858383/display-group-with-no-data-in-crystal-reports-12 ) and that was very helpful. Do you think it would be possible for you to post a sample query of the cross join that you mentioned? I would greatly appreciate it.. thanks mark!
ntsue
@ntsue, answer updated with sample query.
Mark Bannister