SSRS Conditional Formatting

Question

I am working on SSRS Ranking report, where Rank 1 should have background of Green and last Rank should have background of Red.

Example below:

I tried using custom code but that's not working for me as below:

SSRS Expression used as below:

=Code.RankColour(me.value, 1, Fields!RankName.Value)

And RankColor code as below:

Public Function RankColourTotals(ByVal Value As Decimal, ByVal MinValue As Decimal, ByVal MaxValue As Decimal) As String
    Dim strColor As String

    Select Case Value
        Case MaxValue
            strColor = "Salmon"
        Case MinValue
            strColor = "LightGreen"
        Case Else
            strColor = "Gainsboro"
    End Select
    Return strColor
End Function

Note: I am using SQL 2008 R2

score 3 · Answer 1 · answered Dec 17 '15 at 06:35

3

You can do this from a single expression within your text box.

Set the BackgroundColour of your textbox to be

=iif(Fields!myRank.Value = min(Fields!myRank.Value, "DataSet3"),
    "Green",
    iif(Fields!myRank.Value = max(Fields!myRank.Value, "DataSet3"),
        "Red",
        "White"
    )
 )

Will give the result

This is because you are searching the current value of myRank against the maximum and minimum values of myRank within the entirety of your dataset DataSet3.

Please let me know if this solves your problem, or if you require further assistance.

answered Dec 17 '15 at 06:35

Jonnus

2,988
2
24
33

Thanks it works, my only concern is performance since I need to do conditional formatting for 40+ attributes, but will let you know how I go – user2827587 Dec 17 '15 at 07:11
1

Another option is to set the value of the colour in the data source. If for example a SQL query or view examine the value of 'myRank' and return a string value which is the colour name, call the column BGColour. Assign the string value to the background colour attribute for the cell in the tablix. – SQLBobScot Dec 18 '15 at 14:43

score 3 · Accepted Answer · edited Dec 21 '15 at 14:39

Finally I ended up using function call, so I get flexibility to change color at 1 place instead of changing expression in all the coloumns (if business decides to change color). And my code is as below

Public Function RankColour(ByVal Value As Integer, ByVal MinValue As Integer, ByVal MaxValue As Integer) As String
Dim strColor As String

Select Case Value
    Case MaxValue
        strColor = "Salmon"
    Case MinValue
        strColor = "LightGreen"
    Case Else
        strColor = "White"
End Select
Return strColor
  End Function

And then expression used in column cell as below:

=Code.RankColour(me.value, Min(Fields!AbsenteeismRank.Value, "dataset1"), Max(Fields!AbsenteeismRank.Value, "dataset1"))

For information if you need to perform multiple updates as an rdl file is effectively XML you can open it in a text editor and do a simple find and replace to update the colors in all the expressions — Jonnus, Dec 21 '15 at 14:43

score 0 · Answer 3 · answered Aug 30 '18 at 18:51

0

I used this simple expression at the 'Font / FontWeight' property in order to switch to Bold all non-zero values:

=iif(Fields!ingresos.Value > 0, "Bold", "Default" )

It works!

answered Aug 30 '18 at 18:51

Jhollman

2,093
24
19

SSRS Conditional Formatting

3 Answers3

Linked