Implementing Data Bars In A Grouped Table

Introduction

Earlier I wrote an article about the new lookup functions that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the visualization department.  This feature is the Data Bar.  With this new component it’s fairly easy to make your tabular data a lot more visual, and thus easier to interpret.  And here’s how to do it.

I’ll be starting from the report that I created in Your First OLAP Report.  That allows me to focus on the visualization part, without first needing to build a table report.  (Okay, I admit, it’s not 100% the same report – I’ve modified the colors a bit because I felt the green was too dark.) But obviously this method will work with any report that’s showing data in a table.

Furthermore I’m running SQL Server 2008 R2 Nov CTP, 64-bit, and I’m using the BIDS to develop the report.

The final result can be downloaded from Skydrive.

Implementing The Data Bar

The report that we’re using is showing some sales figures grouped in three levels: Country, State/Province and City.  We’re going to add an extra column on the right of the table to contain the data bar.  Let’s first explore the Toolbox pane to discover the new visualization report items.

 
The new items have been highlighted in yellow.  As you can see, besides Data Bar there’s also Sparkline, Map and Indicator.  But those are not on topic now.

To add a Data Bar, simply drag it from the Toolbox into a textbox on the report.  Doing that will show the following popup window:

 
From left to right, there’s Bar, Stacked Bar and 100% Stacked Bar.  And those are also available in vertical direction, Column.  I’m going to use the regular Bar as highlighted in the screenshot.

