How to format background color of a cell which is under a group? - SSRS 2008 R2

0
votes

I have the following matrix:

  • Design view
--------------------------
|  OfficeSID  | [Type]   |
--------------------------
| [OfficeSid] | [Value]  |
--------------------------
  • Preview
----------------------------
| OfficeSID |   A  |   B   |
----------------------------
|    1      | 1029 |  982  |
----------------------------
|    2      |   98 |  782  |
----------------------------
|    3      |  786 |   82  |
----------------------------
|    4      |   29 |    2  |
----------------------------

I want to format background color of the cells i.e 2nd row, 2nd column which is a value field. It is grouped under Type field. The background color should be the following:

  • If value < 0 red
  • If value < 100 and > 0 Orange
  • If value >= 100 Green

I tried using expressions but it dint work.

1
ssrs-2008reporting-servicesssrs-grouping

1 Answers

4
votes

This is a pretty common requirement... It sounds like you're trying to set the BackgroundColor property at the cell level, which is correct, so there's no reason it shouldn't be working.

What expression are you using? I'd use something like:

=Switch(Fields!Value.Value < 0, "Red"
  , Fields!Value.Value > 0 and Fields!Value.Value < 100, "Orange"
  , Fields!Value.Value >= 100, "Green")

Edit after comment:

Hmm, not sure what's going on with your report. I put together a basic example to match your results and it's working as expected.

Put together sample dataset:

enter image description here

Create matrix:

enter image description here

Expression for Value Text Box BackgroundColor property, literally copied and pasted from the answer above:

enter image description here

Final results, which looks like it's working as expected:

enter image description here

So I'm not sure what to suggest here... About the only thing I can think of is whether you have multiple values per OfficeSID/Type combination, in which case you'd need to use an aggregate in the expression, i.e. something like:

=Switch(Sum(Fields!Value.Value) < 0, "Red"
  , Sum(Fields!Value.Value) > 0 and Fields!Value.Value < 100, "Orange"
  , Sum(Fields!Value.Value) >= 100, "Green")

But other than that I guess you could either add the exact details of the data you're using, or start a new report from the ground up in the simplest way possible. This would show if you can get the background working in a new report with no other logic in place.