How to Add a Colored Data Bar in SSRS 2008

Clients typically want to see how well their Vendors, Customers, or Projects are performing. We can, of course, put together a report with numbers and calculations, but why not add some color and meaningful visuals to improve readability?

In SSRS 2008, you can add a Data Bar, and with a few customizations you can turn it into a report for upper management that provides a quick overview of the information in a visual format. Let’s take a look at how to accomplish this.

Create Your SQL Query and Dataset

For the purposes of this post I am assuming that you have the basic knowledge of SQL queries and how to create basic SSRS reports. For information on how to accomplish the latter, read this post.

Add a Data Bar

  1. From the Toolbox, click and drag the “Data Bar” item onto the desired cell.
  2.  

  3. Select the first Data Bar display type.
  4.  

  5. Double click on the Data Bar to bring up the “Chart Data” window.
  6.  

  7. Click the + icon to the right of “Values” and you’ll get a list of all your columns. Select the percentage column you want to use. In my case, we are using the “OnTime” column. You can also use a custom calculation by using the “Expression” option at the very bottom of the list.
  8.  

    NOTE: If you select a specific column, it will have the name of your column. If you use a custom expression, the top row will be called “Series.”

     

  9. Right click on the Data Bar and select “Horizontal Axis Properties.”
  10.  

  11. Change “Maximum” to “100” instead of “Auto.” Click OK.

To Set Colors

  1. Double click on the Data Bar, click the Down arrow next to “Series” and select “Series Properties.”
  2.  

  3. Go to “Fill” and click on the “fx” button for Color. You can use the following expression to determine colors:

=IIf(Fields!OnTime.Value <= 0.25, “Red”,

IIf(Fields!OnTime.Value > 0.25 AND Fields!OnTime.Value <= 0.5, “Orange”,

IIf(Fields!OnTime.Value > 0.5 AND Fields!OnTime.Value <= 0.75, “Yellow”,

IIf(Fields!OnTime.Value > 0.75, “Green”, “Transparent”))))

The expression will work as follows:

  • If your value is 25% or less, the bar will be red.
  • If your value is between 25% and 50%, the bar will be orange.
  • If your value is between 50% and 75%, the bar will be yellow.
  • If your value is greater than 75%, the bar will be green.

Click OK and run your report!

Feel free to play with formatting to your heart’s content. Make it look pretty!

July 17, 2013
  • Scott Jackson

    Great article, thanks for writing !

  • Hameed

    May I know how to include data bar in ssrs 2008 as my current MS SQL version is 2008 its not R2. Is there any way to upgrade to 2008 R2 ?

    Please do help me.