How to output only User Stories from tree query in Azure DevOps (WIQL)?

Question

Trying to create a query to show only "Removed" User Stories under an Epic. Doing this in a tree query as I am filtering down from Epic to Feature down to User Story.

Type of tree: Parent/child. Filter Options: Match top-level work items first

I need a count of ONLY the user stories, as linked item shows the count including features which is not what I want.

Tried Query Tiles Pro but that isn't proving useful, it outright shows the wrong number without even doing any filtering.

Below is the WIQL query using WIQL playground.

SELECT
    [System.Id],
    [System.WorkItemType],
    [System.Title],
    [System.AssignedTo],
    [System.State],
    [System.Tags]
FROM workitemLinks
WHERE
    (
            [Source].[System.TeamProject] = @project
            AND [Source].[System.Id] = 65085
    )
    AND (
            [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
    )
    AND (
            [Target].[System.TeamProject] = @project
            AND [Target].[System.WorkItemType] = 'User Story'
            AND [Target].[System.State] = 'Removed'
    )
ORDER BY [System.WorkItemType]
MODE (Recursive)

Hugh Lin Hugh Lin · Accepted Answer · 2019-06-25T10:31:43

I am afraid that it is not feasible to output only user stories.The parent work items of user stories cannot be filtered out.

If you want to output only userstories ,you can try to export the query as excel and then filter it in excel.

How to output only User Stories from tree query in Azure DevOps (WIQL)?

1 Answers