Our first Report is used when you need to post your stories
somewhere in your war room. For anyone running an agile project utilizing
stories, you want to post those stories in a visible manner. This communicates
in an "in your face manner" the status of the project, as soon as a
team member enters that room. For that reason, being able to print stories out
from your TFS database is important.
As mentioned in the first part of this series, it is assumed
that you are using the standard TFS process template MFS Agile, to capture
stories as scenarios. If you customize your own template to follow your
process more closely, the concepts discussed here should help in creating the
report. But the actual field names may be different.
Fields used for Scenario Stories report
To create your report, first 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.
For the database, select TfsWorkItemTracking. Keep in mind
that we are pulling data not from the warehouse, but from more of a
transactional database. Microsoft may change this in upgrades, so beware. We
are basing this off a view, so hopefully you are a little protected, just make
sure to test this when upgrading in the future.
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)
While stepping through the wizard, do not group anything,
but on filter select System.WorkItemType. Make this "Is equal to"
and enter "Scenario" in the textbox for the filter expression.
After selecting those fields, then you need to add 3
formulas. The first one is for the Score and is called ScoreText.
Listing 1
"Score = " + ToText({WorkItemsLatestUsed.Microsoft.VSTS.Common.RoughOrderOfMagnitude})
Then we add the formula to show the iteration. Below we
have the formula used for displaying the iteration.
Listing 2
"Iteration " + {WorkItemsLatestUsed.Microsoft.VSTS.Common.Rank}
Finally, we create a formula Status. This formula checks to
see what the state is. If the state is Resolved, then this formula will show
the Reason field. The reason field will have completed, removed or other
options related to the resolved option.
Listing 3
IIF ({WorkItemsLatestUsed.System.State}=
"Resolved",{WorkItemsLatestUsed.System.Reason},{WorkItemsLatestUsed.System.State} )
The wizard leaves you in a layout mode where you need to
move some fields around. For this layout we keep everything in the detail area
and put in the ID the Iteration formula, Status formula, and ScoreText formula
in the detail. The Title is put there as well. You can add the assigned to
here also.
See Figure 1 for the look of the report and download an
example of this layout here.
Figure 1