[Update] Time sheet

by Ewald Hofman 4. October 2009 01:08

A lot of software shops want to use TFS as the time tracking tool. Out of the box, TFS does not provide this information, because all time is entered cumulative per work item. But you can find the information when you look at the transaction database table where all the changes to the work items are stored.

This is exactly what I did. I created a report to read from the transaction table to present the information per user per day. This leads to report as below.

 

The post shows you how you can find this information yourself. At the bottom of the post you can find the rdl.

The base of the report is based on the transaction table of the work items. This table is called "Work Item History". The tricky part of the query is to find for each transaction record (which is a change to - or revision of - the work item) its previous revision. When you have that information, you can calculate the difference between the two records. This difference is the amount of time the developer has added to the completed work, and thus the hours registered.

The Sql statement that gives you this information is the following.

SELECT
    ...
FROM
            dbo.[Work Item History]
    INNER JOIN    dbo.[Work Item]
                    ON    dbo.[Work Item History].[Work Item]        = dbo.[Work Item].__ID
    LEFT JOIN    (            dbo.[Work Item History] [Previous Work Item History] 
                INNER JOIN    dbo.[Work Item] [Previous Work Item]
                                ON    [Previous Work Item History].[Work Item]    = [Previous Work Item].__ID 
            )
                ON    [Previous Work Item].System_Id            = [Work Item].System_Id
                AND    [Previous Work Item].System_Rev            = [Work Item].System_Rev - 1
                AND    [Previous Work Item History].[Record Count]    = -1
 

For each transaction there is a negative record to set the values to 0 and a positive record to set the values to the actual values. For the previous revision we are only interested in the positive record, which is decorated with the "Record Count = -1.

When you have this starting point, you can add more tables to support filtering the data, such as on Team Project, Area path, Iteration path, Assignee, etc. I did add those four filters to the report. This resulted in the report that is attached to the blog post.

This report is view only. Notion solution has created a nice product that adds the ability to enter your time sheet from within Visual Studio. For more information on that solution, see http://www.notionsolutions.com/Products/Pages/NotionTimesheet.aspx

TimeSheet.rdl (29.97 kb)

Tags:

Reporting | VSTS 2008 | Work items

Comments

3/6/2011 10:47:15 AM #

Peter Lindholm

Hi. I'm trying to upgrade this solution to TFS 2010, but I'm having some trouble because of the changed schema in the warehouse.

It seems one should use the WorkItemHistoryView, but I can't seem to find [Previous Work Item History] anywhere (among lots others).

Any chance you know where to find this information?

Peter Lindholm Denmark |

Comments are closed

Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen


ClusterMap

Widget Statistics not found.

There is an error in XML document (0, 0).X

Recent comments

Comment RSS