[TFS 2010] How to get progress reports from a hierarchical Work Items structure – Part 2

In my previous post, I went through the theory and steps to define a hierarchy in a work items structure. Now it’s time to have a look at what you need to do in order to get proper progress reports out of it.


This scenario is based on the fact that developers will properly fill up the time spent in their tasks. In other words, the following fields must be up to date:

  • Original Estimate
  • Completed Work
  • Remaining Work

It sometimes mean a lot since it depends on the developers discipline (or PM authority Winking smile ).

Tracking projects and features progress in Excel

Existing reports do not take into account those hierarchies. Sure, you may export all your tasks and have Excel sum up the values, and that will do the trick in a basic way, that is the total for one project. What I want to dig in is how to have this in a less basic way, I just want some drill in, drill out stuff.

Today I’ll expose a recipe for doing this without any further customization in TFS, all will happen in Excel.

Start Excel from a machine where you have also Visual Studio and you get this nice Team menu that will allow you to manipulate Work Items in Excel. We’ll never write enough on how efficient it is.

So, by connecting to TFS through Excel, you get an extract of whatever work item query you want and include all the fields you’ll need. You have to use a tree query because there is no way you can select children and grand children in the same query otherwise. By doing so you get the work items titles in multiple columns (Title1, Title2, Title3, etc.) according to their tree level. Now it is an Excel problem to solve.

Here is an export in Excel, the problem is that title cells or not always filled and you can’t group them straight away.


Note: I only had 4 levels in this example

Shall we start summing up values now? Not really, we want sums for each level. The best way would be to use a PivotTable. But before, we have to perform Excel manipulations in order to be able to group lines related to you features and projects.

Adding columns with grouping values for the PivotTable

Insert a new column for each sublevel, and use the following formulae to fill them, they will copy the parent’s title values in each row:

  • =IF([Title 2]<>””;[Title 2];IF([Title 1]<>””;””;INDEX([Group 2];ROW()-ROW([Group 2]);0)))
  • =IF([Title 3]<>””;[Title 3];IF(OR([Title 1]<>””;[Title 2]<>””);””;INDEX([Group 3];ROW()-ROW([Group 3]);0)))
  • =IF([Title 4]<>””;[Title 4];IF(OR([Title 1]<>””;[Title 2]<>””;[Title 3]<>””);””;INDEX([Group 4];ROW()-ROW([Group 4]);0)))
  • … (more if needed)

About the formulae : I spent quite some time on them, they use structured references in excel tables. What is cool is that it is the same formula for the whole column, what is less cool is that calculated columns are supposed to auto spread there formulae in new rows, but the TFS Excel add-in doesn’t respect this, you’ll have to copy them in new rows yourself (if you have a better method, let me know).

Continue reading

[TFS 2010] How to get progress reports from a hierarchical Work Items structure – Part 1

Work items can represent development tasks, features, requirements, backlog items, projects (and more), and we need to keep them organized in a human friendly, efficient way. TFS 2010 improvements have made it possible to define hierarchical links, and support trees of work items.

On the other hand, work items are part of the developers every day bread and butter, and are a good place to store estimations and time spent on your various tasks.

If you have big enough projects to split the work among multiple teams, you’ll want to make the right choices with TFS and customize it the “right” way to get the most out of it. I mean at least being able to:

  • Define a cohesive and meaningful hierarchy in your work items
  • Leverage the work item ability to track your projects progressthrough the native fields
    • Original Estimate
    • Completed Work
    • Remaining Work

If you want to get an organization that fits well your projects needs, and if you want to get nice progress reports, you’ll need to tweak things a bit.

Default hierarchies

Out of the box, you do have some hierarchy in the work items. In order to check what’s under the cover, you’ll need to install the latest TFS Power Tools and edit the work item templates. It’s not my subject for today since it has been already covered.


The LinksControl control allows the user to create links between work items. When you customize it in the Work Item template, edit the LinksControlOptionsType property to restrict the link types available, and the target Work item types (WITs). The default templates set up the following hierarchies around tasks:

  • MSF for Agile Software Development v5.0 template:
    • User Storycan “contain”
      • User Story
      • Task
    • Taskcan “contain”
      • User Story
      • Task
  • MSF for CMMI Process Improvement v5.0 :
    • Requirementcan “contain”
      • Requirement
      • Task
    • Taskcan “contain”
      • Requirement
      • Task


