Distinct count on multiple columns - ssrs report

0
votes

I am new to ssrs and I am working on a report where I need to get a distinct count on two columns to display the total.

Table/Dataset format: 

ID   CREATEDATE  
123  03/01/2015 11:20 pm  
123  03/01/2015 11:20 PM  
123  NULL  
123  03/20/2015 05:15 PM  
345  04/01/2015 05:20 PM

Datatypes: 

ID - Int  
CreateDate - Datetime

If I get a distinct count on the above ex it should return 3 - First two rows have the same ID and Datetime. Eliminate Null records from the count

I tried something like following it works in all scenarios except when there is null value in a row it shows #error in the output.

=CountDistinct((Fields!ID.Value)+(Fields!CREATEDATE.Value.ToString()))

Could anyone please suggest either how to eliminate null value in my distinct count or better way to do this. Appreciate your time and help.

Thanks

2
reporting-servicesssrs-2008ssrs-tablixssrs-grouping
I would recommend counting at database level rather than SSRS. - BI Dude
handle it within the query of the dataset. - p2k

2 Answers

1
votes

I tried the following and it seem to be working so far.

=CountDistinct(IIF(IsNothing(Fields!CREATEDATE.Value),Nothing,CStr(Fields!ID.Value)+CStr(Fields!CREATEDATE.Value)))
1
votes

Try this

=CountDistinct(CStr(Fields!ID.Value)+CStr(Fields!CREATEDATE.Value))