Connect to TFS using Microsoft Excel

Introduction

Microsoft Excel is a very powerful tool in representing data and chart manipulation. Connection to TFS using Excel helps you to create/modify work item or create reports about your project.

Manage Work Items

Microsoft Excel helps you to do bulk insert/modify work items. You can use Microsoft Excel to upload all your work items, move work items from iteration to another iteration without opening every work item, or doing many modification and publish all of them at the same time.

Add new Work items

You can create your tasks, user stories, Epics and features by using Microsoft Excel and the publish them to TFS. To publish your work items on TFS:

Click on Team Ribbon

Click on New List

Choose your TFS server, Project collection and the Team project that you need to add work items to it.

Choose Input List to create new individual work items

Create your work items in Microsoft Excel

Click on Publish button

Open Project Web Portal to see the new tasks

Create Parent Work Item

Microsoft Excel allows you to add Work Items in tree level structure. Example, You have a system that supports users login. So that you will need to add work Item to called Login Module and its Type is Feature. Under these feature you want to add child user stories that will describe what you need to implement in this module like: user registration, user login and forget password. This is called tree structure, you have Parent work item and child work items that are linked to it.

To add Tree Level Structure:

Click on Add Tee Level Button, Under Team Ribbon

put your parent work Item under Title 1 column and your child work Item under Title 2 column

Publish Your Modification

Open Project Web Portal to view the modifications

If you clicked on Login Module work item, You will find there is a child work item that linked to it.

Modify Bulk Work Items

Sometimes you need to move a group of work items from an iteration to another iteration or modif ybulk of work items in an easy way, So you can use Microsoft Excel to modify your work items and then publish the changes to TFS.

To Modify Work Items:

1. Connect to Team Project in Visual Studio
2. Click on Work Item
3. Do a query on work items that you need to modify
4. Click on Open in Microsoft Office and choose Open Query In Microsoft Excel

 An Excel work​book will be opened with your chosen work items

If you need to modify an attribute which is not appear in the above table, you can click on Choose Columns button under Team Ribbon.

And choose the column that you need to add, I choose Story Point

Story Point column is added to the table.

Below i changed the iteration of work items to Iteration 2

Publish new modifications

Open the query on visual studio, you will find that all work items are moved to Iteration 2

Reports

TFS reports on Reporting service are not rich enough with Filtration or creating custom reports, So integrating TFS with Microsoft Excel provides another way to create custom reports. There are 3 different ways to generate TFS reports using Microsoft Excel.

Generate Reports From Visual Studio

You can generate TFS report from Visual studio through the following steps:

1. Open team explorer in Visual Studio.

2. Connect to your TFS server and your Team Project.

3. Click on Work Item.

4. Right click on query that you want to generate.

5. Choose Create Report in Microsoft Excel. ​

Choose reports that you want to generate 

An Excel Workbook will be generated with All selected reports in the previous step. Each Report will be displayed in a separated sheet. The fisrt sheet contains table of contents of the workbook.

Generate Report From Team Ribbon in Excel

Click on Team Ribbon

Click on New Report

Choose your TFS server, Project collection and your Team Project

Choose the query to generate a report from

Choose reports that you want to generate 

An Excel Workbook will be generated with All selected reports in the previous step. Each Report will be displayed in a separated sheet. The fisrt sheet contains table of contents of the workbook.

Generate TFS report from Analysis Services

Click on Data Ribbon in Excel

Click on From Other Sources

Choose From Analysis Services

Enter your Analysis Server name and its credentials, then click Next

Choose Tfs_Analysis as the database and choose cube or table that will connect to. In this example i used Team System Cube, Then click Next

Enter your conncetion data and then click on Authentication Settings

Choose Use a stored account and write TFS. This will allow the chart or table to auto-update with the latest data each time it is accessed. Then click OK

Choose the data view that you want, Excel sheet and the starting cell. Then click OK

a Pivot Table appears in the sheet. on the left side the pivot table will appear. on the right side there are Pivot Table fields that will be used in Pivot Table creation and data display. on the end of the left side the are four regions, These Regions are used to build the Pivot Table. You will drag Pivot Table Fields from above and drop it in the region you want.

To start creating Pivot Table:

1. You will need to filter by project name. in order to display the current project data. In Pivot Table Fields scroll down to Team Project group, You will find Name under More Fields drag the Name Field To Filter region on the end of left side
2. Drag Work Item.Work Item Type field under Work Item group to Rows region
3. Drag State field under Work Item group to Columns region
4. Drag Work Item Count field under ∑ Work Item group to the Values region

a Pivot Table will appears with values. If you need to create Pivot chart, Click on Pivot Chart button and choose table design that you want

Points of Interest

Microsoft Excel is a very powerful tool in data and reports manipulation and it also save time in insert/modify work items.

References

• Professional Team Foundation Server 2012, ISBN 978-1-118-31409-8

• https://tech.lds.org/wiki/TFS_Reports_using_Excel

• https://agilebackblog.wordpress.com/2012/05/18/custom-burnup-chart-with-tfs-and-excel/