One of the meetings I commonly lead is a demand management meeting. In this meeting we look at the work currently in the pipeline for delivery and make decisions about what needs to happen with it. When working with TFS I run this meeting via an excel worksheet which I load a backlog query into.
The default view for this backlog is a table, which is great for the basics of view/edit but suffers the problem of not easily being able to see the forest for the trees. What I really want is a summary view that enables me to roll up the work dynamically to answer questions raised in the meeting.
To achieve this simply:
- Select a cell somewhere within your backlog query
- Select the design tab of your ‘Table Tools’ ribbon category
- Click ‘Summarize with PivotTable
The options should already have your work item table selected. Either have the table dropped into a new sheet, or your current and click OK.
Once the table is up I generally roll up size as a sum, and Work Item Id as a count by a field such as Area to get the overview I need. Additionally allowing filtering by blocked items is handy.
If you need to drill down to the work items, you can simply use the pivot table tools ‘Expand Field’ function to either have the individual work item id or title values listed as a sub field.