Hello CR friends,

Report Background:
Purpose: To see which work orders belong to which company
Groups:
Group 1: Case Owner
Group 2: Case Status
Group 3: Case Category

Calculated fields
If {@company} = "1" then {Case ID}
If {@company} = "2" then {Case ID}
etc..

These calculated fields are summarized on each group header with a distinct count so it should give me a breakdown of which companies cases are under which owner and which status like such:

Owner     TotalCount   Company1   Company2   Company3
Owner1            7               0               0                 7
Owner2
etc...

Problem:
However, the count distinct of {Case ID} is giving me 1's where it should be 0 (see the red values above) but it works fine where the value should be 1 or higher.

The closest thing I've found is when I switch my calculated fields to
If {@company} = "1" then 1
and do a sum, it works fine, except when there are duplicate records. I can't rely on Select Distinct Records because a SQL join results in several duplicate Case ID's. If you think it would be easier to change my query than the calculation, I'd be happy to divulge my frustrations with that :)

Thanks,
Randin

Hey thanks for the quick reply. Since I already had the report built out I just did the null trick and it worked perfectly. Huzzah!
I'll be sure to look into cross tabs and running totals, as this is surely not going to be my last report in CR and every little trick seems to help along the way.

Cheers & thanks!
Randin