Tracking Progress in a Sprint with TFS

One of the most common situations I encounter with clients and attendees at my presentations on using Team Foundation Server (TFS) for agile development is how to generate a burndown chart. In some ways there is already one provided in the Remaining Work report, granted it is not a true burndown as defined by the Scrum method but it does provide one view into the process. Throughout this post I will assume that the MSF for Agile Team template for TFS 2005/2008 is used.

The challenge with the Remaining Work report is that it aggregates at the work item count level rather than at any relative size measure. Let me explain using an example. Let’s say you have a project backlog that consists of 20 Scenarios and you filter the Remaining Work report by Scenario. Unless the Scenarios are of equal effort then the Remaining Work report does not necessarily give you a true sense of velocity. If the first 10 Scenarios are complete and half the duration of the project is consumed it would appear as though everything is on track. However, if the 10 Scenarios that were complete happened to be the 10 smallest ones, perhaps making up only 10% of the total effort, then the warm fuzzy feeling of having half the scenarios complete would be woefully misleading. One thing that you may notice is the Scenario work item type has a field named “Rough Order of Magnitude” which is intended to be used precisely for incorporating the sizing aspect. There are two challenges with this field as implemented. The first challenge is it is limited to 0,1,2, or 3 however these values can be changed by editing the work item type definition. The second challenge is the field is defined as a dimension type which means that it is more difficult to work with for aggregation purposes out of the data warehouse.

Given these challenges I am going to propose a different method that was proven quite successful and is easily done without modifying any work item types.

The first step is to utilize the Iteration field of work items to track the current sprint. Create an Iteration named “Current Sprint”. This iteration will always represent the current sprint in progress. You move work items in and out of this iteration during the sprint review and planning meetings. The work items assigned to the Current Sprint iteration always represent only those items that are currently planned for the sprint in progress. I will discuss managing this process in detail in a later post.

The second step is to use only Task work items to represent the work to be done in the sprint. During sprint planning the work to be done during the sprint should be identified and added to the Current Sprint iteration.

Assign each task a remaining work value. Although the task work item has this filed listed as hours there is no need to enter a value representing hours, if you use story points you can use those instead. You should now have the initial state of the sprint backlog represented as all of the tasks assigned to the Current Sprint iteration. The sum of the remaining work field is the start value of the burndown chart.

As work progresses throughout the sprint the values of the remaining work field should be updated with the new value as work on the task is complete. Providing the values are updated in a timely manner the current sum of the remaining work will represent the total remaining work for the sprint. You now have all the information you need for a burndown chart.

There are two ways of reporting on this information and both require a bit of extra work.

The first method is to run a query for all tasks filtered on Iteration Path for the Current Sprint. Using the TFS integration with Excel it is easy to sum the remaining work column. In fact the easiest way to do this is to save the spreadsheet with the query and summation in SharePoint and refresh the spreadsheet every day and log the sum of the remaining work. This is a very easy way to determine the daily value and plot it on a big chart on your burndown wall chart.

The second way is to create a burndown report in SQL Reporting Services and post it to the team project reporting site. Creating this report is very easy.

Create a new report project in Visual Studio using the wizard project type. Create a database connection named TfsOlapReportDS and connect to the Sql Server Analysis Server for your Team Foundation Server installation. Create a query by dragging the “Cumulative Remaining Work” from the measures (Work Item History) into the “levels and measures” window and the Date field from the dimensions into the “levels and measures” window. Add Iteration, Team Project and Date as parameters by dragging them into the dimensions window. Set the Date parameter to be Range (Inclusive). Be sure to check the parameters checkbox for each of these parameters. Choose the Tabular report type with Date as the group and Cumulative Remaining Work as the detail, this is essentially irrelevant as we will be creating a graphical representation of the data and removing the generated table anyway.

Once the report designer appears delete the table from the report and add Chart from the toolbox. Drag and drop the Date field from the dataset to the category section of the chart and the Cumulative_Remaining_Work field to the data section. Right click the chart and choose Chart Type -> Line -> Simple Line.

The steps described above assume some familiarity with creating custom reports using SQL Server Business Intelligence Development Studio (aka Visual Studio) and do not cover every little thing that needs to be done but should be sufficient if you have any experience creating custom reports.

Done.