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:
- A query listing of sprints that have active work
- A query listing of team members that have active work
- 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
syntax highlighted by
Code2HTML, v. 0.9.1