SSRS Count Distinct Fields!B.value Where Fields!A.value =

I have a report with two tables. The first table is a list of clients, detailing each client that falls into one category of three with different items for each client. The SQL joins up a client_table with the client_items table (there can be multiple items per client) the SQL results looks like so:

Type ClientID ItemID
A 1 1
A 3 1
A 3 2
B 2 1
B 4 3
C 5 2

My second table is going to return counts of the distinct ClientIDs from various combinations of the types:

• Total (A+B+C) - I've got this one figured out with =CountDistinct(Fields!ClientID.Value,"datasource1")

• Type B+C - Distinct ClientIDs where the type is B or C

• Type C - Distinct ClientIDs where the type is C

I hope that was clear, if not let me know what I need to add to clear it up.

2 Answers

CountDistinct() counts non-null values, so you could use an expression to null out those values you don't want to count and do something similar to your first value.

Type B+C:

=CountDistinct
( IIf ( Fields!Type.Value = "B" or Fields!Type.Value = "C" , Fields!ClientId.Value , Nothing ) , "datasource1"
)

Type C:

=CountDistinct
( IIf ( Fields!Type.Value = "C" , Fields!ClientId.Value , Nothing ) , "datasource1"
)

=Sum(CountDistinct(Fields!UserName.Value,"IssueDate7"))

Here Username is the row group and Issuedate7 is the column group

Regards

Mercy S