Walkthrough: Managing Work Items in Microsoft Project and Microsoft Excel

Article
11/16/2012

This walkthrough describes how to manage work items in Microsoft Project and Microsoft Excel.

Tasks illustrated in this walkthrough include the following:

Creating a Microsoft Excel spreadsheet from a work item query.
Creating a new software requirement in Microsoft Excel.
Synchronizing a work item list in Microsoft Excel with the work item database.
Importing work items into Microsoft Project.
Creating a new task in Microsoft Project.
Creating read-only views in Microsoft Project.
Synchronizing Microsoft Project with the work item database.
Viewing and updating the Project Checklist.

Prerequisites

To complete this walkthrough, you need:

A team project on which you have sufficient permissions to access and create work items.
Complete Walkthrough: Creating a New Team Project to create the AdventureWorks team project.
One or more Team Foundation users to whom you can assign work items. For more information, see How to: Add a User.

Required Permissions

To perform this walkthrough, you must be a member of the Contributors group on the team project, or have the View work items in this node, and Edit work items in this node permissions set to Allow. Also you must be a member of the Contributors group on the project portal. For more information, see Team Foundation Server Permissions.

Managing Work Items in Microsoft Excel

You can use Microsoft Excel to store lists of issues, work items, or even to schedule work.

Creating a Work Item List

Team Foundation provides a Microsoft Excel add-in that ties a list object in the spreadsheet to the work item database.

To create a work item list in Microsoft Excel

In Team Explorer, open the AdventureWorks team project, expand the team project node, expand the Work Items node, and then expand the Team Queries node.
Right-click the All Quality of Service Requirements query, and select Open in Microsoft Excel.

Microsoft Excel opens with all the AdventureWorks requirements in a work item list. The list is empty.

Creating a New Work Item in a Work Item List

After you create a work item list in Microsoft Excel, you can create a new requirement.

To create a new software requirement in a work item list

In the first blank row, enter the following column values:
1. In the Work Item Type column, select Quality of Service Requirement.
  
  Note
  
  You may see a dialog box warning you that inserting a row may shift other data rows down. Click OK on this dialog box if it appears.
2. In the Title column, type Shipping Calculations.
3. In the Quality of Service Type column, select Performance.
4. In the State column list, make sure that Active is selected.
5. If you want, from the Assigned To column list, assign the work item to a different person.
6. Leave all other columns with their default values.
Repeat step 1 to create another requirement titled In-store pickup.
On the Team tab, in the Work Items group, click Publish.

A message appears in the status bar stating that the operation was completed successfully. Team Foundation saves the new requirement to the work item database.

Note

In Microsoft Office Excel 2003, use the Team menu.

Saving Your Work Item List to the Team Project Portal

As you create documents in Microsoft Word, Microsoft Excel, or Microsoft Project, you can save them to the project portal. The project portal is a Windows SharePoint Services site. When you save documents to the project portal, you are sharing them with the whole team and also establishing version control for the documents.

To save a document to the team project portal

In Microsoft Excel, click the Microsoft Office Button, and then click Save As.

Note

In Microsoft Office Excel 2003, use the File menu.
In the Save As dialog box, in the File name box, type http://<TFSServerName>/sites/AdventureWorks, where TFSServerName is the name of your Team Foundation Server.
Click Save.

The Save As dialog box will update with the list of document libraries on the site.
Select the Requirements document library, and then click Open.
In the File name box, type Adventure Works Requirements.
Click Save.

Managing Work Items in Microsoft Project

Microsoft Project is typically used to lay out task dependencies, load balance resources, and estimate end dates.

Creating a Microsoft Project File for Task Assignments

Team Foundation provides a Microsoft Project add-in that connects your project plans with your team project. After work is scheduled in a project plan, you can publish the data to the work item database. Team Foundation creates new work items for the task assignments made in Microsoft Project. The tasks appear in the appropriate developer's work item queue.

To create a project file in Microsoft Project from Team Explorer

In Team Explorer, open the AdventureWorks team project, open the Documents node, and then open the Project Management node.
Right-click the Development Project Plan.mpp file, and then click Edit.
In the File Download dialog box, click OK.
To bind the Microsoft Project file to the AdventureWorks team project, on the Team toolbar click Choose Team Project. Then, on the Connect to Team Foundation Server dialog box, click AdventureWorks.

Getting Work Items into Your Project File

You can now get work items from the database to add to your project file.

To retrieve work items into Microsoft Project

In Microsoft Project, on the Team toolbar, click Get Work Items.

The Get Work Items dialog box appears.
In the Get Work Items dialog box, select Saved query, and then in the drop-down list select All Quality of Service Requirements, and then click Find.
In the Work Items list, select the In-store pickup and Shipping Calculations requirements, and then click OK.

Team Foundation inserts the two requirements as Microsoft Project tasks.

Adding New Work Items in Microsoft Project

You can add new work items to the work item database by entering the information in Microsoft Project and then publishing the new work items to Team Foundation.

The default view in Microsoft Project displays many columns. These columns are synchronized with the work item database:

Work Item ID A read-only value that is a unique number to identify the work item in the team project. This value is generated automatically when you create new work items. WorkItem Id corresponds to ID in the work item database.
Title The title of the work item.
Resource Names The current owner of the work item that is the same as Assigned To in the work item database. The resources in Microsoft Project are updated with user accounts from the team project to make it easier to assign work.
Area Path The team project structure node that contains the work item.
Work Item Type The type of the work item, such as a bug, requirement, task, or risk.
Iteration Path When the work item will be addressed or worked on.
Publish and Refresh A value that indicates whether changes you make to the work item in Microsoft Project are published to the work item database.

