Implementing Data Bars In A Grouped Table

Earlier I wrote an article about the new lookup functions that ship with SQL Server 2008 R2.  Today 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 here.

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")

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")

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")

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:

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

Here’s what that looks like:

Data bar on Country level now reflects the percentage of the group

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.

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!

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

Share

Tags: , , , ,

  1. Paul Goldy’s avatar

    Thanks for the tip on setting the MAX and MIN values of the data bars in the different row groupings. Helped the databars line up for proper visual scaling. Very nice.

    Reply

    1. Valentino Vranken’s avatar

      Hi Paul,

      Good to know that you found the article useful. Thanks for your comment!

      Regards,
      Valentino.

      Reply

  2. Bruce Bacher’s avatar

    Great article. Thanks for the writeup.

    Reply

  3. SQL training’s avatar

    How does SQL Server 2008 R2 differ from the other edition?

    Reply

    1. Valentino Vranken’s avatar

      Check out the first link right under the “References” title, at the bottom of the article. It probably answers your question.

      Reply

  4. Warren Kimmel’s avatar

    I’ve been wondering how to do that. Thank you for all the clear detail in the tutorial.

    Reply

  5. Scott’s avatar

    Nice! Worked great… thanks.

    Reply

  6. Emily’s avatar

    much appreciated. i will pass this on to friends using SSRS 2008 R2

    Reply

  7. Valerie’s avatar

    The data bar is helpful, thanks for the post.

    Reply

  8. Valentino Vranken’s avatar

    Thanks all for your nice comments! I’d like to let you know that the article has been updated recently. One of my colleague-experts at Experts Exchange let me know that the data bar on the left wasn’t showing what it was supposed to, now it does :)

    For the updated section, look for “And then colleague-expert Mark Wills jumps in” and read from there on.

    Have fun!
    Valentino.

    Reply

  9. Miguel’s avatar

    Thank you . Great article.

    Reply

  10. Mike Glasser’s avatar

    Great article. You may have written it long ago, but it just helped me out tremendously. Yet, I still have one problem you did not cover. When I add Total to my group and then try to put the data bar in the total row, I cannot get the total amount to align with the rest of the table. I have a detail a row and a parent group and then a total row before that. Unlike your example, I am using a SUM of differences, so let’s say my scale ends up being -100 to 50. My total of -80 is taking up the entire length of that cell, rather than being shorter than the -100 group. Any suggestions?

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved