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