[SOLVED] Link 'task" workitem to every changeset in the SSRS report on TFS 2015 Server

Link 'task" workitem to every changeset in the SSRS report on TFS 2015 Server

I have created SSRS report on the TFS server for the Changeset ID. I want to add a column which will also mention the task numbers associated with every changeset ID as there may be different tasks for one changeset ID. Please can you suggest me some solution.

SELECT --distinct
       [project_name],
       ChangeSetId,
       CreationDate,
       Comment,
       ProviderDisplayName,
       DisplayName
  FROM tbl_changeset as c
       JOIN
       tbl_IdentityMap IM
         ON C.OwnerID = IM.localId
       JOIN tfs_configuration.dbo.tbl_Identity u
         ON IM.MasterID = u.Id
       JOIN
       [tbl_Version] v
         ON v.VersionFrom = c.ChangeSetId
       JOIN
       [dbo].[vw_projects] p
         ON p.DataspaceId = v.ItemDataspaceId
 WHERE p.project_name = 'XXXX'
ORDER BY C.CreationDate,
         C.ChangeSetID DESC

Solution

I tested the query wrote by @kevchadders against TFS 2015, it works fine.

So, you can custom your report accordingly based on the query.

To review the result, you can run the below query directly on your Sql Server:

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 [Tfs_Warehouse].[dbo].[DimFile] df 
JOIN [Tfs_Warehouse].[dbo].[FactCodeChurn] fcc ON df.FileSK = fcc.FilenameSK
JOIN [Tfs_Warehouse].[dbo].[FactWorkItemChangeset] fwi ON fcc.ChangesetSK = fwi.ChangesetSK
JOIN [Tfs_Warehouse].[dbo].[DimWorkItem] dwi ON fwi.WorkItemID = dwi.System_id
AND fwi.TeamProjectCollectionSK = dwi.TeamProjectCollectionSK
AND fwi.RemovedDateTime = CONVERT(DATETIME, N'9999', 126)
JOIN [Tfs_Warehouse].[dbo].[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

Besides, you can also reference this article if you want to export the changeset report to excel:

Find Changeset details for all work items in a TFS Query

UPDATE:

For the TFS Warehouse cannot be used issue:

The data warehouse schema changed, the existing warehouse database was out of date. So, you can try to create a new warehouse then rebuild in the Admin Console.

Reference this article to fix that: http://blogs.ripple-rock.com/richarderwin/2014/11/06/TFSWarehouseTheDatabaseYouSpecifiedCannotBeUsed.aspx