Using Digital Dashboards in MS SQL Server 2008

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.

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.

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