TFS WITs – Running Hierarchical queries of WITs to pull linked Parent/Child items and flattening the list for sorts.

Sometimes or really quite often it is necessary to run a Team Foundation Server(TFS) Work Item (WIT) query that will pull all the linked child work items(task or bug or test case) for a category of WIT say like a User Story or Requirement.

You can create a hierarchical query with the results you’re looking for -  this will display as a tree, not as a flat list.  One can then use Excel to convert it to a flat list. The steps are as described below:

Go to the Team Explorer Node and pick the type of Query you need (such as All Work Items) and double click on it.

You would end up with something as below. Note that the results you see will vary based on your Team Project and WITs. Click on “Edit Query”.

And Choose “Tree of Work Items”

Then go on to modify your query filter criteria as you choose befits your requirement. In the case below, I have added a filter to filter by WIT Type=”User Story” and link work items that match type of “Any” and as well as set the tree type to “Parent/Child”.

At this point you could either “Save Query” on “Run” it. If you run it, you could see something like the following results at the bottom results pane. In the below you can see the results stating that there are 60 items found, out of which 2 are linked. The linked ones are shown intended in a parent->Child relationship order. These are the two bug WITs of ID 239738 and 27937 respectively.

Now you can “Save Query” for using it later if needed.

You could also now export this result to Excel and do column level sorting on the “Assigned To” field if you needed a flat list from then on. Click on Open in Microsoft Office and choose the “Excel” option.

Once you have the results in Excel, you can sort by whatever field you want, by clicking on the drop downs to the right of each field column header.

Or, you can do more interesting things like, create a new query with the work item IDs of the first query results.

Note that you need to think through, before you want to have entire hierarchies displayed as a flat list. I personally think it does not  make sense to make them flat – the reason is, it becomes difficult for the data to be read at that level of flattening to make any sense. Often times for Project Review meetings etc.,. the team lead manager just wants one such report sorted by “Assigned To” field and then it makes perfect sense though.