Austin Agile DevOps

Team Foundation Server 2010 Real-Time Burndown Charts

The reality is that project managers need work status in real-time. The TFS Excel reports and web parts are great, but because they are sourced from the TFS SSAS cube the information can be up to 2 hours old.

The User Story goes like this...

"As a project manager, team lead or team member I want to see what work is currently active and what the burndown is by team and by person is for that work so I know when work delays or time reporting failures are taking place. I need to know this 30 seconds before the stand-up meeting every day and the information must be accurate in real time."

Here's how I created a burndown chart web part that's:

• Real time
• Is from a specific/fixed start to end date
• Can be by team or by individual
• Skips weekend days

First I developed T-SQL to give me:

1. A query listing of sprints that have active work
2. A query listing of team members that have active work
3. A stored procedure that list burndown chart data as of start and end dates

Then I used the Amrein SQL Query Viewer Web Part (enterprise license for $200) to run the listing queries and display them on a TFS project portal page. As part of the query I assimilated the URL that calls the SharePoint 2010 Query String (URL) Filter web part, which I connected to the Amrein Google Chart Web Part (free). This used the chart data query to display the burndown chart from a common web part page. I also used the same query list for the chart data using the Query Viewer Web Part.
Here's the reports...

Web part page listing the Active Work. Each Team Project needs it's own page and the queries vary by area name.

A burndown chart is displayed in a new browser window by clicking on the "burndown" link.

Here's the code. I developed the T-SQL from SQL Server Management Studio.

Sprint Active Work Report

 
SELECT  task.IterationName as Sprint 
  ,CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101) as Start 
  ,CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101) as [End] 
  ,SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) as Remaining 
  ,SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) as Completed 
  ,SUM(task.Microsoft_VSTS_Scheduling_OriginalEstimate) as Estimate 
  ,'http://[ your TFS SP server ]/sites/tfs/XXXX/SiteAssets/BurndownReport.aspx?start=' 
   + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101))  
   + '&end=' + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101))  
   + '&iName=' + task.IterationName + '&aName=XXXUI-v1&assigned=' as Burndown 
  FROM Tfs_01Warehouse.dbo.CurrentWorkItemView  task, Tfs_01Warehouse.dbo.CurrentWorkItemView sprint 
  WHERE task.ProjectPath LIKE '\XXXX%'  
  AND task.System_WorkItemType = 'Task'  
  AND task.AreaName = 'XXXUI-v1'  
  AND task.System_AssignedTo > ' '  
  AND task.System_State <> 'Closed' 
  AND sprint.System_WorkItemType = 'Sprint' 
  AND sprint.IterationPath = task.IterationPath 
  GROUP BY task.IterationName, sprint.Microsoft_VSTS_Scheduling_StartDate, 

sprint.Microsoft_VSTS_Scheduling_FinishDate 
  HAVING (SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) > 0 
    OR SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) >0) 
  ORDER BY task.IterationName DESC 

Team Member Active Work Report

SELECT  task.System_AssignedTo as Assigned 
  ,SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) as Remaining 
  ,SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) as Completed 
  ,SUM(task.Microsoft_VSTS_Scheduling_OriginalEstimate) as Estimate 
  ,task.IterationName as Sprint 
  ,'http://[ your TFS SP server ]/sites/tfs/XXXX/SiteAssets/BurndownReport.aspx?start=' 
   + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101))  
   + '&end=' + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101))  
   + '&iName=' + task.IterationName + '&aName=XXXUI-v1&assigned=' + task.System_AssignedTo as Burndown 
  FROM Tfs_01Warehouse.dbo.CurrentWorkItemView  task, Tfs_01Warehouse.dbo.CurrentWorkItemView sprint 
  WHERE task.ProjectPath LIKE '\XXXX%'  
  AND task.System_WorkItemType = 'Task'  
  AND task.AreaName = 'XXXUI-v1'  
  AND task.System_AssignedTo > ' ' 
  AND task.System_State <> 'Closed' 
  AND sprint.System_WorkItemType = 'Sprint' 
  AND sprint.IterationPath = task.IterationPath 
  GROUP BY task.System_AssignedTo, task.IterationName, 

sprint.Microsoft_VSTS_Scheduling_StartDate, sprint.Microsoft_VSTS_Scheduling_FinishDate 
  HAVING (SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) > 0 
    OR SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) >0) 
  ORDER BY task.IterationName DESC, task.System_AssignedTo 

Burndown Stored Procedure

SP Call to SharePoint 2010 Query String (URL) Filter web part
-----------------------------------------------------------------------
dbo.WorkItemHistoryView_Burndown;start={start};end={end};iName={iName};aName={aName};assigned={assigned}
dbo.WorkItemHistoryView_BurndownData;start={start};end={end};iName={iName};aName={aName};assigned={assigned}
  
SP Code
----------
USE [Tfs_01Warehouse]
GO
/****** Object:  StoredProcedure [dbo].[WorkItemHistoryView_Burndown]    

Script Date: 09/28/2011 09:49:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 

OBJECT_ID(N'[dbo].[WorkItemHistoryView_Burndown]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WorkItemHistoryView_Burndown]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Bob Hardister
-- Create date: 9/27/2011
-- Description: Produces a burndown table
-- =============================================
CREATE PROCEDURE dbo.WorkItemHistoryView_Burndown 
 @i   int    = 0, 
 @Start  date   = '9/16/2011',
 @End  date   = '10/6/2011',
 @dayRange int    = 0,
 @iName  nvarchar(256) = 'Sprint 03',
 @aName  nvarchar(256) = 'XXXUI-v1',
 @assigned nvarchar(256) = '',
 @currentDay date   = '01/01/0001'
AS
BEGIN
 DECLARE @Burndown TABLE ([Date] date, Remaining float, Completed float)
 SET @dayRange = DATEDIFF(DAY, @Start, @End)
    WHILE (@i <= @dayRange) 
 BEGIN
  SET NOCOUNT ON
  IF (DATENAME(WEEKDAY, @Start) <> 'Saturday' AND DATENAME(WEEKDAY, @Start) <> 'Sunday')
   BEGIN
    INSERT INTO @Burndown ([Date], Remaining, Completed) 
    SELECT @Start as Dte
     ,SUM(Microsoft_VSTS_Scheduling_RemainingWork) as Rem 
     ,SUM(Microsoft_VSTS_Scheduling_CompletedWork) as Act 
     FROM Tfs_01Warehouse.dbo.WorkItemHistoryView 
     WHERE ProjectPath LIKE '\XXXX%' 
     AND System_WorkItemType = 'Task' 
     AND (DateSK <= @Start AND DateSK >= @currentDay)
     AND IterationName = @iName 
     AND AreaName = @aName 
     AND System_AssignedTo LIKE @assigned + '%' 
   END 
  SET @Start = DATEADD(day, 1, @Start) 
  SET @i = @i + 1 
  IF @Start >= GETDATE() SET @currentDay = @End 
 END 
 
SELECT * FROM @Burndown 
END
GO
GRANT Execute ON dbo.WorkItemHistoryView_Burndown TO TfsReports 

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/cvp

Print | posted on Friday, September 30, 2011 2:36 PM | Filed Under [ Agile SCM Talk Blog ]