Getting Started with Data Bars in SQL Server 2008 R2 Reporting Services
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?
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:
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:
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:
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 choosing the data bar, the report designer will update and show the following layout:
Double click inside the cell where you added the data bar to show the Chart Data dialog as shown below:
Click on + and select SalesAmount from the available options; the data bar will render based on the Sum(SalesAmount) as shown below:
At this point the report is ready to run and the output is shown below:
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:
When I selected the data bar, I picked the Data Column instead of the Data Bar as shown below:
Double click inside the cell with the data bar to show the Chart Data dialog and fill it in as shown below::
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:
Note the four vertical bars (one for each ChannelName) versus the single horizontal bar in the first report.
- 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.
About the author
View all my tips