ODBC guide

Query sprint hours by team member (estimated vs completed)

Returns aggregated original estimate, remaining work, and completed work per team member for a sprint (iteration path). Useful for sprint burndown or capacity reporting. The query groups by iteration path and assignee; the WITH clause filters to a specific sprint and state (e.g. Resolved, Closed).

This only gives meaningful results if your team updates Original Estimate and Completed Work on work items as they progress. You can change the iteration path in the query to cover other sprints or the whole product.

SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project='ProductTesting'
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\Sprint 1'' and State IN(''Resolved'', ''Closed'')'
	  
	  --Use below to query all sprints for this Product
	  --,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\'' and State IN(''Resolved'', ''Closed'')'
)