SSRS: Set diff background colors to entire row in Pivot Matrix based on field value , including empty/null cells in that row

Question

** Using SSRS 2008 R2

I came across an issue, when trying to highlight (set background color) an entire row in a Pivot Matrix - it's only changing the backgroundcolor for the cells in the row that have values. the others are staying white. I'm looking to set the background color for the entire row, even those empty cells.

In the editor, I've highlighted the row, and in the "BackgroundColor" property put a switch statement in for the expression.

=Switch(Fields!Department.Value="Accounting", "Yellow", Fields!Department.Value="HR", "Blue",Fields!Department.Value="Marketing", "Red")

Department     JAN      FEB     MAR     APR     MAY
-----------------------------------------------------------
John                     3                       2
Mark            1        4               1
Tim                              2

So in the example above, this works only for the Employee Name cell, and the cells with number values, the rest (empty cells) have no background color.

Any help in how to get the entire row, IE for John (in Accounting) how to get JAN, MAR, APR to be yellow as well?

Any help would be much appreciated, Thanks!!

Answer 1

As discussed in the comments, my solution was to create a hidden column using the department field and then referring directly to the textbox. You would add a new column after your month/date column, outside of any grouping on the columns. You can right click the column header and navigate to "Column Visibility" and just select the Hide option. From there, enter the Department field into the textbox aligned with each name and date. I'm guessing the default textbox name will be something like Textbox6 so just reference it with ReportItems!Textbox6.Value. The expression should look like the following.

=Switch(ReportItems!Textbox6.Value="Accounting", "Yellow",
        ReportItems!Textbox6.Value="HR", "Blue",
        ReportItems!Textbox6.Value="Marketing", "Red")