In managing projects, I have come to utilize Agile Project Management methods to effectively manage software projects. When I say manage software projects, my definition of managing is to lead. No command and control management style enterprise drone. No, I prefer leading by example, and Agile is a great fit for that style.

These Agile techniques have increased the productivity and quality of the groups I have led tremendously. For the agile projects, I have created the custom reports described in this article and others to help lead the projects.

In a previous article, I described creating these reports in Crystal Reports. There is an introduction to using Agile in that article.  Please refer to that for more understanding of actual techniques used. 

For this article and subsequent articles, we assume the reports will be used with an XP/Scrum style methodology. For a more detailed explanation of this, please see this link to view that for a background. That link goes 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. That article was utilized TFS 2005, while this article is based on TFS 2008

In this two part series we will create 3 reports. In this, the 1st part, we create 2 reports: A "Story Details" report to allow you to print out your stories to post up in your team room and a "Current Iteration" report to give a real time status of your current iteration, and a breakdown report.  You can download these reports here. 

·         Team Foundation Server (TFS) 2008

·         SQL Server 2005

·         Visual Studio 2005

·         SQL Server Reporting Services 2005

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.

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 Stories report, open Visual Studio 2005. Use the same project you used to create the Stories report. Go ahead and right click on the reports node of this project. Select Add new report, which will step you through the report wizard. 

The first screen should display your shared datasource, TfsWorkItemTracking.  We want to use this datasource. When prompted for your query, use the query in Code Listing 2.

Below is a description of the fields used.

·         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)

Listing 3

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]

In stepping through the wizard, select Tabular report. When prompted to select your fields use the fields System.ID, System.Title, Microsoft.VSTS.Common.RoughOrderOfMagnitude, Iteration, System.AssignedTo, and Description. Make sure to put Iteration in the group section. Use the defaults as you step through the rest of the wizard, naming the report Current Iteration. See Illustration 3 for an example of what the report design should look like. This report sample is also in the download for this article.

Figure 3

Before we complete this report, we need to move things around a little. By moving the field names into the group header, then moving all the fields over and removing the page header, we get a report that looks like Iteration 4. 

Figure 4

In these two reports we have seen examples of how you can extend Team Foundation Server using Reporting Services 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!