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
