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 ?
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: