The next report communicates the status of the current iteration. In your planning meetings after you have updated your stories, this report automatically reflects your status. It should show who is assigned a story and whether the story is completed or still active. If you would use a "Theme" or "Epic," then this is a good place to show it as well. 

Remember to allow your remote stakeholders to have direct access to this report. This shows a transparent process to the stakeholders. Keep this updated throughout the iteration for better communication to those remote users. They will hear this information in the daily stand-ups and then can refer to this report if they forget where you are in the iteration.

Fields Used for Current Iteration Report

As in the Stories report, open Visual Studio 2005. Use the same project you used to create the Stories report. Go ahead and right click on the reports node of this project. Select Add new report, which will step you through the report wizard. 

The first screen should display your shared datasource, TfsWorkItemTracking.  We want to use this datasource. When prompted for your query, use the query in Code Listing 2.

Below is a description of the fields used.

·         System.IterationPath (to get the project name)

·         System.ID (Unique number for the Story)

·         System.WorkItemType (to filter it by Scenario)

·         System.State (to show whether this is an active story or completed).  

·         System.Reason (if state is set to Resolved, Reason displays if it is completed or removed)

·         Microsoft.VSTS.Common.Rank (this is where the Iteration number is stored)

·         Microsoft.VSTS.Common.RoughOrderOfMagnitude (this contains the score for this  

          particular story)

·         A subquery to get the Description text

·         System.AssignedTo (the team member the task is assigned to)

Listing 3

Select      [System.Id]
            , [System.Title]
            , [System.CreatedDate]
            , [System.TeamProject]
            , [Microsoft.VSTS.Common.RoughOrderOfMagnitude]
            , [Microsoft.VSTS.Common.Rank] as Iteration 
            , [System.State]
            , [Microsoft.VSTS.Common.ClosedDate]
            , [System.Reason]
            ,(  select substring(words,1,2000) 
                from dbo.WorkItemLongTexts wilt
                where FldID = 52
                    and ID = wilu.[System.Id]
                    and wilt.rev = 
                        (   select max(wilt2.rev)
                            from dbo.WorkItemLongTexts wilt2
                            where FldID = 52
                            and ID = wilu.[System.Id])
            ) as 'Description'
                               , [System.AssignedTo]
from dbo.WorkItemsLatestUsed wilu
where [System.WorkItemType] = 'Scenario'
--  AND [System.State] = 'Active'
  AND [Microsoft.VSTS.Common.Rank] = 
    (   select max(wilu2.[Microsoft.VSTS.Common.Rank])
        from dbo.WorkItemsLatestUsed wilu2
        where wilu2.[Microsoft.VSTS.Common.Rank] < 99
        and [System.WorkItemType] = 'Scenario'
        and [System.State] = 'Active')
order by [Microsoft.VSTS.Common.Rank] , [System.Id]

In stepping through the wizard, select Tabular report. When prompted to select your fields use the fields System.ID, System.Title, Microsoft.VSTS.Common.RoughOrderOfMagnitude, Iteration, System.AssignedTo, and Description. Make sure to put Iteration in the group section. Use the defaults as you step through the rest of the wizard, naming the report Current Iteration. See Illustration 3 for an example of what the report design should look like. This report sample is also in the download for this article.

Figure 3

Before we complete this report, we need to move things around a little. By moving the field names into the group header, then moving all the fields over and removing the page header, we get a report that looks like Iteration 4. 

Figure 4