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
in Category Visual Studio, VSTS & TFS
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.
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.
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.
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.
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
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.
8. This report can be viewed from Report Server where it will be added in a folder named DemoReporting as shown
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
4. Select the option of Add A web part followed by 'Page Viewer Web Part'
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://
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.
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.