i have tabel with data like EMPID Name ed345 a ed345 a ed346 b new resource new resounce Count distinct gives me count 3 instead of 2, its counting null as 1 any Suggestion? i am using countDistinct in reportViewer.

In T-SQL I am getting 2 create table #t (EMPID varchar(10),Name char(1)) insert into #t values ('ed345','a') insert into #t values ('ed345','a') insert into #t values ('ed346','b') select count(distinct empid) from #tBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi Binay, Based on your description, I created a test table with the data below: EMPID Name ed345 a ed345 a ed346 b NULL new resource NULL new resource Since you mentioned you are using CountDistinct in the report, I have tested the expression =CountDistinct(Fields!EMPID.Value)and it returns 2. However, if aggregate CountDistinct on field Name by the expression =CountDistinct(Fields!EMPID.Value) it will return 3. So, could you please check if the EMPID of new resource records are NULL but not blank. If I have something misunderstood, please point out and also post the expression you are using. For more information about CountDistinct Function in Reporting Services, please refer to the link below: http://msdn.microsoft.com/en-us/library/ms155923(SQL.100).aspx Thanks, Tony ChainPlease remember to mark the replies as answers if they help and unmark them if they provide no help