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:
- Connect to Team Project in Visual Studio
- Click on Work Item
- Do a query on work items that you need to modify
- Click on Open in Microsoft Office and choose Open Query In Microsoft Excel
An Excel workbook 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:
-
Open team explorer in Visual Studio.
-
Connect to your TFS server and your Team Project.
-
Click on Work Item.
-
Right click on query that you want to generate.
-
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:
- 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
- Drag Work Item.Work Item Type field under Work Item group to Rows region
- Drag State field under Work Item group to Columns region
- 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
