Row count based on condition

[theoryofben]

I'm sure there is a simple solution to my problem...but I'm just starting to use SQL Reporting...so I need a little assistance. Here goes:

My query uses an outer join on a column, let's call it "confirm". I have the report grouped by location and employee and am displaying the total number or records for an employee using the CountRows function.

The problem is that I need to display the total number of rows that have a NULL for the "confirm" column for each employee. I know that reporting replaces NULLS with a blank space, but I still don't know how to calculate this in an expression so I can display it for each employee.

Any assistance or push in the right direction would be greatly appreciated.

Thanks a ton.

________________________________________________
[sub]"I have not failed. I've just found 10,000 ways that won't work."-Thomas Edison[/sub]

 

[haroldholt]

What about taking the other tack and altering the confirm column so that it becomes a meaningful value in the result set that you can then report on ?

e.g. (assuming Confirm datatype is a string) something like

SELECT
COALESCE(t2.Confirm,'Y') AS Confirm
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.Confirm = t1.Confirm

This way you don't have to do anything tricky at the RS client end and can use the same code to ensure that other reports or client-side consumers all return exactly the same result