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 Scenario Stories report, open up Crystal XI.  Next, select "Standard Report Wizard" under the New Report section.  When asked for the connection information, select OLE DB and the "Microsoft OLE DB for SQL Server" connection driver.  Then put in your TFS SQL Server name and enter your login information.

Again, we use the database TfsWorkItemTracking.  Remember that we are pulling data not from the warehouse, but from more of a transactional database.

After you select the database, select the view "WorkItemsLatestUsed" to base the report on.  Use the following fields:

·         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)

With these fields we can now construct our report. This assumes that the Rank field is set to 99 if it was not assigned an actual iteration.

Listing 4

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]

This SQL will be used as a command, rather than selecting tables in the wizard.  We use the fields System.ID, System.Title, Microsoft.VSTS.Common.RoughOrderOfMagnitude, Iteration, System.AssignedTo, and Description.  See Figure 2 for an example of what the report design should look like.  This report sample is also in the download for this article.

Figure 2