Our first Report is used when you need to post your stories
somewhere in your team room. For anyone running an agile project utilizing
stories or scenarios, 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.
We assume that you are using either the standard TFS process
template MFS Agile or a process template based on MSF Agile to capture
stories. For the standard MSF Agile template you will use scenarios as
the stories. 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, this can be done in Visual Studio
2005. If you are attempting this on a machine that has Visual Studio 2008
installed, make sure 2005 is installed before 2008. First, Create a new
project and select from the Business Intelligence Projects. Select the Report
Server Project Wizard.
This wizard steps you through setting up a report in a
reporting services project. When asked for the connection information, enter a
data source name. For this article I entered TFSWorkItemTracking. Leave the
Type set to Microsoft SQL Server, and click on the Edit button to create your Connection
String. Enter your TFS SQL Server name and your login information. Then select
the database TFSWorkItemTracking.
After entering the connection information, click on the
Shareed datasource button. Your screen should look like figure 1.
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 your code
is protected from breakages in an upgrade. Just make sure to test this when
upgrading in the future. Also, make sure that you have selected the checkbox
for "Make this a shared data source."
After clicking the Next button, in the query builder you
need to add the following query.
Where [System.WorkItemType] = 'Scenario'
Below are descriptions of the 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
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)
Continue stepping through the wizard, selecting Tabular
Report, then Select the following fields in the details section. System.ID,
System.WorkItemType, System.State, Microsoft.VSTS.Common.Rank, Microsoft.VSTS.Common.RoughOrderOfMagnitude,
Grouping on [System.ID]. The table layout is stepped, I
selected Slate as the Style. When prompted for the report name, enter Stories.
Next, we need to format the report. In this case we want to
create an electronic story card that can also be printed. See Figure 2 below to
see the format.
We need to add a Status formula to this report since we
really have 2 states to check, but we only want to display Active, Completed,
or Removed. To do this, right click on the Fields!System_State column. Select
Expression and enter the following formula.
=iif(Fields!System_State.Value = "Resolved",
After saving this report, you can now use this to print out
and display in your team room. Or, you can try to use this electronically. I
prefer to print these out and display in our team room. After the first print
out, your team members will most likely write on these cards, which you will want
to capture in TFS also. This way, you have the physical cards, and it is
captured in the database. For some people, just index cards work, but for
other folks (I am in this camp), it is easier to report on this information if
it is captured in a database like TFS.