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)
Subscribe