by May 1, 2010 0 comments



One of the current trends in Business Intelligence is the digital dashboard.
The dashboard on a car tells the driver the current state of the car’s
operations: current speed, current amount of gas in the tank, etc. A digital
dashboard tells a decision maker the current state of the business. You can use
many of the features provided in SQL Server Reporting Services (SSRS) to prepare
your digital dashboard. An interesting new feature in SQL Server 2008 is the
gauge data region, which we will be exploring here.

We shall discuss an example of using the gauge data region for a digital
dashboard. For the example over here I am using the AdventureWorks2008 database,
which can be downloaded from http://www.codeplex.com/. Let’s discuss the various
steps involved.

Direct Hit!

Applies To: Database developers
USP: Learn to use gauges in your digital dashboard

Primary Link:
www.codeplex.com
Search Engine Keywords:SSRS,
Gauge

Creating the project

Here, you start the Business Intelligence Development
Studio, an IDE based on MS Visual Studio for developing BI solutions.

 

Click on the Create Project link as highlighted above.

 

Select the Report Server Project template and name your
project as Digital Dashboard.

 

Right click on the Solution Explorer to the right side of
your screen and select Add New Data Source.

 

Select an appropriate server name for your system.

Creating the project

Right click on the Reports folder in Solution Explorer and
select Add New Item.

 

Name your report as Sales By Category.

In the Report Data window to the left, select a new Data
Source.

 

Select the shared Data Source created earlier.

Specifying the query
Right click on the new data source in the Report Data window and select Add
Dataset.

The query below lists the sales for a given category name.

SELECT
Sales.SalesOrderDetail.SalesOrderID FROM Sales.SalesOrderHeader

INNER JOIN Sales.SalesOrderDetail ON
Sales.SalesOrderHeader.SalesOrderID =

Sales.SalesOrderDetail.SalesOrderID

INNER JOIN Production.Product ON
Sales.SalesOrderDetail.ProductID = Production.Product.ProductID

INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =


Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
Production.ProductCategory

Right click on the new data source in the Report Data
window and select Add Dataset. The query above lists the sales for a given
category name.

ON Production.ProductSubcategory.
ProductCategoryID = Production.ProductCategory.ProductCategoryID

WHERE Production.ProductCategory.Name
= @CategoryName AND OrderDate = ‘2004-03-31’

 

Adding the gauge

Select the Gauge data region from the toolbox towards the
left of your screen.

 

 Draw the Gauge on the design surface in the middle of
the screen. Select the type as Radial.

 

Click on the Gauge and select RadialPointer1. Select Sales OrderID from the drop down.

 

In the value property towards the right, change the Sum
function to Count.

Viewing the report

Click on the Preview page. Specify the category as Bikes
and click View Report. View in the Full Screen mode if required.

Summary
SQL Server Reporting Services provides a good number of out-of-the-box
features that let you build simple and complex reports with ease and later
deploy and deliver them to your users. Refer to the online documentation for
details.

Amaresh Patnaik

No Comments so far

Jump into a conversation

No Comments Yet!

You can be the one to start a conversation.

Your data will be safe!Your e-mail address will not be published. Also other data will not be shared with third person.