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

Posted by: Gouri Sohoni , on 6/22/2010, in Category VSTS & TFS (Azure DevOps)
Views: 81649
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.

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


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).

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

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:


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.


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
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

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

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.

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce the The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this eBook aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on .NET Standard and the upcoming C# 8.0 too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Gouri Sohoni is a Trainer and Consultant for over two decades. She specializes in Visual Studio - Application Lifecycle Management (ALM) and Team Foundation Server (TFS). She is a Microsoft MVP in VS ALM, MCSD (VS ALM) and has conducted several corporate trainings and consulting assignments. She has also created various products that extend the capability of Team Foundation Server.

Page copy protected against web site content infringement 	by Copyscape

Feedback - Leave us some adulation, criticism and everything in between!
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?

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





C# Book for Building Concepts and Interviews



jQuery CookBook