Cumulative Flow Diagram from TFS Data

One of the teams Ive worked with frequently ended a sprint with 2 or more user stories almost ready. To them almost meant less than 2 hours, but in reality, due to sprint planning, task breakdowns etc, we more often than not finished those user stories on day 2 of the next sprint.

There were a number of reasons for this, but to aid in our investigation into why, I used a Cumulative Flow Diagram (CFD) each sprint.

There are any number of ways of creating CFDs and some people are lucky enough to be using a tool that does it for you. Unfortunately TFS, the tool I was using, wasnt one of them. After some searching I eventually found a great post called Cumulative Flow Diagram – How to create one in Excel 2010

It was almost exactly what I wanted, but because it needed manual data entry, I was manually digging through TFS searching for the right info. Thats not too bad if you remember to do it every day, but Id often not be able to due to meetings or some other commitment, let alone illness or annual leave.

What I wanted, was a way to extract the information on a daily basis without me having to open a web browser.

Less Manual Approach

Part 1 What data do I need

This was fairly simple as I realised I need to know:

  • The User Story ID
  • Who the User Story is assigned to? That way I know if its in Development (not the testers or product owner), Testing (one of the testers) or Ready (the product owner)
  • The State, which is one of Committed, In Progress, Done

That way, I can calculate the current status of a User Story by the logic in the following table:

StateAssigned ToStatus
CommittedEmptyTo Do
CommittedA TesterTesting
CommittedProduct OwnerReady
CommittedAnyone ElseIn Progress
Donen/aDone

Part 2 Write TFS Query

Getting the data out of TFS is relative simple. In the web interface, go to Work->Queries, create a new My Queries called CFD and fill it out as below, where Iteration Path is your current iteration:

CFD TFS Query Configuration
CFD TFS Query Configuration

It doesnt matter how you configure the column options for this query as well be extracting things via C# next.

Part 2 Command Line Tool

Running the above query from C# is also simple. The below code clearly has a lot to be desired, but it was a quick 5 second proof of concept and as its been good enough Ive never tweaked it.

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;

using System;
using System.Configuration;

namespace CFDConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
	    var tfsServer = ConfigurationManager.AppSettings["TFS_SERVER"];
            var projectName = ConfigurationManager.AppSettings["PROJECT_NAME"];
            var queryFolder = ConfigurationManager.AppSettings["QUERY_FOLDER"];
            var queryName = ConfigurationManager.AppSettings["QUERY_NAME"];

            // Connect to the work item store
            var tpc = new TfsTeamProjectCollection(new Uri(tfsServer));
            var workItemStore = (WorkItemStore)tpc.GetService(typeof(WorkItemStore));

            // Run a saved query.
            var queryRoot = workItemStore.Projects[projectName].QueryHierarchy;
            var folder = (QueryFolder)queryRoot[queryFolder];
            var query = (QueryDefinition)folder[queryName];

            var queryResults = workItemStore.Query(query.QueryText);

            for (int i = 0; i < queryResults.Count; i++)
            {
                Console.WriteLine(
                    "{0},{1},{2}",
                    queryResults[i].Id,
                    queryResults[i].Fields["Assigned To"].Value,
                    queryResults[i].State);
            }

            Console.ReadLine();
        }
    }
}

As you can see, Ive placed some stuff in the App.config under App.Settings, but otherwise its very simple.

Part 3 Scheduled Tasks

So I dont have to remember to run this every day, I set up a scheduled tasks to run every morning at 8am, running the following command to output to a file called the current days date and time.

cfd_console.exe &gt; %date:~10,4%_%date:~4,2%_%date:~7,2%__%time:~0,2%_%time:~3,2%_%time:~6,2%.txt

This way, for whatever time period you want, you just have to use those files. No outlook reminders or alarms!

Part 4 Cumulative Flow Diagram from TFS Data

When you have a period of time you want to produce a CFD chart for, simply take the relevant .txt files and import the data into the excel template from the above post.

Im sure this could be automated also, but as it only takes a few minutes every iteration I havent bothered.

Summary

The above way is still fairly manual, but its quicker than looking at PBIs and Tasks in TFS regularly. Theres also a lot to be desired with the coding, but it serves its purpose!

I could of course make this into an Excel app and possibly automate the whole thing, but for now, its more than good enough for my needs. One day maybe that will change, but in the meantime, feel free to take the above and adapt it to your needs.

Finally, if youre not already creating a CFD for each sprint, I highly recommend you do. Its a great easy way to explain to both the team and any stakeholders the work in progress and how it affects throughput.