Reports - force display of empty level of group

[SW]

Hi. I'm pullin' my hairs out! I'm designing a fairly simple report
about some physicians at work. They are grouped by Specialty, and
then grouped by status of Senior or Junior. I'm not showing the
physicians' names in the report, but simply a count, by Specialty and
then by status (e.g. Cardiology has 4 Senior and 2 Junior docs).
However, some specialties do not have both senior and junior docs.
How can I force the report to display the empty group level for these
specialties and have zeros in all of its columns?

Specialty Status # docs # patients seen last year
Neurology Senior 4 2
Junior 0 0
Sub-total 4 2

Thank you, SW

 

[pietlinden]

Hi. I'm pullin' my hairs out!   I'm designing a fairly simple report
about some physicians at work.  They are grouped by Specialty, and
then grouped by status of Senior or Junior.  I'm not showing the
physicians' names in the report, but simply a count, by Specialty and
then by status (e.g.  Cardiology has 4 Senior and 2 Junior docs).
However, some specialties do not have both senior and junior docs.
How can I force the report to display the empty group level for these
specialties and have zeros in all of its columns?

Specialty     Status       # docs      # patients seen last year
Neurology     Senior          4                    2
                    Junior          0                     0
Sub-total                          4                     2

Thank you, SW

sounds like you have a join in there that you're not telling us
about. If you use an INNER join (the default), you get a "lossy"
join... any Status without a Doctor or Patients in the group will
"fall out" (not show in the result set). You need to change the join
between Doctor and PatientsSeen (or whatever the join table is) from
INNER JOIN to LEFT JOIN and show all the doctors. Then if you do a
count, you will show nulls for no values. Then use another query to
convert the nulls to zeroes, using NZ().