0

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.

inkswamp
  • 5
  • 1

2 Answers2

0

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.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks. I had already tried a similar approach, giving each of the values their own column and displaying 1 or 0 for them but I'm not sure how to examine the final count for each or how I would keep a running tally. Sorry, not looking for hand-holding here but I'm very new to SSRS and trying to translate my Crystal Reports knowledge to this can be challenging at times. – inkswamp Aug 25 '22 at 14:53
  • Got it. Just discovered the "Add Total" feature in the details row. Nice feature and it works exactly the way I need it. Thank you for putting me on the right path here. Appreciate the help. – inkswamp Aug 25 '22 at 17:15
0

Another option is to simply use a Tablix report. For example, set the 'Row Groups' to Name and 'Column Groups' to Color and use Count as the aggregate.

MilletSoftware
  • 3,521
  • 2
  • 12
  • 15