Menu
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.
458
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
The COUNT DISTINCT function returns the number of unique values in the column or expression, as the following example shows. SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL.
You use groups. At the top click the insert menu, then table, then Table Wizard. Pick your dataset and hit next. Now drag the column for the different types of items you want a distinct sum of into the Row Groups section.
Right-click on any value in column B. Go to Value Field Settings. In the Summarize Values By tab, go to Summarize Value field by and set it to Distinct Count.
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"
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
