Quick Tips: TFS Demand Management via Pivot Table


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.

How To

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. 

Next Steps

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. 




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s