There are scenarios when you need to create a dashboard which requires visual way of presenting data. Speedometer graphs can be used to visually indicate the health of project by showing needle indicator against major attributes of the project.
For example an speedometer needle can show the variance between budget and expense of the project.
If you think you need a similar speedometer graph for your project and looking for step-by-step instruction on how to do it - please read on...
Above speedometer graph is actually an SSRS (SQL Server Reporting Services) report built using Visual Studio.
I am including all the details which may not be needed for advance users who have worked on SSRS report. I have broken this down to mainly
- Tools needed and Installation of those tools
- Design and Development
- Deployment
Feel free to skip through the sections as per your convenience.
Tools needed and Installation:
To create SSRS Reports - you need to have Visual Studio and Report Extension tools installed. If you don't have Visual Studio installed - go to Microsoft site and install a trial version (normally available for 90 days). I would recommend downloading 2010 or 2012 version.
By default when you open Visual studio and create a new Project - you will not see Report Server Project Type which is part of Business Intelligence (BI) Project Templates. If you don't see Business Intelligence Project templates listed then you need to install SQL Server client/data tools (SSDT) or Business Intelligence Development Studio (BIDS) based on what version of Visual Studio you have installed.
SSDT: http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx if you are using 2010 and higher version
BIDS: http://technet.microsoft.com/en-us/library/ms173745(v=sql.105).aspx if you are using Visual Studio 2008 or lower version
Please note that Microsoft Visual Studio 2010 does not support Business Intelligence Development Studio Integration Services, Report Services and Analysis Services projects for SQL Server 2008 and SQL Server 2008 R2. To work around this issue, you can install Visual Studio 2008 alongside Visual Studio 2010 on the same machine and then open the Business Intelligence Development Studio projects in Visual Studio 2008.
I would recommend to use VS 2010 or higher as it makes deploying reports to SharePoint easy.
So after you have VS and Report Extension installed. Create a Report Server Project. You will the following structure created under Solution Explorer.
Design and Development:
Before we go further, let's take a step back and think on the data. In actual project scenarios, you might already have data set to work upon. In our case we would need to create a table for test data. We are creating a table to hold Budget, Expense and Period information.
Here is the script if you want to create this test table in your environment.
USE [TestDB]
GO
/****** Object: Table [dbo].[BudgetExpense] Script Date: 10/31/2013 2:19:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BudgetExpense](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Period] [date] NULL,
[Budget] [float] NULL,
[Expense] [float] NULL
) ON [PRIMARY]
GO
Now let's go back to Visual Studio and start delving in. Creating a Report is basically three step process
- Create Data Source
- Create Data Set
- Create Report
First steps is your database connectivity. Second step is the business logic to arrive at data set and third step is the presentation layer associated with the data set.
Shared Data Sources: We generally create a Shared data sources as we can use one data source to connect and create one or many data sets.
You can click on Edit to build the connection.
Shared Data Sets:
I have used a query to get Data Set results but you can use other options as it fits. Here is the query in case you want to use in relation to the Test Table I created:
select q1.Budget, q1.Expense, q2.Score from (Select sum(Budget) as Budget, sum(Expense) as Expense, 1 ID from BudgetExpense) q1
join
(Select
(select sum(Expense) from BudgetExpense)/(select sum(Budget) from BudgetExpense)*100 as Score, 1 ID) q2 on q2.ID = q1.ID
Reports - Now we come to Presentation layer. You can right click on Report folder and click on "Add new Report" to start. But if you are not using embedded data sets then start with right click and clicking on "Add new item" and then Report Type Project
Now after we add report project, first step is to choose/create Data Source and Data Set. You can choose Shared Data Source and Data Set created during previous steps or create new ones. In this example, we would use Shared ones.
Now the next step is to add Speedometer Gauge. The Gauge data region is a one-dimensional data region that displays a single value in your data set You can read more about it here
Change scale properties
Add Range, Change Range Properties
Gauge Data, Expression
Preview
Add a few labels and data:
Preview:
Score Percent
Preview
Deployment to SharePoint: Deployment to SharePoint is easy. Ensure that reporting services is enabled in your SharePoint environment and you have reporting features activated in your site collection. Enter SharePoint site URL and document library address to which you want the report files to be uploaded. Once you click on deploy - it will upload report and data connection files to SharePoint.
You can use Report Viewer web part to display your reports.