Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting Started with Data Bars in SQL Server 2008 R2 Reporting Services


By:   |   Last Updated: 2010-12-22   |   Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services Charts

Problem

I'm looking at several new visualization features in SQL Server 2008 R2 Reporting Services and the data bar looks like something that I could really use. Can you provide an example of how to use this in a report?

Solution

The data bar is a new visualization that essentially provides a bar chart within a single cell in a report. This enables you to easily compare the data in one row to another based on the relative length of their data bars.

In this tip I will walk through creating a report that uses a data bar. As an example I will create a simple report using Report Builder 3 which is available on the SQL Server 2008 R2 Feature Pack site.. A preview of the report is shown below:

create a report that uses a data bar in sql server 2008 r2 feature pack

My report will use the ContosoRetailDW database and show channel sales for a given year. I will use the following query in the report:

SELECT c.ChannelName, SUM(f.SalesAmount) SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimDate d ON d.DateKey = f.DateKey
JOIN dbo.DimChannel c ON c.ChannelKey = f.ChannelKey
WHERE d.CalendarYear = @Year
GROUP BY c.ChannelName
ORDER BY c.ChannelName

To create a new report choose the Table or Matrix Wizard in Report Builder, create a data source, a dataset (i.e. the query above), then add the columns to the matrix as shown below:

to create a new report choose the Table or Matrix Wizard in Report Builde

Note that this gives us calendar years on the rows, channel names on the columns, along with the total sales. To add a data bar to the report, click the Data Bar on the Insert tab as shown below then click in the report cell where you want to render the data bar:

click the data bar on the insert tab

After clicking in the cell where you want to add the data bar, you'll be prompted to choose the data bar type; select the Data Bar as shown below:

after clicking in the cell where you want to add the data bar

After choosing the data bar, the report designer will update and show the following layout:

the report designer will update

Double click inside the cell where you added the data bar to show the Chart Data dialog as shown below:

chart data dialog box

Click on + and select SalesAmount from the available options; the data bar will render based on the Sum(SalesAmount) as shown below:

the data bar will render based on the sum

At this point the report is ready to run and the output is shown below:

run the report and view the output

The data bar allows us to visually compare the total sales of one channel relative to another.

Now let's do another report and show a different type of data bar. I'll use a slightly different query that shows sales grouped by year and channel:

SELECT d.CalendarYear, c.ChannelName, sum(f.SalesAmount) SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimDate d ON d.DateKey = f.DateKey
JOIN dbo.DimChannel c ON c.ChannelKey = f.ChannelKey
GROUP BY d.CalendarYear, c.ChannelName
ORDER BY d.CalendarYear, c.ChannelName

The report will show years on the rows and channels on the columns. Instead of a single data bar, I'll have one for each channel. Here's the report layout in the designer:

report layout in the designer

When I selected the data bar, I picked the Data Column instead of the Data Bar as shown below:

select data bar type

Double click inside the cell with the data bar to show the Chart Data dialog and fill it in as shown below::

chart data dialog

Note that ChannelName has been added to the Category Groups; essentially the data bar is rendering the sum of SalesAmount for each ChannelName. The report now looks like this:

Data Bars are a new addition to SQL Server Reporting Services 2008 R2 and provide a nice way to spice up your reports with a bit of visualization

Note the four vertical bars (one for each ChannelName) versus the single horizontal bar in the first report.

Next Steps
  • Data Bars are a new addition to SQL Server Reporting Services 2008 R2 and provide a nice way to spice up your reports with a bit of visualization.
  • You can download the Contoso Retail DW sample database used in the report from this CodePlex site. Select ContosoBIdemobak.exe and follow the instructions for installing.
  • You can download the sample reports here.
  • You can download Report Builder 3 from the SQL Server 2008 R2 Feature Pack site.


Last Updated: 2010-12-22


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, July 13, 2012 - 10:13:22 AM - Ray Barley Back To Top

Looking through the properties of the data bar, I think it may be possible; drill in to the Chart Areas collection,and you will see a Value Axes collection which has properties in the Scale category like Minimum (default is 0), Scale Break Style, and Cross At


Friday, July 13, 2012 - 3:59:51 AM - vandan Back To Top

Hi Everyone,

I am building a report based on sales person for previous and current business year sales . now the problem is when i calulate the diffrence of two business year's sales  am getting both positive and negative values..

I want to get both positive and  negative values in same 'bar chart'...

can any one help me in solving the above problem

thank you,


Learn more about SQL Server tools