CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-tablixssrs-grouping
itsfighter
itsfighter

Reputation: 177

Distinct count on multiple columns - ssrs report

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

Upvotes: 0

Views: 6549

Answers (2)

Aftab Ansari
Aftab Ansari

Reputation: 946

Try this

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

Upvotes: 1

itsfighter
itsfighter

Reputation: 177

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)))

Upvotes: 1

Related Questions