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.

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.

Listing 1

select  [System.IterationPath],
           [System.ID],
           [System.WorkItemType],
           [System.State], 
           [System.Reason] ,
           [Microsoft.VSTS.Common.Rank],
           [Microsoft.VSTS.Common.RoughOrderOfMagnitude],
           [System.Title]  
from WorkItemsLatestUsed
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 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)

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, System.Title. 

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. 

Figure 2

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.

Listing 2

=iif(Fields!System_State.Value = "Resolved", 
Fields!System_Reason.Value,Fields!System_State.Value).  

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.