Conditionally colouring text in SQL Server Reporting Services

A report generated by SQL Server Reporting Services showing conditional colouring of different values in the report

SQL Server Reporting Services is a great tool for building reports quickly, except for the horrendously hard to maintain XML format that the report definitions get persisted in. Recently I was asked if we could tweak a report so negative numbers were displayed in red. It took not very much digging, so here's how.

Using an expression to determine the colour of text

Pick a report, any report, that you've built in SSRS and open it in SQL Server Report Builder (tip: If you've got more than one SSRS server, install the app properly rather than using the click-once version). Now choose a particular cell/value that you want to add some conditional colour to, right-click on it and choose Text Box Properties... from the context menu:

Picking the properties option from the context menu

Then the Text Box Properties window will appear that has a side-bar on the left containing various different options, from which we want the Font one. Clicking on Font swaps out the main pane for various options to setup the font that's used to display the data. Font, Size, Style, Color, Effects and Line spacing. Notably each of these has a button next to them captioned 'fx'.

Click on the 'fx' button next to the Color drop-down to bring up the Expression window where an expression can be defined that will determine the colour of the text in each row of the report. The big text box at the top of the window is where you can specify the expression used to determine the colour of the text, there are plenty of examples at (some of which cover this particular scenario!)

=IIF(Me.Value < 1, "Red", IIF(Me.Value > 2, "Green", "Black"))

That's a nested IIF expression that says: If the value in the field (Me.Value) is less than 1 then the text colour should be Red, otherwise,.. If the value in the field (Me.Value) is greater than 2 the the text colour should be Green, otherwise it should be Black.

There are plenty of other ways you can tweak the formatting of data in reports, the examples I linked earlier are a good starting point!

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment