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] <=
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]
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.
