azureazure-devopswiql

Azure DevOps query for work items if any parent is in progress


I'm working on identifying all of our product backlog items that do not have a story point estimation. However, we have so many PBI(product backlog items) right now that need estimating. It is simply a mess. I need a query to help scop down the work. I know how to create an Azure DevOps query such that I return all the product backlog items which are new which do not have a value for effort.

That wiql looks like this

SELECT
    [System.Id],
    [System.WorkItemType],
    [System.Title],
    [System.AssignedTo],
    [System.State],
    [System.Tags],
    [Microsoft.VSTS.Scheduling.Effort]
FROM workitems
WHERE
    [System.TeamProject] = @project
    AND [System.WorkItemType] = 'Product Backlog Item'
    AND [System.State] = 'New'
    AND [Microsoft.VSTS.Scheduling.Effort] = ''

However, I need it to add one more step that filters out items that do not have a parent or grandparent in an active status.

Question :

What is a query that I could use that work gives me only the “PBI”s I don't have an effort in the state new where one or more of their parents(recursive) has a state of in progress ?


Solution

  • You can use the tree work item query type (in my case for user stories):

    SELECT 
        [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State], [System.Tags] 
    FROM 
        WorkItemLinks 
    WHERE 
        ([Source].[System.TeamProject] = '<Your_project>'  AND ( [Source].[System.WorkItemType] = 'Feature'  OR  [Source].[System.WorkItemType] = 'Epic' ) AND  [Source].[System.State] <> 'New')
         And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') 
         And ([Target].[System.WorkItemType] = 'User story'  AND  [Target].[System.State] <> 'New'  AND  [Target].[Microsoft.VSTS.Scheduling.Effort] = '') 
     ORDER BY [System.Id] 
     mode(Recursive,ReturnMatchingChildren)
    

    Query editor:

    enter image description here