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.
- 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.
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");
}
else
{
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");
timesheet.WriteLine("Name,Date,TFSId,Title,Hours");
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)
continue;
if (workItem.IsValid() == false)
{
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) >= 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);
timeEntries.Add(entry);
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);
}
}
}
}
timesheet.Close();
timesheet.Dispose();
Console.WriteLine("Timesheet Completed!");
}
Console.Read();
}
}
}
Enjoy! 