LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Creating Agile Project Reports with TFS and Reporting Services - Part 2
by Eric Landes
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 31659/ 60


This series introduces the reader to creating custom reports for TFS utilizing Reporting Services.  In Part 1 of our 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. 

This last part of the series, we will explain how to create a burndown chart utilizing TFS and Reporting Services. 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 report in Reporting Services.

System Requirements

·         Team Foundation Server (TFS)

·         SQL Server 2005

·         Visual Studio 2005

·         Reporting Services 2005

The Burndown Chart explained

Before we create the burndown, let us give a quick explanation of what a burndown chart is for.  The burndown helps your team see the status of the project. An XP or Scrum project uses some type of report like this updated frequently (in our case 2 - 3 times a week).  Post this report in the team room to make it highly visible to the team.

Use the burndown chart to show how many points you started with in your project, and over time, how many remain in your project. The chart 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 Part 1. We use the MSF Agile templates when creating the workspace for this project. The field 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.

SQL Statements

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 Reporting Services, 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] <= 
                          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]

Our SQL sums the Microsoft.VSTS.Common.RoughOrderOfMagnitude field (which we indicate how many points the story is worth in) into a field called Iteration Total. Remember that we use the field Microsoft.VSTS.Common.Rank as the Iteration field. Since we group the query by Microsoft.VSTS.Common.Rank (which indicates our iteration), that makes the query sum our points for each iteration.

Our subquery is complex. We use a sum of all completed points subtracted from the total points of the project to get the running total of the project. As mentioned earlier, we are using the System.WorkItemType of Scenario to signify a story. We also want to make sure that it does not equal Removed in order for this to count.

Reporting Services

To create our Report, let us first open a Reporting Services project. On the reports folder, click on Add then click on New Item. In the dialog box in the Report Projects section, select Report (not report wizard) and name the report burndown.rdl. The data section of the report should display.  On that section go to the Dataset drop down, and select the <New Dataset…> option. In that dialog box name the dataset burndown, and make sure the data source shows the TFSWorkItemTracking shared data source. The command type should be set to text, then paste the SQL shown above in the query string.

After closing the dataset dialog box, you can now run the query to make sure data comes back.  Once you see data in that window, click on the layout tab to start creating the chart. From the toolbox, drag the chart onto your design area. Right click, and go to properties. We want to create a chart that shows the total points for each iteration. So we go to the data tab and select burndown from the Data source field. Next, we click on the Add button and on the Values tab at the Values drop down select =Sum(Fields!RunningTotal.Value).  In the Series Label add the title "Point Label."

Now in the categories group, click on the add button there. In the Group on section, select from the drop down =Fields!Iteration.Value. Also, under the label field, select the same value to print out the Iteration numbers for each point total.

Let us also add a matrix to this report to show actual totals. From the tool box drag the Matrix object beneath your chart. In that Matrix, for the Row, put the System_TeamProject and for the columns, put the Iteration. For the data, put the running total in. Finally, format the matrix with borders and bold headers as shown in Figure 1. 

Figure 1

Now your report is ready to print and put on the wall in your team room. Deploy that report to your TFS Server, and you can set up a subscription to email to you every 2 days.


In this article we went over how to create a quick burndown chart using Reporting Services 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. We also walked through setting this report up in Reporting Services. 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!

©Copyright 1998-2021  |  Page Processed at 2021-11-29 6:13:54 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search