Number of WorkItems with changesets or linked item

Question

We have a system in use where developers will log a support ticket using a new 'Support Ticket' work item in TFS 2010. If there's a code change as a result they will link the changeset to this work item type. Alternatively if there's a related Bug in the system that requires a code change they will link the Support ticket to the bug and then check in the changeset linked to the bug.

I'm trying to pull out a report either in TFS or preferably using SQL that gives me:

• the number of support tickets created in a month.
• the number of support tickets with linked bugs that have a changeset logged against them
• the number of support tickets with a change set directly linked to the support ticket.

Is there a way to do this in SQL

Answer 1

Run this against your Data Warehouse. This SQL will list all Work Items with their associated Change Sets. I've commented out a few items in the WHERE clause and left them in to give you an idea on what you can filter on as I'm not sure what process template you have setup in TFS.

The df.FilePath field commented out in the WHERE clause allows you to filter on specific repositories/branches.

SELECT DISTINCT --df.[FileName]
    --,df.FilePath
    dwi.System_title AS 'Title'
    ,dcs.ChangesetID AS 'ChangeSetID'
    ,dwi.System_id AS 'WorkItemID'
    ,dwi.System_WorkItemType
    ,dwi.System_State
    ,dwi.System_CreatedDate
    ,dwi.System_ChangedDate

FROM DimFile df 
JOIN FactCodeChurn fcc ON df.FileSK = fcc.FilenameSK
JOIN FactWorkItemChangeset fwi ON fcc.ChangesetSK = fwi.ChangesetSK
JOIN DimWorkItem dwi ON fwi.WorkItemID = dwi.System_id
AND fwi.TeamProjectCollectionSK = dwi.TeamProjectCollectionSK
AND fwi.RemovedDateTime = CONVERT(DATETIME, N'9999', 126)
JOIN DimChangeset dcs ON dcs.ChangesetSK = fcc.ChangesetSK 

WHERE dwi.System_revisedDate = CONVERT(DATETIME, N'9999', 126)
--AND df.FilePath LIKE '%$repositorylocation%'
--AND dwi.System_WorkItemType IN ('Product Backlog Item', 'Task', 'Bug')

ORDER BY dcs.ChangesetID

This code was run against TFS 2013, but I believe the Schema is the same in 2010

Answer 2

You can write queries against the TFS data warehouse or the TFS analysis cube, assuming you have reporting configured. Do not write queries directly against the project collection databases.

The data in the warehouse should contain what you're after. For example, there is a Fact table for linked items:

Work Item Changeset

Contains one row for each relationship between a work item revision and a changeset.

And for work item history:

Work Item History

Versioned file of work items using transition count and record count to aggregate information at a point in time.

It may require some tricky queries, but the data should be there.