This project is read-only.

does anyone access timesheet data from the TFS qery editor?

Sep 9, 2012 at 9:12 PM

does anyone access timesheet data from the TFS qery editor?  try to that the report written by nick H. and download to excel etc... has anyone done this?

Sep 19, 2012 at 9:49 PM

Hello,

The TFS timesheet data is stored as an XML document within a single field of a TFS Work Item record - the downside of this is that it is not really readable within the query editor.

Have you tried the VS toolbox extension I'm written for reporting (it's available as a seperate download in the reporting section)?

Thanks

Nick

Dec 11, 2013 at 10:50 PM
Edited Dec 11, 2013 at 10:50 PM
I know that this is probbably way out but in the event that someone needs this for the future here is the linqpad query that I use to collect timesheet information.
void Main()
{
    var query = from w in WorkItemLongTexts where w.FldID == 10451 select w;
    Dictionary<int,int> WorkItemVersions = new Dictionary<int,int>();
    List<WorkItemTime> TimeList = new List<WorkItemTime>();
    
    foreach (var element in query)
    {
        if (!WorkItemVersions.ContainsKey(element.ID))
            WorkItemVersions.Add(element.ID, element.Rev);
        
        if (WorkItemVersions[element.ID] < element.Rev)
            WorkItemVersions[element.ID] = element.Rev;
    }
    
    foreach (var wiv in WorkItemVersions)
    {
        var individualWorkItem = (from q in query where q.ID == wiv.Key && q.Rev == wiv.Value select q).First();
        XmlDocument xDoc = new XmlDocument();
        xDoc.LoadXml(individualWorkItem.Words);
        XmlNodeList timelist = xDoc.SelectNodes("/ArrayOfTimeSheetEntry/TimeSheetEntry");
        foreach (XmlNode time in timelist)
        {
            WorkItemTime t = new WorkItemTime()
            {
                 CreatedDate = Convert.ToDateTime(time.Attributes["CreatedDate"].Value),
                 CreatedBy = time.Attributes["CreatedBy"].Value,
                 Comment =  time.Attributes["Comments"].Value,
                 Time =  Convert.ToSingle(time.Attributes["Minutes"].Value) /60.0f,
                 TimeSheetDate = Convert.ToDateTime( time.Attributes["TimeSheetDate"].Value),
                 WorkItemId = wiv.Key,
                 
            };
            TimeList.Add(t);
        }
    }
    //TimeList.Dump();
    var subquery = from r in TimeList where r.CreatedBy == "{TFS USERS NAME}" && r.TimeSheetDate > new DateTime(2013,11,21) orderby r.TimeSheetDate select r ;
    subquery.Dump();
}

// Define other methods and classes here
class WorkItemTime
{
    public string CreatedBy { get; set; }
    public string Comment { get; set; }
    public DateTime TimeSheetDate { get; set; }
    public DateTime CreatedDate { get; set; }
    public float Time { get; set; }
    public int WorkItemId {get;set;}
}
Mar 25, 2016 at 5:57 PM
I have little utility to access the timesheet data, does any one need?

Thanks
Jayalal