Walkthrough: Managing Work Items in Microsoft Project and Microsoft Excel

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 Foundation Server.

• Sufficient permissions to access and create work items.

• Complete Walkthrough: Creating a New Team Project to create the AdventureWorks team project.

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

1. 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.

2. Right-click the All Quality of Service Requirements query and choose Open in Microsoft Excel.

   Microsoft Excel opens with all the AdventureWorks requirements in a work item list. The list is initially 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

1. In the insert row (the row that contains an asterisk), 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, select a different person to assign the work item to.

   6. Leave all other columns with their default values.

2. Repeat step 1 to create another requirement titled In-store pickup.

3. On the Team menu, click Publish Changes.

   A message appears in the status bar stating that the operation was completed successfully. The new requirement has been saved to the work item database.

   Note

   In Microsoft Office Excel 2007, the Team menu is located on the Add-Ins tab on the ribbon.

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 which is a Windows SharePoint Services site. When you save documents to the project portal, you are sharing them with the whole team in addition to establishing version control for the documents.

To save a document to the team project portal

1. In Microsoft Excel, on the File menu, click Save As.

2. In the Save As dialog box, in the File name box, type https://<TFSServerName>/sites/AdventureWorks, where TFSServerName is the name of your Team Foundation Server.

3. Click Save. The Save As dialog box will update with the list of document libraries on the site.

4. Select the Requirements document library and then click Open.

5. In the File name box, type Adventure Works Requirements.

6. 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. New work items are created for the task assignments made in Microsoft Project, and the tasks appear in the appropriate developer's work item queue.

To create a project file in Microsoft Project from Team Explorer

1. In Team Explorer, open the AdventureWorks team project, open the Documents node, and then open the Project Management node.

2. Double-click the Development Project Plan.mpp file.

3. 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

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

   The Get Work Items dialog box appears.

2. In the Get Work Items dialog box, select Saved query, in the drop-down list select All Quality of Service Requirements, and then click Find.

3. In the Work Items list, select the In-store pickup and Shipping Calculations requirements, and then click OK.

   The two requirements are inserted 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 that 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   Which team project structure node that the work item is part of.

• Work Item Type   The type of the work item, such as bug, requirement, task, or risk.

• Iteration Path   When the work item will be addressed or worked on.

• Publish and Refresh   A value that indicates if 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

1. Select the row underneath the Shipping Calculations requirement.

2. On the Insert menu, click New Task.

   An empty task row is created under the Shipping Calculations requirement.

3. 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.

4. Select the row underneath the In-store pickup requirement.

5. On the Insert menu, click New Task.

   An empty task row is created under the In-store pickup requirement.

6. 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.

7. 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 let you track which work items belong to the Kiosk team versus the Web team, for example.

To assign component categories in Microsoft Project

1. In the Shipping Calculations requirement, from the Area Path column list, select Web.

2. In the Update web basket code task, from the Area Path column list, select Web.

3. In the In-store pickup requirement, from the Area Path column list, select Kiosk.

4. In the Create new client application task, from the Area Path column list, select Kiosk.

5. On the Team toolbar, click Publish.

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

6. 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

1. In the Shipping Calculations requirement, from the Iteration Path column list, select M2 Beta.

2. In the Update web basket code task, from the Iteration Path column list, select M2 Beta.

3. In the In-store pickup requirement, from the Iteration Path column list, select M1 Prototype.

4. In the Create new client application task, from the Iteration Path column list, select M1 Prototype.

5. On the Team toolbar, click Publish.

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

6. 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.

7. You now see the requirements and tasks grouped by the project iteration for AdventureWorks.

8. On the File menu, click Save/Update.

9. In the Save As dialog box, in the File name box, type Requirements Work.

10. Click Save. The project file is saved to the project portal and to the Documents node in Team Explorer. You may have to update the view in Team Explorer before you see your new project file.

Working with Multiple Project Files

You can create multiple Microsoft Project files to create multiple views of the same work items. This allows for 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

1. In Team Explorer, open the AdventureWorks team project, open the Documents node, and then open the Project Management node.

2. Double-click the Development Project Plan.mpp file.

3. To bind the Microsoft Project file to the AdventureWorks team project, on the Team toolbar, click Choose Team Project. Then, in the Connect to Team Foundation Server dialog box, click AdventureWorks.

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

   The Get Work Items dialog box appears.

5. In the Get Work Items dialog box, select Saved query, in the drop-down list, select All Quality of Service Requirements, and then click Find.

6. In the Work Items list, select the In-store pickup and Create new client application work items.

7. Click OK.

   The two work items are inserted as Microsoft Project tasks.

   Note

   The work item database does not maintain Microsoft Project task relationships that let you construct different views with different dependencies across multiple Microsoft Project files.

8. Select the Create new client application task and then click Indent on the Formatting toolbar.

9. On the Publish and Refresh column list for both work items, click Refresh Only.

10. Try to change the Title column of Create new client application, to Create new client.

    You receive an error 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.

11. On the File menu, click Save.

12. In the Save As dialog box, in the File name box, type Kiosk Work.

13. Click Save.

Viewing and Updating the Project Checklist

The project checklist gives you an easy way to make sure that the steps that are required to complete a phase of the project are completed. The checklist is stored in Microsoft Excel, and you can assign work items and synchronize the checklist with Team Foundation to track progress on the steps.

To view and update the Project Checklist

1. From Team Explorer Documents node, expand the Project Management folder and open the Project Checklist.xls file.

2. 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.

3. 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.

See Also

Tasks

Walkthrough: Creating a New Team Project
Walkthrough: Joining a Team Project
Walkthrough: Resolving Conflicting Changes in Microsoft Excel or Microsoft Project