This will restrict available links options in this Work Item type GUI:


There are two important I’d like to focus.

Using LinksControls

  • You can have multiple instances of the LinksControlcontrol. Each control will only allow the user to create links according to what you have set up in the WIT
    • Notice the Parent and Children control, next to the All links control, the second one has no restriction
    • If you want to be more restrictive, start filtering Work Item links, you may add a “Parent” tab with a LinksControl instance allowing only the Parent link (in the direction child to parent), but don’t forget to remove this type of link from the “All links” tab control…

You can’t totally control the links

Bad news: there are other ways to create links between work items which can easily bypass our restrictions:

  • Using the “Create linked work item button” from a WI
  • Drag & drop in the result pane of a tree query will re-affect parents regardless of the restrictions
  • Programmatically using the TFS client API


Is it a problem? Well, I’d really like a way to enforce those restrictions, but I’ll admit you can of course live without them:

  • Push forward team queries that filter the work item types and the link types, so that mistakenly created work items will be ignored
  • As a TFS admin, create queries that will display odd work item instances (orphan tasks, or tasks below bugs, or anything you don’t want to see in your TFS)

Getting hierarchical your way

Let’s say you have a few Projects that your teams develop during each iteration. Each project is composed of different features, and each feature is composed of a hierarchy of tasks:


Classic approach: create new work item types based on the “Task” work item. Sorry I’m not detailing here, but you’ll find plenty of online resources for this.

Edit their “Parents and children” LinksControl as follows:

  • for Projects (can only have Feature children):


  • for Features (can be related to Projects and Tasks):


  • for Tasks (can be related to Features and Tasks):


You can create a single Team Query for one of those projects and all its hierarchy:


And here you are, you have a nice customized hierarchy (those are from my samples adapted from real data):


It looks ok, now we can get started working.

Using hierarchies is not exactly new in 2012 since they’ve been available from TFS 2010. I just think it is important to have an idea of what you have to do get started properly, with a structure that fits your organization business vocabulary. And, based on that, how you are going to get sorted with reporting? That will the subject of the next couple of posts… Stay tuned!

TFS Integration tools configuration for work items

Hi, today I’m detailing the configuration I used for the migration. At first I thought this step would have been quite complex. I have to say I was pleasantly surprised, the TFS Integration tools are powerful indeed and I ended up with readable configuration file.

Avoiding validation errors

I cannot afford being delayed by invalid work item fields, such as a missing user account that blocks the copy of a work item. I needed to bypass any kind of control on work item fields. Invalid stuff is not the subject of the migration.

I changed the work item types and added the ALLOWEXISTINGVALUE in many fields! Typically in custom fields that use global lists because the list have been modified since then. Also, in many fields bearing user accounts (System.ChangedBy, System.AssignedTo, Microsoft.VSTS.Common.ActivatedBy, Microsoft.VSTS.Common.ResolvedBy, Microsoft.VSTS.Common.ClosedBy).

<FIELD name="Changed By" refname="System.ChangedBy" type="String" syncnamechanges="true" reportable="dimension">

<!—VLA remove this after migration –>


Enable bypass rule data submission

This is a migration option I really needed, when enabled, the work items are not created with regular TFS API, but with an XML construction that is submitted. This actually bypasses a few controls on the work item fields. This also it will avoid too many errors when you actually raw copies without too much control.

Here are interesting details about this option.

Each time I created a new migration in the TFS integration tools I would copy paste this block in the actual configuration.

    <Addins />
    <UserIdentityLookup />
    <DefaultUserIdProperty UserIdPropertyName="DisplayName" />
           <CustomSetting SettingKey="EnableBypassRuleDataSubmission" SettingValue="true" />
  <StoredCredential />

Areas transposition

I previously said I wanted no transformation during the migration, but we had in mind to restructure completely Areas and Iterations. The root node for Areas is the Team Project name, then you start having major nodes such as : Front office, Main portal, Back office, Client, BI, Databases, and the like. In this unification of team projects, we wanted to design a single and cohesive Area structure.


Continue reading