2

I want to set the background color (gradient) of a column based on another field's value from largest value to smallest.

Refer to the following table:

Gradient example

As shown in above table, the background color of SysName column (instead of Hours column) should be set starting with darker green to light green depending on the value of Hours column.

How can this be achieved in SSRS?

bitnine
  • 1,752
  • 1
  • 17
  • 20
Rameshwar Pawale
  • 632
  • 3
  • 17
  • 35

1 Answers1

3

There is a pretty simple way to set this up, particularly if your value fields are from detail rows.
Here is an example:

Gradient Example

The Fill Color property can take a hexadecimal color string in the format #AABBCC, which has two digits corresponding to each of the color's respective RGB values. An expression can be set up to generate that string. Note that the fill expression references the field by name, so it can be used for any TextBox within the appropriate scope.

Included is a simple version of an expression as its the easiest to look at to understand what's going on. The second expression scales from the minimum value instead of 0 and uses the specific colors of your example.

Simple Version:

="#" + Format(255 - CInt(200 * Fields!Target.Value / Max(Fields!Target.Value, "DataSet1")), "X2") + "FF" + Format(255 - CInt(200 * Fields!Target.Value / Max(Fields!Target.Value, "DataSet1")), "X2")

That provides a linear color gradient from 0 to the max value. The maximum value will result in 55 red, 255 green, and 55 blue, while a 0 value will be white (255/255/255). Note that the static FF is the 255 green.

Minimum Value Version:

In order to have a gradient that uses the minimum value instead the expression gets a little messy, but is still easy to set up.

="#" + Format(255 - CInt(155 * (Fields!Target.Value - Min(Fields!Target.Value, "DataSet1")) / (Max(Fields!Target.Value, "DataSet1") - Min(Fields!Target.Value, "DataSet1"))), "X2") + Format(255 - CInt(55 * (Fields!Target.Value - Min(Fields!Target.Value, "DataSet1")) / (Max(Fields!Target.Value, "DataSet1") - Min(Fields!Target.Value, "DataSet1"))), "X2") + Format(255 - CInt(135 * (Fields!Target.Value - Min(Fields!Target.Value, "DataSet1")) / (Max(Fields!Target.Value, "DataSet1") - Min(Fields!Target.Value, "DataSet1"))), "X2")

Variable Version:

If you want the color values to be easier to change, you can set up report variables that specify the RGB values and the amount they should be adjusted for the maximum value. In that case the expression would be:

="#" + Format(CInt(Variables!MaxR.Value - Variables!AdjR.Value * (Fields!Target.Value - Min(Fields!Target.Value, "DataSet1")) / (Max(Fields!Target.Value, "DataSet1") - Min(Fields!Target.Value, "DataSet1"))), "X2") + Format(CInt(Variables!MaxG.Value - Variables!AdjG.Value * (Fields!Target.Value - Min(Fields!Target.Value, "DataSet1")) / (Max(Fields!Target.Value, "DataSet1") - Min(Fields!Target.Value, "DataSet1"))), "X2") + Format(CInt(Variables!MaxB.Value - Variables!AdjB.Value * (Fields!Target.Value - Min(Fields!Target.Value, "DataSet1")) / (Max(Fields!Target.Value, "DataSet1") - Min(Fields!Target.Value, "DataSet1"))), "X2")

These would be the report variables used to generate the gradient from the second expression:

Color Variables

Note: If your value field is itself an aggregate, another slight adjustment is needed. You need to use nested aggregates for the values, but SSRS doesn't allow you to use a dataset scope with nested aggregates. So you can add a parent group that groups on a constant value, then substitute it for the dataset references.

bitnine
  • 1,752
  • 1
  • 17
  • 20
  • Actually, I wanted to color the SysName column instead of Hours(Value column). So in your case it should be Location column. – Rameshwar Pawale Sep 22 '16 at 13:48
  • My apologies if my stock example image made it unclear, but you can actually use that expression on any or all of the cell's Fill Color properties in the row. – bitnine Sep 22 '16 at 13:51
  • Thanks much. Although, I am just unclear about how should I change the color? Currently, it shows Green color. – Rameshwar Pawale Sep 22 '16 at 14:28
  • I've updated the answer so that the example expression matches the colors from your sample. And if you need to change the color around and don't want to dig into the expression, I've included a way to use report variables instead. – bitnine Sep 22 '16 at 15:31
  • Modular answer - fantastic - do you happen to know anywhere that the web still lists the full valid strings for the Format function (such as "X2")? Looks like the old documentation has been removed – Coruscate5 May 10 '19 at 19:01
  • I need to convert this into SSRS code: background: linear-gradient(180deg, #005AFA 0%, #6400AF 100%); Any idea how to do it? – Mark Jul 20 '21 at 19:18