I had a situation where I had to go directly into the database and determine the work items that fell into a certain category and where they showed up on my long range plan (which iteration). It turns out that the iteration path isn’t easily accessible directly in the database. There are a number of places you can look, but none are particularly satisfactory.
The various tables are TreeIDPairs, TreeNodes, xxTree and the views are TreePairs, TreePathParts, TreesAreUsed and TreesWereUsed. All of these views and tables have problems with them in one way or another. For example, the TreePairs view has columns called Name (all values are a ‘’) and AboveID (all values are ‘0’) – not very useful. The TreesAreUsed contains the full Area Path but no Iteration Path. The xxTree has both but I preferred to not rely on that table (anything with an ‘xx’ to start the table name tells me it either is, or may be, deprecated). So I wrote my own query which I thought might be helpful to others.
WITH FullIterationPath (ID, ParentID, Name, tlevel, hierarchy) AS ( --Anchor definition SELECT a.ID, a.ParentID, a.Name, 0 as tlevel, a.Name as hierarchy FROM TreeNodes a WHERE ID = 439 --FEC Solution-Products Root UNION ALL --Recursive definition SELECT a.ID, a.ParentID, a.Name, tlevel + 1 as tlevel, cast(hierarchy + '' + a.Name as nvarchar(255)) as hierarchy FROM TreeNodes a JOIN FullIterationPath b ON b.ID = a.ParentID ) SELECT ID, ParentID, Name, tlevel, hierarchy FROM FullIterationPath
You can also use this to construct the area path as well.