Team Foundation Server (TFS) brings us a lot of improvements for Application Life Cycle Management and work item tracking.
For development teams following an Agile methodology (Scrum), working on a couple of projects and covering a wide set of technologies like ours, it’s fairly common to have a number of team projects for each project we are working on. However, there are a couple of drawbacks when using multiple team projects as described in Good Reasons to not create a new Team Project.
In order to enable managers to analyze and plan our engagement, we needed to find a way to export data from all team projects we are currently working on. Unfortunately, TFS doesn’t support this fairly reasonable request. TFS provides a great integration with Excel, which enables easy exporting, modifying the data and publishing it back to TFS. However, if the query contains work item data from multiple team projects, an error TF208015 is thrown and no data will be exported.
This is a known issue and it in the TFS backlog. Suggested workaround is to create separate workbooks bound to different team projects and then to aggregate the data from tables. This means that you will still need to do a refresh in multiple workbooks.
We managed to find a more elegant way of retrieving the needed data using TFS Power tools. This add-on contains Team Foundation Power Tool (tfpt.exe) – a command line tool that enables you to work with files and directories under version control, team projects, and work items queries.
So, what you need to do is to create a Query in Visual Studio Team Explorer that displays the data you need and then export the data using the following commands. These will generate the TSV(tab separated values) and XML file.
c:\Program Files (x86)\Microsoft Team Foundation Server 2010 Power Tools>tfpt query /format:xml "Engineering Team\Team Queries\Team Sprint Backlog - All Projects" /collection:http://tfs:8080/tfs/tfs%20projects > d:\allprojects.xml
c:\Program Files (x86)\Microsoft Team Foundation Server 2010 Power Tools>tfpt query /include:header,data "Engineering Team\My Queries\AllProducts" /collection:http://tfs:8080/tfs/tfs%20projects > d:\allprojects.txt
Data from this generated TSV (or XML) can easily be imported in Excel and analyzed further, to the great satisfaction of your managers.
Microsoft keeps an open channel and listens to new feature requests from the users, so if you would like to have this feature in a next version of the TFS, go to feature request, register and vote.