To add new work items in Microsoft Project

Select the row underneath the Shipping Calculations requirement.
On the Insert menu, click New Task.

Microsoft Project creates an empty task row under the Shipping Calculations requirement.
In the new task, enter the following column values:
1. In the Title column, type Update web basket code.
2. From the Work Item Type column list, click Task.
3. On the Formatting toolbar, click Indent.
Select the row underneath the In-store pickup requirement.
On the Insert menu, click New Task.

Microsoft Project creates an empty task row under the In-store pickup requirement.
In the new task, enter the following column values:
1. In the Title column, type Create new client application.
2. From the Work Item Type column list, click Task.
3. On the Formatting toolbar, click Indent.
You now have two new tasks that are sub-tasks of the Shipping Calculations and In-store pickup requirements.
On the Team toolbar, click Publish.

A message appears in the status bar that indicates that the operation was completed successfully.

Assigning Area Paths to Work Items in Microsoft Project

You can assign component categories from the project structure that you created previously to work items in Microsoft Project. These assignments enable you track which work items belong to the Kiosk team versus the Web team.

To assign component categories in Microsoft Project

In the Shipping Calculations requirement, from the Area Path column list, select Web.
In the Update web basket code task, from the Area Path column list, select Web.
In the In-store pickup requirement, from the Area Path column list, select Kiosk.
In the Create new client application task, from the Area Path column list, select Kiosk.
On the Team toolbar, click Publish.

A message appears in the status bar that indicates that the operation was completed successfully.
From the Project menu, select Group By, and then select Team Project Area. You may have to select More Groups to find the Team Project Area group.

You will now see the requirements and tasks grouped by the project structure for AdventureWorks.

Assigning Iterations to Work Items in Microsoft Project

Next, you can specify when the work will be done by assigning the iterations that you created earlier.

To assign iterations in Microsoft Project

In the Shipping Calculations requirement, from the Iteration Path column list, select M2 Beta.
In the Update web basket code task, from the Iteration Path column list, select M2 Beta.
In the In-store pickup requirement, from the Iteration Path column list, select M1 Prototype.
In the Create new client application task, from the Iteration Path column list, select M1 Prototype.
On the Team toolbar, click Publish.

A message appears in the status bar that indicates that the operation was completed successfully.
From the Project menu, select Group By, and then select Team Project Iteration. You may have to select More Groups to find the Team Project Iteration group.
You now see the requirements and tasks grouped by the project iteration for AdventureWorks.
From the File menu click Save/Update.
In the Save As dialog box, in the File name box, type Requirements Work.
Click Save.

Microsoft Project saves the project file to the project portal and it also appears in the Documents node in Team Explorer. If you do not see your new project file in Team Explorer, refresh the view by right-clicking the team project and clicking Refresh.

Working with Multiple Project Files

You can create multiple Microsoft Project files, which enables you to create multiple views of the same work items. This enables different teams to focus on different parts of the work. You can make some of the documents refresh-only to help reduce the potential for conflicting changes that were made from multiple locations.

To create a read-only Microsoft Project file

In Team Explorer, open the AdventureWorks team project, open the Documents node, and then open the Project Management node.
Double-click the Development Project Plan.mpp file.
In the File Download dialog box, click OK.
To bind the Microsoft Project file to the AdventureWorks team project, on the Team toolbar click Choose Team Project. Then, on the Connect to Team Foundation Server dialog box, click AdventureWorks.
In Microsoft Project, on the Team toolbar, click Get Work Items.

The Get Work Items dialog box appears.
Select Saved query, and then in the list of saved queries, select All Quality of Service Requirements.
Click Find.
In the Work Items list, select the In-store pickup and Create new client application work items.
Click OK.

Team Foundation inserts the two work items as Microsoft Project tasks.

Note

The work item database does not maintain Microsoft Project task relationships that would enable you to construct different views with different dependencies across multiple Microsoft Project files.
Select the Create new client application task, and then click Indent on the Formatting toolbar.
From the Publish and Refresh column list for both work items, click Refresh Only.
Try to change the Title column of Create new client application, to Create new client.

You will get an error message stating that the task is read-only. This feature lets you create multiple views of the same work items and makes sure that changes are only made in one document. Press the Esc key to undo the changes.
From the File menu, click Save.
In the Save As dialog box, in the File name box, type Kiosk Work.
Click Save.

Viewing and Updating the Project Checklist

The project checklist makes it easy for you to be sure you complete the steps required to complete a phase of the project. The checklist is stored in Microsoft Excel. You can assign work items and synchronize the checklist with Team Foundation to track progress on the steps.

Note

The project checklist is available in MSF for Agile Software Development. If your project uses a different methodology, you may not have this checklist. MSF for Agile Software Development is one of the process guidance templates that are included with Team Foundation. For more information, see Finding and Using Process Guidance.

To view and update the Project Checklist

InTeam Explorer on the Documents node, expand the Project Management folder and open the Project Checklist.xls file.
In the Project Checklist, under the Assigned To column, assign each work item by typing or selecting the user name of a person on the team.
Synchronize the Project Checklist in Microsoft Excel with Team Foundation.

For more information about synchronizing Microsoft Excel spreadsheets with Team Foundation, see Working with Work Items in Microsoft Excel and Microsoft Project.