TFS Timesheets

In this post I want to talk about getting time-spent reports out of TFS.

When working with TFS, we manually increase the Completed work field by the number of hours spent on the task. We don't record when those N hours were spent. The assumption is that developers update it as soon as the work is done.

So what do you do if you want a summary table showing how many hours were spent, on what, and when? Unfortunately, there's no such feature out of the box. However, there is a set of APIs that will help us write a utility that produces the report we want. It's called the TFS Object Model. And that's exactly what we're going to do: write that utility.

Our application will be a console app.

Here is an example of how it's used:

TFS_Timesheet.exe /collection:http://server:8080/tfs/defaultcollection /from:2012-03-01 /to:2012-03-31

The parameters are the address of the TFS project collection and the start and end dates of the report.

Let's create a class to hold these parameters:

public class RequestData
{
    public RequestData()
    {
        From = new DateTime(1900, 1, 1);
        To = new DateTime(2500, 1, 1);
    }

    public Uri Collection { get; set; }
    public DateTime From { get; set; }
    public DateTime To { get; set; }
}

When the application starts, we initialize our class:

public static void Main(string[] args)
{
	var request = new RequestData();

	foreach (var arg in args)
	{
		if (arg.StartsWith("/collection:"))
		{
			request.Collection = new Uri(arg.Substring("/collection:".Length));
		}
		else if (arg.StartsWith("/from:"))
		{
			request.From = DateTime.Parse(arg.Substring("/from:".Length) + "T00:00:00.0000000");
		}
		else if (arg.StartsWith("/to:"))
		{
			request.To = DateTime.Parse(arg.Substring("/to:".Length) + "T00:00:00.0000000");
		}
	}
}

Reading data from TFS

Let's add one more class that will hold data about a piece of work done.

public class TimeEntry
{
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public int Id { get; set; }
    public string Title { get; set; }
    public double Hours { get; set; }
    public string Comments { get; set; }
}

Now our job is to get a collection of objects of this class, i.e. the list of work done during the given period.

var timeEntries = new List<TimeEntry>();
var teamProjectCollection = new TfsTeamProjectCollection(request.Collection, true);
var workItemStore = teamProjectCollection.GetService<WorkItemStore>();
var workItems = workItemStore.Query(string.Format(
    @"SELECT [System.Id], [System.ChangedDate], [System.Title], [System.State]
      FROM WorkItems
      WHERE [System.ChangedDate] >= '{0}' AND [System.ChangedDate] < '{1}'
      ORDER BY [System.ChangedDate]", request.From.ToLongDateString(), request.To.AddDays(1).ToLongDateString()));

foreach (WorkItem workItem in workItems)
{
    if (workItem == null || !workItem.IsValid()) continue;

    foreach (Revision rev in workItem.Revisions)
    {
        foreach (Field field in rev.Fields)
        {
            if (field.Name == "Completed Work" &&
                field.OriginalValue != field.Value &&
                Convert.ToDateTime(rev.Fields[CoreField.ChangedDate].Value) >= request.From &&
                Convert.ToDateTime(rev.Fields[CoreField.ChangedDate].Value) < request.To.AddDays(1))
            {
                var entry = new TimeEntry();
                entry.Id = workItem.Id;
                entry.Date = Convert.ToDateTime(rev.Fields[CoreField.ChangedDate].Value);
                entry.Name = rev.Fields[CoreField.ChangedBy].Value.ToString();
                entry.Title = workItem.Title;
                entry.Hours = Convert.ToDouble(field.Value) - Convert.ToDouble(field.OriginalValue);
                if (entry.Hours > 0)
                {
                    timeEntries.Add(entry);
                }
            }
        }
    }
}

Building the report

The final step is to build the report from the collected data.

private static void WriteTimesheet(List<TimeEntry> timeEntries)
{
    var workItems = timeEntries.Select(t => t.Id).Distinct().OrderBy(t => t).ToList();
    var dates = timeEntries.Select(t => t.Date.Date).Distinct().OrderBy(t => t).ToList();

    using(var timesheet = File.CreateText("timesheet_report.html"))
    {
        timesheet.Write("<html><head><title>TFS Timesheet Report</title><style>\n"+Properties.Resources.Css+"\n</style>");
        timesheet.Write("<meta content=\"text/html; charset=utf-8\" http-equiv=\"content-type\" />");
        timesheet.Write("<head><body><table><thead><tr><th class='id'>ID</th><th class='title'>Title</th><th>&nbsp;</th>");

        foreach (var date in dates)
        {
            timesheet.Write("<th>" + date.ToShortDateString() + "</th>");
        }

        timesheet.WriteLine("</tr></thead><tbody>");

        // Total time.
        timesheet.Write("<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>");

        foreach (var date in dates)
        {
            var entries = timeEntries.Where(t => t.Date.Date == date).ToList();
            var hours = entries.Aggregate(0.0, (t, i) => t + i.Hours);

            timesheet.Write("<td class='total'>" + hours + "</td>");
        }

        timesheet.Write("</tr>");

        // Time broken down by task.
        foreach (var wid in workItems)
        {
            timesheet.Write("<tr>");
            timesheet.Write("<td class='id'>" + wid + "</td>");

            var title = timeEntries.First(t => t.Id == wid).Title;
            timesheet.Write("<td class='title'>" + title + "</td>");

            var entries = timeEntries.Where(t => t.Id == wid).ToList();
            var hours = entries.Aggregate(0.0, (t, i) => t + i.Hours);
            timesheet.Write("<td class='total'>" + hours + "</td>");

            foreach (var date in dates)
            {
                entries = timeEntries.Where(t => t.Id == wid && t.Date.Date == date).ToList();
                hours = entries.Aggregate(0.0, (t, i) => t + i.Hours);

                timesheet.Write("<td>");
                if (hours > 0)
                {
                    timesheet.Write(hours);
                }
                else
                {
                    timesheet.Write("&nbsp;");
                }

                timesheet.Write("</td>");
            }

            timesheet.WriteLine("</tr>");
        }

        timesheet.WriteLine("</tbody></table></body></html>");
    }
}

You can download the whole thing here.