Tracking Team Projects in Microsoft Excel and Microsoft Project

Article
11/16/2012

You can use Microsoft Excel and Microsoft Project to manage and track your projects. Your project plan is directly connected to the tasks that your team members are working on and lets you:

Publish tasks from Microsoft Excel and Microsoft Project directly into Team Foundation Server.
Get dynamic status updates on the status of tasks in your project plan.

The work items that make up your project are stored by Team Foundation Server with information about their status and interrelationships between other work items and source code files. Team Foundation integration with Microsoft Excel and Microsoft Project lets you manage work items using all the power of those tools without losing the connection to the data in Team Foundation Server. You can use Microsoft Excel to sort, filter and manage requirements and scenarios. You can use Microsoft Project to create and schedule tasks. Or, you can use Team Explorer to keep track of and report progress on all work items.

Tracking Projects in Microsoft Project

As a project manager, you can track projects in Microsoft Project and Team Foundation Server:

Set up a project plan.
Create a set of tasks, schedule them, and publish them to Team Foundation Server.
Team members receive their tasks and report on their progress in Team Explorer.
Track the progress of the project in Microsoft Project.

Setting Up a Project Plan

To set up a project plan connected to Team Foundation, do the following:

Connect the project plan to Team Foundation. For more information, see How to: Connect to Team Foundation Server.
Map work item fields to Microsoft Project. For more information, see How to: Change How Fields are Mapped to Microsoft Project.

You can also set up views in Microsoft Project to provide a visual representation of project data that is useful in tracking your project. For example, you can have a view that helps you track the progress, or a view that shows you how your team resources are being used. Besides the views available already in Microsoft Project, Team Foundation provides two additional views for tracking your team projects. For more information about these views, see Microsoft Project Work Item Views. For information about how to create your own view, see How to: Create a View in Microsoft Project.

Importing an Existing Project Plan into Team Foundation

You can create a new project plan or take an existing project plan and publish it to Team Foundation Server so that your team can view and update their work items.

To import a project plan into Team Foundation

If you are creating a new project plan, set up your tasks, durations, resource assignments, dependencies and other details as you would ordinarily do with Microsoft Project.
Connect the project plan to Team Foundation Server. For more information, see How to: Connect to Team Foundation from Microsoft Excel or Microsoft Project.
In the Work Item Type column, set the work item type for each work item that you want published to Team Foundation Server.
In the Sync column, for summary tasks that you do not want to publish to Team Foundation Server, select Do Not Publish.
If you have any tasks that are assigned to more than one resource, divide them into separate tasks that can be assigned to one resource (Team Foundation Server does not currently support assigning a work item to multiple resources). If you like, you can group the separate tasks into a summary task to receive the automatic calculation benefits.

Note

When you assign resources to tasks, you should specify those resources by their display names from Active Directory Domain Services (AD DS). If you assign resources by alias or other shortened form of their names, you risk introducing inconsistencies with how those resources are specified in Team Foundation Server.
To group sets of tasks, create a set of areas in Team Foundation Server, and then group tasks by setting the Area column. For more information about how to create areas in Team Foundation Server, see How to: Modify the Team Project Areas.
On the Work Item toolbar, select Publish to publish the project plan to Team Foundation Server.

Note

When you publish data to Team Foundation Server for your team members, you are publishing parts of task information. The portions correspond to the data mapping between Microsoft Project and Team Foundation Server. You are not publishing the whole project plan or resource fields to Team Foundation Server.

Project Support Limitations

When you are working with Microsoft Project and Team Foundation Server, remember that not all the information in your project plan is maintained in the work items. Therefore, you should keep your project plan in order to preserve the information that Team Foundation Server does not store. The following information is not shared or has limited support:

Dependencies Team Foundation Server does not store the predecessor or successor information for work items published from Microsoft Project. You can create links between work items; however, they are simple links. For example, a work item link cannot be used to make sure one work item is completed before another.
Microsoft Project Notes. Microsoft Project Notes are not published to Team Foundation Server. To send notes to team members outside Team Foundation Server, you can use Microsoft Excel.

Master Projects

If you create a master project in Microsoft Project, make sure that all sub-projects are connected to the same team project and have unique work items. If the sub-projects are connected to different team projects or have shared work items, you see errors.

Also, when you edit work items in a master project, you must edit them in the sub-project. When you work in the master project, you should only work with work items in a virtual read-only manner.

Tracking progress of a Team Project in Microsoft Project

This section covers:

Updating your project plan with new work items.
Setting up high-level tasks.
Tracking the progress of work items.

Updating Your Project Plan with New Work Items

As soon as you have published work items from you project plan into Team Foundation, team members can create new work items. You can set up a query in Team Foundation that returns the new work items and then when you open your project plan, you can get the new work items using the query you created.

Another way to track new work items is to ask your Team Foundation Server administrator to:

Set up a rule so new work items are automatically assigned to you.
Set up e-mail notifications.

You then receive an e-mail for new work items and can decide whether to update your project plan by pulling in the new work items to make the appropriate resource assignment. For more information about refreshing work items, see How to: Publish or Refresh Work Items in Microsoft Excel or Microsoft Project.

Setting Up High-Level Tasks

You can create a high-level task in Microsoft Project to let you track detailed tasks without making your project plan unmanageable.

To set up a high-level task to track more detailed work items

Set up a high-level task (not a summary task) in Microsoft Project and publish that to Team Foundation Server for your team.
Ask your team members to:
- Use Microsoft Excel to keep track of all the detailed tasks that are part of the high-level task.
- Set up a simple formula in Microsoft Excel that adds up all the remaining work hours and completed work hours into your high level task.

Tracking the Progress of Work Items

Your team members use Team Foundation or Microsoft Excel to:

View their work items.
Record the status of those work items.
Record the time spent on those work items.

To see the progress made on the work items in your project plan, you refresh your project plan. Because your project plan is directly connected to the work item database, you spend less time manually entering status data into your project plan. For more information, see How to: Publish or Refresh Work Items in Microsoft Excel or Microsoft Project.

If you want to keep track of changes that you made to your plan as the project progresses, you can capture a baseline before you refresh and then compare with the post refresh baseline to see the changes. For more information, search for "Save a Baseline Plan" in Microsoft Project Help.

To keep track of what work items are behind schedule or ahead of schedule, display progress lines before you refresh your project plan and see the progress made by your team. For information about displaying progress lines, search for "Display Progress Lines" in Microsoft Project Help.

Share via