ARTICLE: Report Services 2008 R2 - Data Bar
SQL Server Reporting Services is now in its fourth major version and each one has gotten better and better with each release. Some may say, “technically speaking it is in its third release”. Sure, but I would argue that the upgrades made in the 2008 R2 warrants the label “major release”. Especially considering that R2 has its very own pricing model! As such, I have listed each of the versions below:
Reporting Services Versions
At any rate, what I really want to show you today is how one could utilize the Data Bar object in Reporting Services 2008 R2.
Step By Step
Step 1: Create a new Reporting Services Project
If you are new to Reporting Services report development, the usual report development environment is Business Intelligence Development Studio (often referred to as BIDS). BIDS can be found under the Start Menu >> All Programs >> Microsoft SQL Server 2008 R2 >> Business Intelligence Development Studio. Walk through the wizard to create a new Reporting Services Project as shown in figure 1 below.
Figure 1: Create a Reporting Services Project
Step 2: Create a new report in SQL Server 2008
To complete this task, select Project and then Add New Item. Select the Report template as shown in figure 2 and name your report to something a little more meaningful.
Figure 2: Add New Report Item
Step 3: Create Dataset and Data Source
Create a dataset called GetDataBarResults using the script in Script 1 below using any local data source you have available. For my example, I will be using the AdventureWorks database.
Open the Report Data pane by selecting View >> Report Data. To create a new dataset, select New in the Report Data pane and then Dataset in the drop down menu. Name your dataset appropriately and create a new data source as shown in figure 3.
Figure 3: Data Source Properties
Click OK, name your datasource and OK a second time to save your data source settings. You should now be back at the dataset properties dialog box as shown in figure 4. Copy the script in script 1 into your query text pane.
Figure 4: Dataset Properties
Script 1: Data Bar Example
CREATE TABLE #DataBar (Name VARCHAR(25), Score TINYINT)
INSERT INTO #DataBar SELECT 'Darth Vader', 52
INSERT INTO #DataBar SELECT 'Yoda', 100
INSERT INTO #DataBar SELECT 'Luke Skywalker', 75
INSERT INTO #DataBar SELECT 'Chewbacca', 45
INSERT INTO #DataBar SELECT 'Jar Jar Binks', 15
INSERT INTO #DataBar SELECT 'Padme Amidala', 85
INSERT INTO #DataBar SELECT 'Obi-Wan Kenobi', 90
INSERT INTO #DataBar SELECT 'Han Solo', 88
INSERT INTO #DataBar SELECT 'R2-D2', 100
INSERT INTO #DataBar SELECT 'C-3PO', 100
SELECT * FROM #DataBar ORDER BY Score DESC
Step 4: Create the Data Bar
Drag a Data Bar object over from the Toolbox onto the report design surface. After you let go of your mouse, you will be prompted to select the type of data bar that you wish to use as shown in Figure 1 below. For this example, I will keep the default horizontal.
Figure 5: Data Bar Type Selector
Click OK and then drag your Score column from the GetDataBarResults dataset in the Report Data pane overtop and into the Chart Data Values section as shown in figure 6.
Figure 6: Chart Data Values
Step 5: Add a Table
Since we really haven’t linked the results to an individual test, we now could add a table to our report. Drag a table over onto your design surface and delete one of the columns. Drag Score from your dataset into the left column and then drag your data bar into the column on the right. A sample of what this may look like is shown in figure 7.
Figure 7: Data Bar Example
And there you have it! A data bar example using SQL Server Reporting Services 2008 R2. With very little effort, you can set expressions to dynamically change the colors of the bars or use a gradient effect. For your convenience, I have included a zip file containing this example project. You can download it HERE.
Since I am writing this in order to help others and if you have found this article helpful, please rate it below. If you would like to provide constructive criticism to help me provide the information that you would like to see, I would love to hear that as well!
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSDBusiness Intelligence Consultant – Pragmatic Works Consultants
Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter
What I understand is that data bar is representing the actual score in form of bar. But Han Solo's data bar (whose score is 88) is longer than that of Yoda (whose score is 100)!!! What am I missing??
Wow! I missed one step in this example. It should automatically do this for you, but for some reason it didn't. At any rate...I will update the blog, but inside the databar series you have to set the horizontal axis to align the values based on the tablix in which I placed it in.
If you have downloaded my example, select the databar. When you see the Chart Data dialog box, right click on the SCORE and then choose Horizontal Axis Properties... Enable the checkbox called "Align axes in" and select the Tablix1. Re-render the report and you should see better results.
Thanks for bringing this to my attention.