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
- From the Toolbox, click and drag the “Data Bar” item onto the desired cell.
- Select the first Data Bar display type.
- Double click on the Data Bar to bring up the “Chart Data” window.
- 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.
- Right click on the Data Bar and select “Horizontal Axis Properties.”
- Change “Maximum” to “100” instead of “Auto.” Click OK.
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.”
To Set Colors
- Double click on the Data Bar, click the Down arrow next to “Series” and select “Series Properties.”
- 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!