TFS -为分层工作项生成报告

Question

我有一个层次结构的工作项链接到集合，如下所示：

Parent-1
    Child-1
        sub-child-1
        sub-child-2
    Child-2
        sub-child-3
Parent-2
    Child-3
        sub-child-4

现在，基于上面的层次结构，我需要使用SSRS创建一个报告，以显示每个父工作项的子项和子项的数量。

Answer 1

我已经在MSDN论坛上发布了同样的问题，他们说我需要创建SQL query来生成这种信息和here is their answer

Answer 2

您可以使用以下方法在SSRS中构建所需的工作项层次结构：

作为数据源的分析多维数据集连接

• 基于工作项树维度的MDX查询构建的数据集

• 正确配置报表tablix行组属性

您可以阅读我关于构建层次结构here的详细答案。

此外，此blog post可能会对您有所帮助。

Answer 3

我已经为我的组织创建了另一个可能有用的查询。它的输出：

Epic1
  -Feature1
     -User Story1
       -Task1
       -Task2
       -Task3
     -User Story2
       -Task1
       -Task2
   -Feature2
     -User Story1
       -Task1
       -Task2
       -Task3
     -User Story2
       -Task1
       -Task2

Epic2
  -Feature1
     -User Story1
       -Task1
       -Task2
       -Task3
     -User Story2
       -Task1
       -Task2
   -Feature2
     -User Story1
       -Task1
       -Task2
       -Task3
     -User Story2
       -Task1
       -Task2

我们使用以下查询来生成TFS 2015中的分层工作项：

;WITH    cte
          AS ( SELECT   DimTeamProject.ProjectNodeName ,
                        dimworkitem.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,
                        dimworkitem.System_rev,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_RemainingWork,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_OriginalEstimate,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_CompletedWork,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_StoryPoints,
                           CurrentWorkItemView.AZDES_SprintPoints,
                           CurrentWorkItemView.System_AssignedTo,

                        Row_Number() over(Partition by dimworkitem.system_id,TeamProjectSK, FactWorkItemLinkHistory.TargetWorkItemID Order by dimworkitem.system_rev DESC ) rownum
               FROM     DimWorkItem ,
                        DimTeamProject ,
                        FactWorkItemLinkHistory,
                        DimWorkItemLinkType,
                        CurrentWorkItemView
               WHERE    DimWorkItem.TeamProjectSK = DimTeamProject.ProjectNodeSK 
                        AND DimWorkItem.System_Id = FactWorkItemLinkHistory.SourceWorkItemID
                        and DimWorkItemLinkType.WorkItemLinkTypeSK = FactWorkItemLinkHistory.WorkItemLinkTypeSK
                        AND CurrentWorkItemView.System_Id=DimWorkItem.System_Id
                        and CurrentWorkItemView.ProjectNodeSK=DimWorkItem.TeamProjectSK
                        /*    -To Test the Query using the project Name of our choice-    */
                        --AND DimTeamProject.ProjectNodeName ='Test Project Name Here'

                        AND DimWorkItem.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 dimworkitem.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 ,
                        dimworkitem.System_WorkItemType,
                        DimWorkItem.System_State,
                        TeamProjectSK,
                        DimWorkItemLinkType.LINKName,
                        DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
                        DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
                        DimWorkItem.System_CreatedDate,
                        dimworkitem.system_rev,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_RemainingWork,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_OriginalEstimate,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_CompletedWork,
                           CurrentWorkItemView.Microsoft_VSTS_Scheduling_StoryPoints,
                           CurrentWorkItemView.AZDES_SprintPoints,
                           CurrentWorkItemView.System_AssignedTo
             )
    SELECT distinct t1.ProjectNodeName ,
                    t1.TeamProjectSK,
                    t1.System_Id requirement_Id ,
                    t1.System_WorkItemType,
                    t1.System_State,
                    t1.System_Title requirement_title ,
                    t2.System_Id Change_request_id ,
                    t1.LinkName,
                    t2.System_WorkItemType Change_Request_Type,
                    t2.TeamProjectSK,
                    t2.System_State change_request_system_state,
                    t2.System_Title Change_Request_Title,
                    t1.Microsoft_VSTS_Scheduling_OriginalEstimate,
                    t1.Microsoft_VSTS_Scheduling_RemainingWork,
                    t2.Microsoft_VSTS_Scheduling_CompletedWork,
                    t1.Microsoft_VSTS_Scheduling_StoryPoints,
                    t1.AZDES_SprintPoints,
                    t1.System_AssignedTo,

            t1.Microsoft_VSTS_Common_ActivatedDate,
            t1.System_CreatedDate,
            t1.Microsoft_VSTS_Scheduling_TargetDate,
            T1.rownum
    FROM    cte t1
            INNER JOIN cte t2 ON t1.TargetWorkItemID = t2.System_Id
                                  and t1.rownum = 1
                                  and t1.TeamProjectSK=t2.TeamProjectSK
                                  --and t1.System_Id=Test System id here
                                  --and t1.TeamProjectSK=Test Team Project SK here
                                --and  t1.projectnodename='Test Project Name Here'

    ORDER BY t1.System_Id;