In wiql Is there a way to summarize completed, remaining hours etc... from all child tasks?

Question

I would like to create a wiql query that will return FEATURE and the Sum of completed work for all tasks under all user stories that this feature has!

I have the query below that will return

• FEATURE
• -- User Story
• ---- Tasks

But is there any way to aggregate the CompletedWork for example?

SELECT
      [System.Id],
      [System.WorkItemType],
      [System.Title],
      [System.State],
      Effort, 
      Microsoft.VSTS.Scheduling.StoryPoints,
      Microsoft.VSTS.Scheduling.OriginalEstimate,
      Microsoft.VSTS.Scheduling.RemainingWork,
      Microsoft.VSTS.Scheduling.CompletedWork

FROM workitemLinks

WHERE
      (
              [Source].[System.Id] = 22052
      )
      AND (
              [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
      )
ORDER BY [System.Id]
MODE (Recursive)

Answer 1

Sum of completed work for all tasks under all user stories that this feature has

I am afraid this feature is not feasible in wiql. WIQL syntax is used to define a query as a hyperlink or when using the work item query language(rest api).If the feature you need is not available in query, it also can't be implemented through wiql.

If you want to aggregate the completed work, you can try power bi,agree with Shamrai Aleksander. For details,you can refer to this docs.

You could also submit a suggestion ticket to suggest the new feature through this url. After suggest raised, you can vote and add your comments for this feedback. When there are enough communities vote and add comments for this feedback, the product team member will take this feedback seriously.