CODEWITHSUNDEEP
sql-serverreporting-services
jreed121
jreed121

Reputation: 2097

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:

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

Upvotes: 17

Views: 53332

Answers (2)

user2314199
user2314199

Reputation: 21

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

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

Regards

Mercy S

Upvotes: 2

Ian Preston
Ian Preston

Reputation: 39566

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

Upvotes: 31

Related Questions