In part 1 of our series we went over the premise of using Team Foundation Server (TFS) and Crystal Reports to create reports for Agile Projects.  This included the basics of how the agile process can work.  Part 1 included some basics of how the agile project might work. That 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 the 2nd part of the series, we covered 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 to report the status of your current iteration, and a breakdown report. 

In the 3rd part of this series, we will explain how to create a burndown chart utilizing TFS and Crystal Reports.  We will explain the Burndown chart report, then delve into how we create that report, first looking at the SQL to run against the TFS database, then how to create the Crystal Report.

·         Team Foundation Server (TFS)

·         SQL Server 2005

·         Visual Studio 2005

·         Crystal Reports XI

Before we create the burndown, let us give a quick explanation of what a burndown chart is for.  An XP or Scrum project uses some type of report like this updated frequently (in our case 2 - 3 times a week).  Use the burndown chart to show how many points remain in your project.  The chart also shows the (hopefully) downward trend of the points completed for each iteration. 

As we work through each iteration, our team finishes a certain amount of points.  This series of articles assumes that we capture those points for the team in a scenario work item in TFS.  With TFS as the backend for your reporting, keep in mind that you have version information there that can be added to the data shown in this report.

For the data to create the burndown chart, I assume that TFS fields are used in the same manner elaborated on in Parts 1 and 2.  We use the MSF Agile templates and Rank is used to capture iteration information.  The Rough Order of Magnitude field captures the number of points for the story. 

Some of the other fields can be used when you need to do more analysis.  For instance, the date fields are helpful when looking at when more points are added to the total.  Using that data for a report is beyond the scope of this article, but we may explore that later.

To create the burndown chart from the TFS tables, we first need to construct a SQL statement that contains these key elements: the iteration number, a running total of the points completed per iteration, and the actual total of points. 

Since the purpose of the burndown chart is to help in project estimation, we want show the trend of work completed.  We also want to see work remaining in this chart.  In Crystal, we could create a formula to subtract the running total of points completed from total points. In the SQL used for this article we do the subtraction in the SQL statement instead (See Listing 1).

Listing 1

select      [System.TeamProject]
 
        , sum(wil.[Microsoft.VSTS.Common.RoughOrderOfMagnitude]) as 'Iteration Total'
        , wil.[Microsoft.VSTS.Common.Rank] as Iteration 
            , ((select sum(wil2.[Microsoft.VSTS.Common.RoughOrderOfMagnitude])
                        from dbo.WorkItemsLatestUsed wil2
                        where wil2.[System.WorkItemType] = 'Scenario'
                          AND wil2.[System.Reason] <> 'Removed'
                          AND wil2.[System.TeamProject] = wil.[System.TeamProject]) -
              (select sum(wil3.[Microsoft.VSTS.Common.RoughOrderOfMagnitude])
                        from dbo.WorkItemsLatestUsed wil3
                        where wil3.[System.WorkItemType] = 'Scenario'
                          AND wil3.[System.State] = 'Resolved'
                          AND wil3.[System.Reason] = 'Completed'
                          AND wil3.[Microsoft.VSTS.Common.Rank] <= wil.[Microsoft.VSTS.Common.Rank]
                          AND wil3.[System.TeamProject] = wil.[System.TeamProject])) As 'RunningTotal'
            , (select sum(wil2.[Microsoft.VSTS.Common.RoughOrderOfMagnitude])
                        from dbo.WorkItemsLatestUsed wil2
                        where wil2.[System.WorkItemType] = 'Scenario'
                          AND wil2.[System.Reason] <> 'Removed'
                          AND wil2.[System.TeamProject] = wil.[System.TeamProject]) as 'Total'
 
from dbo.WorkItemsLatestUsed wil
where wil.[System.WorkItemType] = 'Scenario'
  AND wil.[System.State] = 'Resolved'
  AND wil.[System.Reason] = 'Completed'
group by wil.[System.TeamProject], wil.[Microsoft.VSTS.Common.Rank]
order by wil.[System.TeamProject]

First add a Crystal Report to a Crystal Report project.  After selecting a cross-tab report (assuming we use the Crystal Wizard), we then create a new connection.  For that connection, using the SQL Native OLE DB connection driver, enter your server information and the database TfsWorkItemTracking.  Once your server is added, select Add Command under your servername.  Paste the query from code example 1 into the command window.

For the columns in the cross-tab, use the Iteration field. For the rows, use field Team Project.  Finally for "summary fields" use the field Running Total.  Then stepping through the wizard, we choose a bar graph.  We select a format from the options available in the styles section. 

Next, under chart options (right click on the chart to see this) choose the Title option to change the Title to BurnDown Chart.  Your chart is now ready to post in your war room!

Figure 1

In this article we went over how to create a quick burndown chart using Crystal Reports and TFS.  This report can be used in XP, Scrum and other agile methodologies.  Our article included a SQL statement that includes the formula for a running total.  This formula can also be added in your Crystal report as a formula.  While this ends this series, I hope to do a few more articles that elaborate on expanded agile project management reports using this same combination.  I hope this was helpful and keep coding better software!