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
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
After you select the database, select the view
"WorkItemsLatestUsed" to base the report on. Use the following
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
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
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
, [Microsoft.VSTS.Common.Rank] as Iteration
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'
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.