在 SQL Server 中获取 TFS 2015 工作项层次结构

Question

我正在尝试在 Tableau 中创建一份报告，以生成 TFS 2015 中所有工作项及其各自层次结构的树结构如

长篇故事->功能->用户故事->任务

但是反复尝试创建 sql 查询都失败了。您能否帮助我进行SQL查询，以帮助获取所有工作项并显示其层次结构？

谢谢。

Answer 1

建议使用 TFS

REST API 在 TFS 中创建查询，而不是使用 SQL 查询，WIQL 如下所示：

"wiql": "select [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State], [System.Tags] from WorkItemLinks where (Source.[System.TeamProject] = 'xxx' and Source.[System.WorkItemType] <> '' and Source.[System.State] <> '') and ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') and (Target.[System.WorkItemType] <> '') order by [System.Id] mode (Recursive)"

Answer 2

因此，我找到了此查询，该查询有效，可以帮助您为层次结构创建 tableau 工作簿：

 WITH    cte
          AS ( SELECT   DimTeamProject.ProjectNodeName ,
                        System_WorkItemType ,
                        DimWorkItem.System_Id ,
                        FactWorkItemLinkHistory.TargetWorkItemID ,
                        DimWorkItem.System_Title,
                        DimWorkItem.System_State,
                        DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
                        DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
                        DimWorkItem.System_CreatedDate,
                        DimWorkItemLinkType.LinkName,
                        TeamProjectSK,
                        system_rev,
                        row_number() over( partition by system_id,TeamProjectSK, FactWorkItemLinkHistory.TargetWorkItemID order by system_rev desc ) rownum
               FROM     DimWorkItem ,
                        DimTeamProject ,
                        FactWorkItemLinkHistory,
                        DimWorkItemLinkType
               WHERE    DimWorkItem.TeamProjectSK = DimTeamProject.ProjectNodeSK 
                        AND DimWorkItem.System_Id = FactWorkItemLinkHistory.SourceWorkItemID
                        and DimWorkItemLinkType.WorkItemLinkTypeSK = FactWorkItemLinkHistory.WorkItemLinkTypeSK
                        /*    -To Test the Query using the project Name of our choice-    */
                        --AND ProjectNodeName =
                        AND System_State in ('ACTIVE','NEW')
                        /* -System Revisions are created when the entry is modified. Onlt the latest entry will have the below revised date-  */
                        AND System_RevisedDate = '9999-01-01 00:00:00.000'
                        AND DimWorkItemLinkType.Linkname IN ( 'Parent',
                                                     'child' )
               GROUP BY DimTeamProject.ProjectNodeName ,
                        DimWorkItem.System_Id ,
                        FactWorkItemLinkHistory.TargetWorkItemID ,
                        DimWorkItem.System_Title ,
                        System_WorkItemType,
                        DimWorkItem.System_State,
                        TeamProjectSK,
                        DimWorkItemLinkType.LINKName,
                        DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
                        DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
                        DimWorkItem.System_CreatedDate,
                        system_rev
             )
    SELECT distinct t1.ProjectNodeName ,
            t1.System_Id requirement_Id ,
            t1.System_WorkItemType,
            t1.System_Title requirement_title ,
            t2.System_Id Change_request_id ,
            t1.LinkName,
            t2.System_Title Change_Request_Title,
            t1.Microsoft_VSTS_Common_ActivatedDate,
            t1.System_CreatedDate,
            t1.Microsoft_VSTS_Scheduling_TargetDate,
            t1.System_State,
            T1.rownum
    FROM    cte t1
            INNER JOIN cte t2 ON t1.TargetWorkItemID = t2.System_Id
                                  and t1.rownum = 1
    ORDER BY t1.System_Id;

使用 CTE 查找获取完整的层次结构，它比之前发布的查询更快、更高效。