58.3. Excel and Project Integration
Looking at, adding, or editing work items can get a bit complicated and won't scale well when you have hundreds of tasks. This can be problematic especially for project managers who are not used to working inside Visual Studio. They usually prefer to work from Excel or Project. This integration is really easy using the provided add-ins.
58.3.1. Excel
From the ribbon simply click New List and choose a Team Project and Work Item Query. This will retrieve all the information from a web service and display it in Excel. Once it's there you can sort, filter, edit, and publish changes back to the server, refresh the changes made by others, add links or attachments, and choose columns to be displayed.
Another way of doing this is from Team Explorer. From a work item query's context menu, select Open in Microsoft Excel. This will create a new Excel worksheet with the information. Figure 58-8 shows both options and Figure 58-9 shows the results in Excel.
Figure 58.8. Figure 58-8
Figure 58.9. Figure 58-9
58.3.2. Project
There is also an add-in for Project. Similarly to Excel, you can connect to a server, choose a team project, and select a work item query, but instead of using the entire list, you have to choose each of the work items you want to import to your project, as shown in Figure 58-10.
Figure 58.10. Figure 58-10
Once your work items are imported you can edit each of their fields directly in Project. This is possible thanks to the Column Mappings between TFS fields and MS Project Columns. For example, Resource Names in Project will map to the Assigned to field in TFS. Fields that exist only in Team System will be mapped to Text Fields in Project; for example, Work Item Type is mapped to Text 24. This is preconfigured in the process template.
You can add new work items, nest them in iterations or areas, assign them to people, choose a work item type, balance workloads between resources, see project reports, and refresh the progress from the server after each developer changes the work remaining or work item state.
There are important differences that make the integration a bit tricky:
This TFS 2008 doesn't have a notion of hierarchical work items; this will be improved for the next version. Work items can be related, but it isn't a parent/child relationship; some process templates overcome this by relating different work item types; for example, a Product Backlog Item in Scrum associated to a Sprint Backlog Item would be a parent/child relationship and you can represent that in Project as well. The problem with this approach is that you can't have the N-level relationships commonly used for work breakdown structures (WBS). You could have that in Project but the relationship won't be persisted in TFS.
Iterations and areas are represented as fields, while in Project you tend to nest activities under iterations or areas. This is a similar problem and is more a restriction of the MSF process templates than of TFS.
In Project you can have many resources for the same activity, but TFS only accepts one entry. One approach could be to add a custom field to the work item type, but then the mapping won't work because you would need to have one column in Project mapped to two fields in TFS. The workaround is to add the activity once for each resource.
There's no concept of predecessor and successor in TFS, a concept commonly used in pert charts for project managers. Again, you could use this concept in Project, but it won't be persisted in TFS. Or you could modify your work item types to include this field and add the mappings for Project.
There's no Project server/TFS integration out of the box. There are a couple of third-party tools that do this for you. The next version of Team System will include it, as well as project management improvement across multiple projects for load balancing of resources.