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

Question

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

welcome to stackoverflow :) ,tasknumber column is in which table? — Ashu, Aug 01 '17 at 03:52
https://stackoverflow.com/questions/33157683/number-of-workitems-with-changesets-or-linked-items-with-changesets/33960311#33960311?newreg=678c4fc6e2904a9ab3399fc6e59c334a @kevchadders had answered similar question. But that was for tfs 2013 I suppose. — Rohit Jachak, Aug 01 '17 at 04:03

Andy Li-MSFT · Accepted Answer · 2017-08-03T02:00:45.867

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

I appreciate your response. Could not paste the image so posting this link to give clear picture of what must be the problem with this query not working for me. My FactWorkItemChangeset is also empty. http://imgur.com/a/3VHyA. Could you please provide me some suggestions. — Rohit Jachak, Aug 02 '17 at 03:27
@RohitJachak 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](https://blogs.msdn.microsoft.com/sunder/2010/04/27/when-trying-to-enable-reporting-after-upgrading-my-server-i-get-a-schema-warning/) — Andy Li-MSFT, Aug 03 '17 at 01:57

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

1 Answers1