Creating Reports in Microsoft Excel and Visual Studio 2010 (Team Foundation Server 2010)

Posted by: Gouri Sohoni , on 6/22/2010, in Category Visual Studio, VSTS & TFS
Views: 70367
Abstract: In this article we will focus on how a new report can be created with Excel and also how an existing report can be modified using Excel and Visual Studio 2010.
There are a lot of out-of-box reports available with Visual Studio 2010. These reports vary depending upon the Process Template selected to create Team Project (MSF for Agile Version 5.0 or MSF for CMMI version 5.0).  With Visual Studio 2010, dashboards reports are also added. There are many reports which are in Microsoft Excel. We can differentiate all these reports under 3 categories, Report Manager Reports, Excel Reports and Dashboards.
 In this article, we will focus on how a new report can be created with Excel and also how an existing report can be modified using Excel.

 

Let us see how to create a report using WorkItem Query and view in Excel
We can select flat list query to generate report.
1.    Select ‘Add Query’ right clicking on "WorkItems" from Team Explorer and select Type of Query as Flat List (the default choice) as follows.
NewQuery2

You will observe that the query is referring to the current project. Run the query for all WorkItems for which the state is Active. Select the option of Create Report in Microsoft Excel

FlatList
OR

If a query is already created and saved in Team Foundation Server, open a new workbook in from Microsoft Excel and select New Report tab. Connect to Team Foundation Server and then select query.
 
2.    You can generate 2 kinds of reports. One for current and the other for trend reports (6 reports in all).

NewWorkItem
3.    Click on Finish to view all these reports in separate worksheets. The first worksheet shows the table of contents as follows

NewQuery
 
4.    Now we can view all the reports by just selecting the one. The last link for ‘How to: Edit reports in Microsoft Excel’ requires internet connection or connectivity to the proxy server.

Some of the reports are as follows:

workItem
WorkItem_1

The last report showing trend for work items assigned.
 
5.    The ‘Column Options’ provide a way to customize the columns to be added to report.
 
6.    This new report can be saved along with other existing reports which are then reflected on the project portal with Excel reports.
We can do the modification to the existing report by opening it in Microsoft Excel and doing changes. Follow these steps:
a.    Connect to the Team Project by using Team Explorer. Double click the report to be changed from Documents – Excel Reports.

CodeChurn

The report will be opened after giving a warning for opening the file.
 
b.    The default report is shown for last 4 weeks. I removed the Row label and it shows as follows
SSGS
It shows total number of lines added, modified and deleted for all the projects in DefaultCollection.
 
c.    We can change the parameter selection to suit our requirement. In this case it is selected for a particular team project and only for one for one of the area hierarchy from it

SSGS_1 
 
which will reflect on the number of lines as follows
Value
 
d.    Further customization is done to add Month as Row Label and it looks as follows

UserInterface
 
e.    The modified report can be replaced with the existing report or a new report can be saved keeping the original intact.
Note: Microsoft Office with Excel should be installed on the box which has Team Explorer installed and is in the same domain as Team Foundation Server.
In next article we will discuss how to add a report to Project Dashboard as a web part.
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
Comment posted by Arun on Tuesday, February 8, 2011 12:22 PM
Thanks Gouri. One correction in bullet 1. It should say Select "Add query" option by right clicking on "WorkItems" to get a new Query.
Comment posted by Carol on Friday, February 11, 2011 6:39 AM
Thanks Arun. I have made the changes on the author's behalf.
Comment posted by Conner on Tuesday, April 26, 2011 3:41 PM
Thanks for the article Gouri.  But are we able to export into different file versions of Excel?

-Conner
http://www.oneclickcommissions.com/excel-reports.html
Comment posted by Kishor Mali on Wednesday, November 19, 2014 3:32 AM
Error after selection of query
TF208098 : excel unable to access Analysis Services DataBase TFS_Analysis on Server SQLSERVER

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