Visual Studio Online Backlogs – Export Work Items in Correct Order

If you are using TFS on premise or VS Online work organization options, then most probably you are a software project manager or a software owner with structured mind or a manically organized developer.

In any of these cases you have defined, and then re-defined to improve, a certain work items hierarchy. Either Scrum, or Agile or CMMI, all offer different templates with their own naming convention. On the Example of CMMI you can move features (and its child objects) to a new position, to reorganize priority level. This is very nicely displayed on the Backlogs view where each feature has its order number.

As soon as you have to export the work items sets – say, to create a report in a Word document – you will see that this is not trivial. For Word directly there are some obsolete third-party add-ins that I didn’t bother testing. From the TFS server site there is no export to some sort of text format (I’d prefer here the simplest tab indent text files). Thus if adhering to “native” tools, you can only export work items to Excel.

First make sure that you have the Excel Team Foundation Add-in installed and it is activated in Excel preferences – my first problem occurred there, since I had deactivated all. By the way, to install this add-in you really need Team Explorer or Visual Studio. But you’ll need them not only because of the add-in – see the fifth obstacle at the end of this article.

Second distraction, there is no possibility to export from the backlogs view. I’ll have to switch to Queries View.

Third distraction, there are tons of useless queries such as My Favorites (never used this on my PC as well), but not a default for all items. This is easily done: create new query, select the Tree of work items type, and select Work Item Type = [any] in the current project. Give it an adequate name and put it in the Shared Queries.

Fourth problem: if you run the query now you can see in the results that the features order does not match your Backlogs View order. The problem is, features here are ordered by (feature) ID. There is also no “Order ID” field you can sort by. Thanks to an MSDN blog you can find here, I learned how the so called backlog priority is working behind the scenes. In CMMI the magic is hidden in the Stack Rank field value.

You can add this field to the query but then again it is not very helpful. And we are still missing some “sort by” option in the query if you scrutinize the screenshot above. Finally just by incidence I found it. Essentially do not select the Column options from the Results panel, but the one above – from the Query Editor panel. There is a second tab called Sort columns and initially the value there is Id [Asc]. Since we found the field name though, we can exchange this to Stack Rank. You can see the result in the next screenshot:

Finally the fifth obstacle: you cannot export directly from the web browser. There is an option to send query as an email, whatever this might mean. You have to fire up your VS or Team Viewer and connect to the TFS or VS online portal. After that you can navigate to, and open the query. Once done you see the button Open in Microsoft Office. From there select Excel:

Hopefully Microsoft improves something soon, so that in the near future, a common software project manager wouldn’t need to fight this battle here twice.