Search code examples
reporting-servicesswitch-statementreportbuilder3.0ssrs-expression

Grouping categories on a bar chart in SSRS

I am trying to group together names, and count the number of tickets each assignee is working on. Then I would like to graph this information onto a bar chart. Each ticket can be assigned to multiple assignee's.

I used the below expression, but I'm unable to get the desired output. The numbers on the chart do not add up correctly.

=SWITCH(Fields!assignees.Value LIKE "*Jerry*",Count(Fields!issue_number.Value),
Fields!assignees.Value LIKE "*Chris*",Count(Fields!issue_number.Value),
Fields!assignees.Value LIKE "*James*",Count(Fields!issue_number.Value),
Fields!assignees.Value LIKE "*Steve*",Count(Fields!issue_number.Value))

Table

Issue_Number   Assignees        
     1           Jerry          
     2           Chris          
     3         James, Jerry   
     4         Chris, Jerry         
     5         Chris, Jerry  
     6           Jerry         
     7           Steve      
     8           Steve

Desired Output

Bar Chart Example:

enter image description here

Solution

  • It looks like the issue is that you aren't specifying a specific condition to count, you're basically just using the same count for each part of the switch expression. You need to actually get the count of each, so you'll need an IIF in the second part of the SWITCH. Try the following expression:

    =SWITCH(Fields!assignees.Value LIKE "*Jerry*", SUM(IIF(Fields!assignees.Value LIKE "*Jerry*", 1, 0)),
Fields!assignees.Value LIKE "*Chris*", SUM(IIF(Fields!assignees.Value LIKE "*Chris*", 1, 0)),
Fields!assignees.Value LIKE "*James*", SUM(IIF(Fields!assignees.Value LIKE "*James*", 1, 0)),
Fields!assignees.Value LIKE "*Steve*", SUM(IIF(Fields!assignees.Value LIKE "*Steve*", 1, 0)))