How to Generate a TFS Work Item Report In Excel

TFS (Microsoft Team Foundation Server) is a beautiful tool for managing and coordinating a project as a team. You can do many things using TFS but I won't go into the details of what you can do here.
One of the many things you can do is managing Work Items like:

-Bugs
-Findings
-Issues
-Shared Steps
-Tasks
-Test Cases
-User Stories

Excel may be the greatest tool Microsoft has ever created. So many features and ability to perform many tasks like creating formulas, relating data, sorting, drawing graphics… list goes on and on.

In this post I will be talking about how to use these 2 beautiful tools together to generate an Excel report for a Work Item list in TFS. Excel version is 2010, and for TFS(I mean Visual Studio) I have 2 versions running on the same machine: 2010 and 2012.
If you have Visual Studio 2010 or Visual Studio 2012 already installed, then steps 1,2 and 3 should be enough to enable the “Team” menu that we will need to create the reports since installations of VS 2010 and VS 2012 include Team Explorer in them. If you do not have Team Explorer installed on your computer you should install it as described in step 4.

ENABLING TEAM MENU

1)
First check If you already have the Team menu item. Can you see it in the top menu?

2)
If you don't have this menu item do the following to check if you can add it:

Click File -> Options
Click “Add-Ins”
Select “COM Add-ins” and click “Go”

3)
In the window that opens see if you have the item “Team Foundation Add-in”. If you do than you already have Team Explorer installed on your system, check it and click OK.

Now you should have the “Team” item in the top menu as displayed in the first screenshot.

INSTALLING TEAM EXPLORER

4)
If you do not have the “Team Foundation Add-in” item in the “COM Add-Ins” window then you should install Visual Studio 2010/Microsoft Visual Studio Team Explorer 2010 or Visual Studio 2012/Team Explorer for Microsoft Visual Studio 2012. Team explorers can be installed as a stand alone application if you do not need Visual Studio.

For Team Explorer 2010: http://www.microsoft.com/en-us/download/details.aspx?id=329 (ISO)
For Team Explorer 2012: http://www.microsoft.com/en-us/download/details.aspx?id=30656

You can follow steps 1,2,3 to enable “Team” menu once you install one of the Visual Studios/Team Explorers above.

GENERATING THE REPORT

Now that you have enabled the Team menu, it is time to generate the report.

Click “Team” on the top menu
Click New Report

On the window that opens select your Team Foundation Server, and select your Team Project then click “Connect”.

On “New Work Item Report” window select the query that you have created before on TFS and click Next.
Note: This is the query that will get the desired records from TFS and put them in Excel.

The rest is pretty straightforward.

Hope this helps someone.

Good luck,
Serdar.

Leave a Reply

Your email address will not be published. Required fields are marked *