Microsoft SQL Code To Get A List of Open TFS Items For All Projects
I have been playing around with a way to have Microsoft SQL Server Reporting Services (SRS) send a list of open items from Microsoft Team Foundation Server 2008 (TFS) to anyone who has open assigned TFS tasks within all projects. The database structure is not entirely clear nor does it appear that there are stored procedures to retrieve this information.
While doing SQL traces I discovered that TFS uses in-line SQL statements and builds dynamic SQL statements instead of executing stored procedures. As such, I have tested the SQL statement below and it appears to work correctly. One thing I am struggling with is any project created using the eScrum template. It does not store Areas and Iterations of Sprint Backlog Items the same way. If you have figured it out, please post a comment, and I’ll take a look at it some more later.
SELECT w1.ID, w1.Title, w2.Iteration, w2.[Iteration Path], w1.[Work Item Type], w1.State, w3.Email
FROMWorkItemsAre w1
LEFT JOINTFSWarehouse.dbo.Iteration w2 ON w1.IterationID = w2.__ID
LEFT JOINTFSWarehouse.dbo.Person w3 ON w1.[Assigned TO] = w3.Person
WHERE
w1.State <> ‘Done’
AND w1.State <> ‘Deleted’
AND w1.State <> ‘Deferred’
AND w1.State <> ‘Complete’
AND w1.Title IS NOT NULL
ORDER BY w2.Iteration, w1.Title
In a follow up post, Use Microsoft SRS To Automatically Send Open Tasks To TFS Users, I describe how to create the report using Microsoft SQL Server 2005’s SQL Server Business Intelligence Development Studio and how to schedule it.