I have a set of records in SSRS (SQL Server 2019) with a "code" field that has a value from a list (M, S, W). I'm grouping on another field and need to get counts of this field's value within the group (i.e., how many rows in the group have S, how many M, etc.)
I'm probably describing this poorly but this is a simplified version of what I'm trying to do.
name | code
-----|-----
Bob | M
Bob | S
Bob | S
Bob | W
Joe | S
Joe | S
Grouping on name, I'd like to be able to see this:
Bob group M = 1, S = 2, W = 1
Joe group M = 0, S = 2, W = 0
I'm trying to write an expression to hide/show a group based on these values so I need to be able to test them. So far, I'm not able to figure out how to do this in SSRS. Can it be done?
FWIW, I've previously accomplished this in Crystal Reports by creating variables within the group and literally counting each value while printing the records and then examining them at the end of the group.
I would probably just sum 1 for each matching value. Assuming the expression sits within your rowgroup then you won't need the scope specified
=SUM(IIF(Fields!code.Value = "M", 1,0))
Obviously needs to be repeated for each code but that should work.