Including NULL results in PIVOT-based SSRS report

[mkrausnick]

I have:

select userloc,event_type,count(*) as cnt from (
select distinct userloc,event_type,clientid
	from table1
) t2 group by userloc,event_type
) t1
pivot 
(
sum(cnt) for event_type in 
([New] , [Renew])
) t3

This produces a nice grid with user locations down and client events across.

If there are no records for a location containing the value 'Renew', for example, that value comes out NULL in the pivot result set, not zero as I would expect. If I then feed the result set into an SSRS Report, the row with the null value does not appear on the report.

How do I fix it so the report includes rows where one of the values is null?

Thanks,

Mike Krausnick
Dublin, California

 

[mkrausnick]

Never mind - my statement that rows with NULL values do not appear on the report was incorrect. I must have done something else wrong.

Mike Krausnick
Dublin, California