Understanding how the Team WebAccess burndown chart works, Part 2

It’s time to pick up where we’ve left off with the first blog post about how the burndown chart works. In that post we’ve seen how we can construct a query expression equivalent to the one Team WebAccess uses when retrieving data needed to render the burndown chart for a specified team and a specified iteration. In this blog post, we’ll examine how this query expression can be used to retrieve results as they were in a specified point in time. Before we do that though, we’ll need a way to figure out when our iteration starts and ends.

Retrieving the iteration start and finish dates

I have already written about a way to get and set iteration dates in a blog post titled Managing the team iteration schedule. Please be sure to check it out if you’d like to know the exact details on how the iteration dates can be retrieved. In that blog post, I’ve implemented an extension method for the ICommonStructureService4 interface, called GetIterationDates, and which we’re going to use today:

static bool GetIterationDates(TfsTeamProjectCollection tpc, string projectUri, 
    string iterationPath, out DateTime startDate, out DateTime finishDate)
    ICommonStructureService4 css = tpc.GetService<ICommonStructureService4>();
    startDate = finishDate = DateTime.MinValue;

    var schedule = css.GetIterationDates(projectUri);
    var sch = schedule.FirstOrDefault(s => s.Path.Equals(iterationPath));

    if (sch != null)
        if (sch.StartDate.HasValue && sch.EndDate.HasValue)
            startDate = sch.StartDate.Value;
            finishDate = sch.EndDate.Value;
            return true;

    return false;

What this method does is actually pretty simple. It retrieves the entire iteration schedule for the specified team project and then tries to filter out the iteration dates for the specified iteration. The iteration schedule itself does not depend on the team, but rather it’s defined on the project level. Whether a team works on one of the iterations in the schedule or not does, of course, depend on the team in question. What this means, is that is multiple teams are working on the same iteration, the start and end dates will be the same for all the teams.

If we find the dates for the specified iteration path, we assign them to the output parameters and the method returns true. In any other case, the method returns false, indicating the iteration dates have not been read.

Executing the historical queries

Once we retrieve the iteration dates, we have to execute a historical query for every single iteration day and retrieve the filtered work items for that date. I’d just like to point out that we only have to do this for dates before and up to today. For example, if we are in the middle of the iteration, retrieving data past today simply doesn’t make any sense. To keep track of the data we’ll be retrieving using the historical queries, lets introduce a simple class:

class DataPoint
    public DateTime Date { get; set; }
    public double? RemainingWork { get; set; }
    public double IdealTrend { get; set; }
    public double ActualTrend { get; set; }

The Date and RemainingWork properties should be pretty self-explanatory.  Don’t worry about the trend line stuff just yet though, as we’ll cover that in the next post. Right now, here’s a snippet that will iterate through the iteration days, execute the historical query and retrieve the total remaining work from the work items:

DateTime today = DateTime.Today;
List<DataPoint> dataPoints = new List<DataPoint>();
var ci = CultureInfo.GetCultureInfo(1033);

for (DateTime date = startDate; date <= finishDate; date = date.AddDays(1))
    double? work = null;

    if (date <= today)
        string asof = date.AddDays(1).AddMilliseconds(-1).ToString(ci);
        string fullQuery = string.Format("{0} ASOF '{1}'", queryText, asof);
        IEnumerable<WorkItem> items = wiStore.Query(fullQuery).
            OfType<WorkItem>().Where(wi => wi.Fields.Contains(remainingWorkField));
        work = items.Sum(wi => wi.Fields[remainingWorkField].Value as double?);
    dataPoints.Add(new DataPoint
        Date = date,
        RemainingWork = work

What’s a bit interesting about this snippet is the way we define the AsOf clause for the historical query. Suppose we want to execute the query as of April 25th 2012? What exact time should we use? Passing just 04/25/2012 would retrieve the state at the start of that day (i.e. midnight). Since there could be changes during the day that we would miss by using this approach, this isn’t going to cut it. A better way would be to move the date one day forward (i.e. to 04/26/2012 0:00:00) and then turn the clock back by a millisecond. After this manipulation the DateTime value would be 04/25/2012 11:59:59.999 PM, which is the very last second of April 25th.

The rest of the code should be simple to understand. The Query method returns an instance of the WorkItemCollection class and we cast it to a sequence of WorkItem objects using the OfType<T> extension method. After that we can use the standard Enumerable<T> extensions to calculate the remaining work with ease.

Just to get the idea, here’s a chart that displays the data I’ve obtained by running this code using the query text obtained from the first post on my dev machine:

The resulting burndown chart

What’s a bit interesting about this chart is that, in spite of having the information about which days are considered weekends for our team project, Team WebAccess doesn’t seem to exclude any data points that are retrieved on weekends. For these days, we don’t expect the remaining work to change and we get numerous flat areas over the weekends. Here’s exactly what I mean:

The burndown chart with highlighted weekends

What do you think? Would it be better to include or exclude the weekends from the burndown chart? Should there be an option, so we can configure it both ways? Additionally, in case you’re wondering how to programmatically set or retrieve the weekend days for your team project, feel free to check out my blog post about managing the process configuration.

There’s more to come…

Coming up in the third and final part of the series, we check out how the actual and ideal burndown trend lines are calculated. If you’re interested in reading the first part of the series, you can find it here. I’d be very interested to hear how you like the burndown chart. Do you think it doesn’t present enough data and that it could be improved? Please feel free to share your thoughts!

Be Sociable, Share!
  • Santiago Matalonga

    Great post! Though my team would like to see a BD chart with non working days omitted.

    IS it possible to configure it?

    • http://blog.johnsworkshop.net/ Ivan Popek

      Hi santiago! It’s possible to configure the working days either programmatically (http://bit.ly/SLbc1q) or using the Process Template Editor (I believe), which is a part of the Team Foundation Server Power Tools package (http://bit.ly/GF8ljO). The link is for the VS11 Beta version, but it supposedly also works with the RC and, most probably, Visual Studio 2012 RTM.


  • JT

    One Issue with this approach I can think of unless I missed it is if you have a task that was developed before the sprint and injected in the middle of the sprint – how is the data structured within the task/sprint so that the sprint knows when the task was added to the sprint? When you perform a historical query – unless you know when it was added wouldn’t you make the mistake of adding those remaining hours for all the days before you actually added it to the sprint thus shifting the results upward for all of the historical queries? Is the system running and building a historical table so this wouldn’t be necessary? Is the system updating each day and storing the data?

  • Rohini Singh

    I am getting exception while parsing query at line:-
    IEnumerable items = wiStore.Query(fullQuery).OfType().Where(wi => wi.Fields.Contains(remainingWorkField));

    Exception detail:-
    Expecting left bracket. The error is caused by «’FabrikamFiber’»

    at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.Initialize(WorkItemStore store, String wiql, IDictionary context, Int32[] ids, Int32[] revs, Boolean dayPrecision)
    at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql, IDictionary context)
    at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql)
    at MyTfsStoryBoard.Form1.button1_Click(Object sender, EventArgs e) in c:downloadsMyTfsStoryBoardMyTfsStoryBoardMyTfsStoryBoardForm1.cs:line 205
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.Run(Form mainForm)
    at MyTfsStoryBoard.Program.Main() in c:downloadsMyTfsStoryBoardMyTfsStoryBoardMyTfsStoryBoardProgram.cs:line 18
    at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()

    Can anyone help?