Data Bars are a new feature in SQL Server Reporting Services 2008 R2 introduced with the November CTP. Data Bars are useful for building what are often referred to as “trellis” and “table lense” displays that help report consumers indentify possible correlations between multiple values, or in the case of a single column, to simply get an idea of the distribution of small and large values, and the differences between them.
I’ve taken the NFL report from a previous post and added a few columns to it to show what is possible using the new data bar feature in SQL Server Reporting Services.
The first column to the right of the sparkline displays the team’s turn over ratio. One more column to the right displays the team’s points per game versus the league average. The final column displays the team’s yards per game statistic.
When I first look at this table, which is sorted by total wins, I see a couple of things. First, the Denver Broncos gained the most yards, but turned the ball over a lot (which probably explains why they missed the playoffs last year and maybe why the Bears are having problems this year :) ). Second, the teams that score less points than the league average also tend to lose more games than average. That’s a bit obvious, but notice that the Steelers actually scored less than the league average, but had the third most wins. Without the aid of the data bars, I suspect it would take much longer to see these insights.
To build this report, you will need to download the NFL database here. Also download the NFL report with the win-loss sparklines as this serves as the starting point for this exercise. In part 1, we’ll build a data bar to track points per team, and in future posts we’ll handle building out the turn-over ratio and points per game versus average columns that go a little more in depth with what you can do with data bars.
To insert a data bar in your report, you can simply pick the tablix cell you want to place the data bar in and go to the ribbon and Insert —> Data Bar. That provides you with the following data bar picker page.
We built support in for a few different data bar types; the standard data bar, the stacked data bar, the stacked 100% data bar, and we also added the column versions of each of these. In this case, let’s pick the standard data bar.
Once the data bar is added to the report, we need to give it a value. For this first example, let’s use the “Score” field from the dataset.
If we run this report, it should look like this.
Select the data bar, right click and bring up the horizontal axis properties property page. Let’s spend a few minutes here to go over these options.
At the top of this page, you will see that the “Align axes in:” option is set to “Talbix3”. This setting works in conjunction with the Axis Min and Axis Max settings direclty below it. For cases where the Axis Minimum or Maximum are set to “Auto” the group maximum or minimum will be used to define the axis range for the data bars. In the sample below, I’ve copied the data bar to a new column to the right and simply changed the minimum setting to Auto. Notice the distinction between the two columns. The column on the right almost implies that the Cincinnati Bengals scored very few points in the 2008 season, when they actually scored 204 points. Personally, I prefer the view on the left where the axis minimum is set at zero.
The final report based on this introduction to data bars can be found here.
In future posts, we’ll dig into some additional formatting options for the data bars and how to handle scenarios where you have both positive and negative values (as shown in the first report in this post).