In part 1 of our series we went over the basics of how the agile process can work.  This included some basics of how the agile project might work. Also the article went over how to utilize standard Team Foundation Server process templates with your agile process, to help produce reports to help manage your projects.

In this 3 part series, we will look to create 3 reports. In this, the 2nd part, we will go over 2 reports:  A Story details report to allow you to print out your stories to post up in your war room and a current iteration report that reports the status of your current iteration, and a breakdown report.  You can download these reports here. 

·         Team Foundation Server (TFS)

·         SQL Server 2005

·         Visual Studio 2005

·         Crystal Reports XI

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

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

In these 2 reports we have seen examples of how you can combine using Team Foundation Server and Crystal Reports to produce management reports for an agile project.  In this part of the series we put together a report to show stories and one to show the current iteration.  Our final part of the series will show how to create the final report needed in an agile project.  Until then, happy coding!