SRSS Conditional formatting colour with value ranges?

Question

I have a sql reporting services report that displays sales from all of our stores etc it includes a column which displays a % to budget value. What I want to do is if the value is >= .35 then make it the background of the cell Green, if the value is < .35 and > .30 then Yellow if the value is < .30 then Red.

It's really doing my head in I just can't seem to get it to work with all 3 ranges.

I can put in the background color properties of the cell the expression:

=IIF(Fields!ID__to_Budget.Value >= 0.35, "Green", "Red")

And that works, but of course I don't have any Yellow range, anything under .35 becomes red.

I have been trying to do a nested expression on the background color like this:

=iif( (Fields!ID__to_Budget.Value >= 0.35), "Green",
      ( iif(Fields!ID__to_Budget.Value <0.35 and > 0.30, "Yellow",
         iif(Fields!ID__to_Budget.Value < 0.30 "Red", "White") ) ) )

but it complains about a syntax error on the "and >0.30" part.

Many thanks Lance

ErikE ErikE · Accepted Answer · 2013-04-04T01:35:45

You're missing a comma. But, nested Iifs are a pain, so to do the simple function route, I would use the Switch function for this, instead, so you don't have to nest anything--it will be easier to get the syntax right.

=Switch(
   Fields!ID__to_Budget.Value < 0.30, "Red",
   Fields!ID__to_Budget.Value < 0.35, "Yellow",
   Fields!ID__to_Budget.Value >= 0.35, "Green"
)

See Reporting Services Expression Examples for more on this function.

But it seems to me that encoding the "severity" of the values is something better done in the database:

CREATE TABLE dbo.BudgetSeverity (
   FromValue decimal(20, 10) NOT NULL PRIMARY KEY CLUSTERED,
   ToValue decimal(20,10) NOT NULL,
   SeverityLevel tinyint NOT NULL
);
INSERT dbo.BudgetSeverity
VALUES
   (-9999999999, 0.3, 1),
   (0, 0.35, 2),
   (0.35, 9999999999, 3)
;

Then in your data set, join to this table.

SELECT
   ...,
   BS.SeverityLevel
FROM
   ...
   INNER JOIN dbo.BudgetSeverity BS
      ON B.ID__to_Budget >= BS.FromValue
      AND B.ID__to_Budget < BS.ToValue

Finally, in your report:

=Choose(Fields!SeverityLevel.Value, "Red", "Yellow", "Green")

This way you can change the severity levels without having to deploy a new report. If you don't like the idea of putting this in the database, you still have other options. In the Code section of the report you could write a simple function to return a color based on the value passed in. Then you can use a Case statement which will be much, much easier to manage, and you can reuse this function in different fields, making it easier to update all of them at once if something changes.

SRSS Conditional formatting colour with value ranges?

2 Answers