Wednesday, March 7, 2012

How to generate Timesheet from Microsoft Team Foundation Server (TFS) 2010 2012

There is not straight forward way to generate timesheet using standard TFS query. Here is concept and sample code to generate Timesheet data from Team Foundation Server (TFS) 2010 using Client Object Model. The code works on following assumption and facts. 
  • You are using “Work Completed” field to track time spend on Work Item. On UI it could be anything. E.g. “Completed” on schedule tab.
  • This field is always having incremental values. That means changes values from 5 to 4 indicate you have decreased time spent which is impractical in real world.
  • You have access to all items for which you want to generate Timesheet.
The Idea
  • Connect to TFS using Client Object Model.
  • Use query to fetch all work item you are interested. In this case, I am interested to fetch all item updated on and after Mar 5, 2012.
  • Loop through all item.
  • For each item, loop through History items and look for change in field “Work Completed”
  • and difference of change is non zero
  • Log required information to CSV file.
  • That’s it, your timesheet is ready to open in Excel.
Working code:
using System;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Net;
using System.Collections.Generic;
using System.IO;

namespace TFS_Timesheet
    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; }

    class Program
        static void Main(string[] args)
            if (args.Length < 5)
                Console.WriteLine("Invalid Argument");
                Console.WriteLine("TFS_Timesheet.exe TFSURL UserName Password Domain StartDate");
                Console.WriteLine("TFS_Timesheet.exe http://server:port/dir/defaultcollection <UserName> <Password> <Domain> 2012-03-05");
                Console.WriteLine("Timesheet Started!");
                List timeEntries = new List();
                DateTime startFrom = DateTime.Parse(args[4] + "T00:00:00.0000000");
                StreamWriter timesheet = File.CreateText("c:\\timesheet.csv");

                Uri collectionUri = new Uri(args[0]);

                NetworkCredential cre = new NetworkCredential(args[1], args[2], args[3]);

                // Connect to the server and the store. 
                TfsTeamProjectCollection teamProjectCollection =
                   new TfsTeamProjectCollection(collectionUri, cre);

                WorkItemStore workItemStore = teamProjectCollection.GetService();
                WorkItemCollection workItems = workItemStore.Query("SELECT [System.Id], [System.ChangedDate], [System.Title], [System.State] FROM WorkItems WHERE [System.ChangedDate] >= '" + startFrom.ToLongDateString() + "' ORDER BY [System.ChangedDate]");
                foreach (WorkItem workItem in workItems)

                    if (workItem == null)

                    if (workItem.IsValid() == false)

                    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) >= startFrom)
                                TimeEntry 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);
                                Console.WriteLine(entry.Name + "\t" + entry.Date + "\t" + entry.Id + "\t" + entry.Hours);
                                timesheet.WriteLine(entry.Name + "," + entry.Date.ToShortDateString() + "," + entry.Id + ",\"" + entry.Title + "\"," + entry.Hours);
                Console.WriteLine("Timesheet Completed!");



Jason said...

This is exactly what I have been looking for! Thank you so much!

Unknown said...

I was just wondering, as I can see quality content in this place...

HM said...

Good one!