TFS Reporting Recipes #1: Get the Iteration Path/GUID for the Current Sprint

Purpose

Many times you want to filter the contents of reports based on sprints or iterations (for example, the number of re-opened bugs in each sprint in the project).  In this case, you often want the default of the report filter to show the iteration path of the current sprint.  This query will retrieve the current sprint’s iteration path.

Prerequisites

• TFS 2010 or higher
• The Scrum process template
• A @ProjectGUID  parameter – The GUID of the Team Project in which you’re interested

Query

SELECT IterationPath
FROM dbo.CurrentWorkItemView
WHERE System_WorkItemType = 'Sprint'
    AND ProjectNodeGUID = @ProjectGUID
    AND Microsoft_VSTS_Scheduling_StartDate <= GetDate()
    AND Microsoft_VSTS_Scheduling_FinishDate >= GetDate()