The Data Bar has now been added to the report, but it doesn’t do anything yet.  We first need to tell it what data to visualize.  Clicking it once will select it, clicking it once more will show us the following Chart Data popup:

 
Click the plus icon to get a drop-down of fields in the dataset.  Select the numeric field that you want to visualize, in my case that’s the Reseller_Sales_Amount.

 
By default, the aggregation used on the data is Sum.  But there are other options as well, have a look at the dropdown next to the [Sum(Reseller_Sales…  In the example here I’m going to keep the Sum.

 
The report Preview looks like this:

 
In this report we can easily see what states have got a higher sales amount: those with the longer bars.

Let’s add bars for the Country level as well.  There are two ways to achieve that: you can either drag a new Data Bar onto the report or you can just copy/paste the textbox containing our first Data Bar.

To make the report easier to read I will change the color of the Data Bar to the color of the group’s background.  Setting up the color of the Data Bar is done as follows: select the Data Bar so that you get the Chart Data popup.  In the Properties pane you should see that the Chart Series is selected.  Then right-click on the bar and select Series Properties.

 
In the Series Properties window, select the Fill page and select your favorite color.  If you want you can also use a gradient fill or pattern.  You can even use a Switch statement and color them differently depending on their value, similar to the method that I used in my SSRS and MDX: Detecting Missing Fields article.  Well, in short, any expression that you can think of and results in a color will work fine!

 
With the second data bar and custom coloring set up, here’s the rendered report:

 
Hang on, is that correct? As you can see, the data bar for California is longer than the one for Canada while the sales amount for Canada is definitely the higher one.  An even nicer example is United Kingdom with only one state England.  Both amounts are equal yet their data bars are certainly not.

Well, this is because the data bar by default uses the same scope as the group where it’s put.  Canada and United Kingdom are in the Country group and all Country data bars compare nicely to each other.  California and England are in the State/Province group and also compare nicely to each other!

Depending on the report’s requirements this may or may not be the desired effect.  But I wouldn’t be mentioning this if there weren’t any other options, would I?

Setting The Maximum Value Of The Horizontal Axis

A correct column label for our current column would be “% of group”.  Let’s add a second column which will show the percentage of the row compared to the total of the dataset, “% of overall total”.  After adding the extra column, copy/paste the data bars from the first column over into the new textboxes in the second column.

To get what we want, we need to tell the data bar that the maximum for the horizontal axis is the total of the dataset and not the total of the grouping level of the table.  So, right-click one of the data bars in the new column and select Horizontal Axis Properties.

 
As you can see, the default for the Maximum value is set to “Auto”.  Note that the name of my tablix is “Tablix1”, as shown in the Align axes in dropdown.

 
Let’s replace the Auto as Maximum with the following expression:

=Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")
                      

Select all Open in new window

This expression says that we need the sum of the Reseller_Sales_Amount, scoped over the whole tablix.

Having changed the Maximum value on both data bars gives us the following preview:

 
As you can see, this time around the data bars for England and United Kingdom have gotten the same size.  Bars from different grouping levels can now be compared with each other.

Showing Labels On The Data Bars

In this report it would be interesting to add a label that displays the percentage to the status bars, so let’s do that.

For the percentage calculation of the first column of data bars we need to get the total of the current group and divide that by the total of the group one level higher.  For the State/Province level that gives us the following expression:

=Sum(Fields!Reseller_Sales_Amount.Value)
                          / Sum(Fields!Reseller_Sales_Amount.Value, "grpCountry")
                      

Select all Open in new window

The grpCountry refers to the name of the grouping one level higher, as shown in following screenshot:

 
And for the Country level we need this expression:

=Sum(Fields!Reseller_Sales_Amount.Value)
                          / Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")
                      

Select all Open in new window

The only difference with the previous expression is the scope.

“Ok, so where do I type those expressions?” I hear you think.  Right-click the Data Bar (after first selecting the textbox that contains it) and choose Show Data Labels.

 
That adds a label to the chart but we still need to configure it to show the percentage.  By default it uses the value as label.  This can be verified in the Chart Series properties:

 
The UseValueAsLabel is set to True, confirming what I just stated.  Furthermore, activating the Show Data Labels option also set the Visible property to True.  So another way of adding the label is by just setting this property to True.

Let’s now configure that label.  Right-click it and select Series Label Properties.

 
The General page of the Series Label Properties window allows you to define the Label data.  That’s where you need to enter the expression that I mentioned earlier.

After you’ve entered the expression, the BIDS will ask you if you want to set the UseValueAsLabel to False, so click Yes.

 
With the label selected as shown in the next screenshot you can use the formatting toolbar buttons to give it a decent font and all.

 
For our second column of data bars the expressions need to take into account that we need the percentage as compared to all the data, unrelated to the level.  This actually makes it easier because we can use the same expression on both the State/Province and Country level.  Furthermore, it’s exactly the same expression as the one used on Country level in the first data bar column (i. e. the second expression above) because Country is the highest level.

Let’s have a look at the result in preview:

 
Woah, looks like we forgot something doesn’t it?  We forgot to format the label as being a percentage!  There are two ways to get that done: through the Number page on the Series Label Properties window:

 
Or by using the properties with the label selected (the Properties pane should show Chart Series Labels in the dropdown on top):

 
I’ve opted for a percentage without any decimals.

Let’s have another look at that report:

 
That sure looks better doesn’t it?  The status bars on the right compare over the grouping levels while those on the left compare with each other within the same group.  And they all have a clear label indicating what they represent.

And then colleague-expert Mark Wills jumps in with the following remark (rephrased a little):

Hang on, are you really sure those bars on the left are what they should be?  How's it possible then that 71% of NSW (Australia) appears to be only about 20% and the 100% of England (United Kingdom) is only halfway filled?

Interesting observation and indeed, Mark is right: those data bars on the left are still not right!  Let’s get this fixed.

On the Country level the fix is really easy.  What we want the bar to reflect here is the same as in the bar on the right: the percentage of the country compared to the whole dataset.  That’s achieved using this expression as Maximum in the Horizontal Axis Properties:

=Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")Here’s what that looks like:
                      

Select all Open in new window

And finally, here’s the expression to be used to fix the data bar on State/Province level:

=Sum(Fields!Reseller_Sales_Amount.Value, "grpCountry")Indeed, the data bar on this level should show the percentage compared to all the items in the group.
                      

Select all Open in new window

Final result:


Conclusion: it’s not a good idea to leave the Maximum setting in the Horizontal Axis Properties set to “Auto”.

Have fun putting those data bars on your reports!

PS: instant poll: Y or N?

Valentino.

References
BOL 2008R2: What's New (Reporting Services)
BOL 2008R2: Sparklines and Data Bars
An Introduction to Data Bars in SQL Server Reporting Services 2008 R2

Originally appeared at my blog: http://blog.hoegaerden.be/2010/05/27/implementing-data-bars-in-a-grouped-table