Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Implementing Data Bars In A Grouped Table

ValentinoVBI Consultant
CERTIFIED EXPERT
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2017: MVP Data Platform
Published:
Updated:

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 Reporting Services 2008 R2 report items: Map, Data Bar, Sparkline and Indicator
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:

 Select Data Bar Type 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:

 Data Bar: Chart Data
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.

 Data Bar: Chart Data with Reseller_Sales_Amount selected
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.

 Other aggregation functions of the Data Bar
The report Preview looks like this:

 Report preview with the data bar
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.

 The right-click menu on the Data Bar.
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!

 Series Properies: setting up the Fill color
With the second data bar and custom coloring set up, here’s the rendered report:

 Report with data bar on two grouping levels
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.

 Right-click menu on tablix textbox with a data bar
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.

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

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

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:

 Report rendered with status bar scoped over whole dataset
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")

Open in new window


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

 The groupings as defined on my example tablix
And for the Country level we need this expression:

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

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.

 Show Data Labels in right-click popup on Chart Series
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:

 Chart Series properties: UseValueAsLabel
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.

 Right-click menu on label
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.

 Do you want to set the UseValueAsLabel to False?
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.

 Data Bar with Label selected
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:

 Preview of report with labels on data bars
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:

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

 Chart Series Labels properties
I’ve opted for a percentage without any decimals.

Let’s have another look at that report:

 The final report with two data bar columns
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:

Data bar on Country level now reflects the percentage of the group
=Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")Here’s what that looks like:

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.

Open in new window


Final result:

All data bars fully operational as advertised!
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 
4
10,091 Views
ValentinoVBI Consultant
CERTIFIED EXPERT
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2017: MVP Data Platform

Comments (1)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Voted yes.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.