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

Question

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 ?

Answer 1

First of all, there’s no such a relationship called “Grandparents” for work items.

For a specific work item like PBI p, it can only have a “Parent” like a Feature F1, as for the Feature F1 if it has a parent like another Feature F2 which you can call it the “Grandparent” of PBI p logically, but this Feature F2 has nothing to do with the PBI p actually. Please check Work link types for a better understanding.

In addition, if you want to query relationships such as “Parent” in Azure DevOps, you can change the “Type of query” to “Work items and direct links”. You can check Use direct links to view dependencies for more information.

Please refer to below wiql and you will see how to query PBIs whose effort is null and parent has a state of “In Progress”:

SELECT
[System.Id],
[System.WorkItemType],
[System.Title],
[System.AssignedTo],
[System.State],
[System.Tags]
FROM workitemLinks
WHERE
    (
        [Source].[System.TeamProject] = @project
        AND [Source].[System.WorkItemType] = 'Product Backlog Item'
        AND [Source].[System.State] = 'New'
        AND [Source].[Microsoft.VSTS.Scheduling.Effort] = ''
    )
    AND (
        [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Reverse'
    )
    AND (
        [Target].[System.TeamProject] = @project
        AND [Target].[System.WorkItemType] <> ''
        AND [Target].[System.State] = 'In Progress'
    )
ORDER BY [System.Id]
MODE (MustContain)

Below is the corresponding Query Editor:

Answer 2

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: