Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Data Driven Colored Text for Reporting Services Reports


By:   |   Last Updated: 2012-02-13   |   Comments (10)   |   Related Tips: > Reporting Services Dynamic Reports

Problem
How can I color code certain entries in a SSRS report?  In SSRS you can use data driven expressions to color code certain rows, so check out this tip to see how to accomplish this.
Solution

Before we get started, I'm assuming you already have a report created. In this example, I'm using the AdventureWorks database and I'm running a report on Name, Email, Hire Date, Title and Pay Rate.  My sample report looks like the following.

AdventureWorks SSRS report output

Formatting Needs

I want to distinguish three different levels of pay. If the Employee makes $10.00 or less I would like to change the text Red. If the employee makes between $10.01 and $20.00 I would like to keep the text Black and if the employee makes more than $20.00 I would like to change the text Green.

Changing Text Color

First, go to the Design tab of Designer view and select all the fields in which the color of text needs to change. In this example, I'll select all fields.

selecting fields to apply formatting to

Next, I'll go to the Properties Window. If you don't see this window you can choose View, Properties or simply hit F4.

In the Properties Window click the arrow beside Color and choose Expression:

ssrs development properties window for colors and expressions

In the Expression box type your VB expression and click OK. In this example I'm using the following:

=SWITCH(Fields!Pay.Value <= 10, "Red", Fields!Pay.Value >= 20, "Green")


ssrs expression configuration

This expression is saying if the Pay column value is less than or equal to $10.00 change the text Red or if the Pay column value is greater than or equal to $20.00 change the text to Green otherwise leave as is.

After clicking OK, click Preview in the Design view and we can see the report color changes.

ssrs formatted report with different colors

Changing Background Color

Also, if you'd like to make the colors stand out more you can change the background of the row instead of the actual text color. 

In the Properties window change the text color back to Black and use the above expression in the Background Color:

change ssrs background colors

After saving changes, preview and we can see what the changes look like.

formatted ssrs report with different background colors
Next Steps

Learn more about SSRS:



Last Updated: 2012-02-13


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 21, 2017 - 10:18:19 AM - Raul Back To Top

 Can this be used based off a formula calculation?

 

=FORMATPERCENT((Sum(Fields!Billable.Value)/(Sum(Fields!Productive.Value)+Sum(Fields!Non_Productive.Value)+Sum(Fields!PTO.Value))),1)

 

 

 

 


Monday, January 12, 2015 - 4:21:13 PM - Laura Back To Top

I just figured it out on my own but I did it on all the fields so I used this:

 

=SWITCH(IsNothing(Me.Value) ,

 

"Plum")

 

 

 

so on my question I would have done this for a more specific field. I am really new and teaching myself

 

=SWITCH(IsNothing(Fields!military_status.Value) ,

 

"Plum")

 

 

 


Monday, January 12, 2015 - 4:00:47 PM - Laura Back To Top

What if you want to highlight a null field? I know normally in my query I would say if miltary_status is null, then

 

what do you use in expressions? this is what I have:

 

=SWITCH(Fields!military_status.Value isNothing,

 

"Red")


Monday, October 20, 2014 - 8:04:30 AM - Romesh Back To Top

Thank you for this post,

 

But I am facing one problem in SSRS, actually I have one date filed with time format yyyy-mm-dd hh:mm:ss, and I want to change color according to today or now base. Means in database having today date and time that column should be show another color, rest of as it is showing.

I used   = IIF(Field.Column.value = today(), “Red”,”White”) Or = IIF(Field.Column.value = Now(), “Red”,”White”)

But it’s not working.

 

Please help me.

 


Friday, April 11, 2014 - 3:51:59 PM - Ankit Shah Back To Top

all your posts always best and very helpful Brady..

-- Ankit shah


Tuesday, September 10, 2013 - 4:56:34 AM - Karan Back To Top

Hi,

What if I want to select top 5 rows and change their color as red.

Selecting top 5 values is main agenda.

Thanking in anticipation.

Karan Kapoor


Wednesday, May 01, 2013 - 1:21:41 PM - Maggie Back To Top

I have a business requirement asking to filter report by color. E.g., filter by Red, Green, Yellow color-coding. Is that possible? Can they filter on a SWITCH attribute?


Friday, February 22, 2013 - 12:20:36 PM - Pajani Back To Top

Hi there, Thanks for your article. Wondering whether can you help me in this regard? In my SSRS report, i want to format column contents on this logic that if the column value is "abc:123" then before column value gets bold,i mean in this form. "abc:123". Any possibility ?

You select the Markuptype is HTML for the text box,  Then value property of text box write  like this.

=

"<font color=#ff0000><b>"+FormatDateTime(now,DateFormat.LongDate)+ "</b></font> " +FormatDateTime(now,DateFormat.LongTime)


Tuesday, February 12, 2013 - 4:54:23 PM - Rosy Back To Top

 

Really it is very helpful and thank you!!


Monday, July 02, 2012 - 11:39:19 AM - Faisal Back To Top

Hi there, Thanks for your article. Wondering whether can you help me in this regard? In my SSRS report, i want to format column contents on this logic that if the column value is "abc:123" then before column value gets bold,i mean in this form. "abc:123". Any possibility ?


Learn more about SQL Server tools