Creating Agile Project Reports with TFS and Reporting Services - Part 2
page 4 of 6
by Eric Landes
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 29019/ 92

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.

View Entire Article

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-05-22 7:43:56 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search