Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Report Services 2008 R2 - Data Bar

  • 25 May 2010
  • Author: briankmcdonald
  • Number of views: 10288
  • 0 Comments

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

  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2

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

New Report 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

Add 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

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

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

Data Bar 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

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

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, MCSD
Business 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

Print
Categories: Reporting Services
Tags:
Rate this article:
3.0

briankmcdonaldbriankmcdonald

Other posts by briankmcdonald

Please login or register to post comments.