TFS - Generate a Report for Hierarchical Work Items

Question

I have a hierarchical work items linked to gather as follows:

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

now based on the hierarchy above I need to create a report using SSRS to show the number of children and sub-children for each parent work item.

Answer 1

I have created another query for my organization which could be potentially useful. Its output:

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

We use the following query for generating the hierarchical work items in 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;

Answer 2

You can build the wanted hierarchy of work items in SSRS using:

• TFS Analysis Cube connection as datasource
• a dataset built upon an MDX query based on the [Work Item Tree] dimension
• the proper configuration of the Report tablix row group properties

You can read my detailed answer about building hierarchies here.

Also this blog post might help you.

Answer 3

I've posted the same question in MSDN forums and they stated that I need to create SQL query to generate this kind of information and here is their answer