Creating Reports in BIDS and Visual Studio 2010 and add them as a Web Part to Project Portal of TFS 2010

Posted by: Gouri Sohoni , on 6/26/2010, in Category Visual Studio, VSTS & TFS
Views: 57461
Abstract: In this article we will see how a report can be created with the help of Business Intelligence Development Studio (BIDS), using Microsoft SQL Server 2008 and how it can be added as a web part to a Project Portal of TFS 2010.
In the last article Creating Reports in Microsoft Excel and Visual Studio 2010 (Team Foundation Server 2010) , we discussed how to create and modify reports with Microsoft Excel and VS 2010. In this article we will see how a report can be created with the help of Business Intelligence Development Studio (BIDS), using Microsoft SQL Server 2008 and how it can be added as a web part to a Project Portal.
Reports can be created against the relational database or an Analysis Services cube. Reporting against Analysis Services cube is done when we need to create reports against historical data or use aggregated fields in reports. We use relational database as a data source for the report when we need to create report with transactions and not aggregations or historical data. When Team Foundation Server 2010 is installed, a relational database named Tfs_Warehouse is created. This database has various tables containing data from all the collections in the Team Foundation Server. Data in these tables is in non normalized form. This form is easy for executing reports as searching and sorting happens much faster than those operations in normalized database. In this article, we will create a report using this relational database and later add it as a web part to the portal.
Reports with Relational Database
There are a lot of views provided with this warehouse database.
Views
Note: I have removed the views from the figure which start with ‘v’ and end in ‘Overlay’. These are the views which are used to process the cube so you should not create reports based on these views.
There are tables which start with ‘Dim’ which means they will be used as dimensions in cube and the tables which start with ‘Fact; will have some or other numeric measures.
DBO
DBO_1
This warehouse database contains data about Builds, Source Code, Code Coverage, Tests, Test Results and Work Item details, as can be seen from the names of different tables. The relationship between these tables is maintained in the database as can be seen from the following database diagrams.
Build
DimWorkItem
FactWorkItem
Following are the steps for creating report using Business Intelligence Development Studio (BIDs).
1.    Start BIDs and select project template of Report Server Project.
2.    From Solution explorer create a shared data source which points to the Tfs_Warehouse.
3.    Create a report by using Report Wizard for which the data source is the newly created one.
4.    In the query, select the view named ‘CodeChurView’ and select fields to have the following query
Quer
5.    Select tabular view and specify dateTime as Goup entity and remaining fields as Details.
6.    Specify Table Style, enter name and click on Finish after selecting the check box for Preview Report.
7.    In order to deploy this report we need to select the project properties and specify TargetServerURL for Reporting Service.
DemoReporting
8.    This report can be viewed from Report Server where it will be added in a folder named DemoReporting as shown
Properties
Now that the report is ready let us find out how it can be added on the Project Dashboard. Follow these steps:
1.    By using the Report Manager move the report to the Team Project folder.
2.    From Internet Explorer enter URL for Project Portal
http://vs2010/sites/<Collection name>/<Project name>
3.    In order to add a web part we need to go to the Site Actions and Edit Page
SiteActions
4.    Select the option of Add A web part followed by 'Page Viewer Web Part'
Misc
5.    Edit the newly added web part by taking the option of Modify shared web part
6.    Enter the complete URL for the report to be added after http://
PageViewer
7.    Resize the size of this report by selecting the advanced option in Edit. The size can be specified in cm, inches, mm, points, picas or pixels.
8.    The report is shown embedded on the project dashboard as shown.
CodeChurn
In this article, we discussed how a report can be created and made available as a web part on project portal of Team Foundation Server 2010.
In the next article, we will discuss how to create report based on analysis services cube.
Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Gouri Sohoni is a Microsoft MVP, Trainer and consultant for over two decades. She is a Microsoft Certified Trainer (MCT), MCITP and has conducted several corporate trainings on Microsoft technologies that include Visual Studio 2010 (ALM), SQL Server 2008 BI, SQL Server 2008 developer track, SQL Server 2005 BI, SQL Server 2005 developer track etc.


Page copy protected against web site content infringement by Copyscape


User Feedback

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel