Report on Work breakdown in TFS

by Ewald Hofman 13. May 2009 15:32

Introduction

In Team Foundation Server 2005 and 2008 it is possible to define links between work items. By doing this, you can imply a work breakdown for your project. This work breakdown is not explicit, since these links are not hierarchical. Therefore is the only report in TFS that does something with the links not very usable. It shows all work items that have some relations.

When work item A is related to work item B, the report shows you two lines:

  1. A is related to B
  2. B is related to A

When you make the agreement in a project that:

  • The highest level of the work breakdown is always a scenario in the Agile process template, and Change Request or Requirement in the CMMI process template.
  • The second level are the tasks and the bugs that are related to the root level,
  • The third level are the bugs that are related to a task

You can now create a report that shows you the implied hierarchy of your work breakdown.

When we take it even to a further level, it is also possible to create a rollup of hours. On the second and the third level, the hours are retrieved from the warehouse, and the hours at root level are a summary of the all related tasks and bugs. When you do this, you get insight in the progression of the project.

Implementation

In TFS, there is the operational database, which is unsupported to use. The data from the operational database is aggregated in the TfsWarehouse database. The last data store of TFS is the SSAS cube which is used to create high performance calculations on your data.

For this report, we need the TfsWarehouse database. To get the three levels of the work breakdown we have to add the Work Item table three times to the query and link the tables together via the Related Work Item table. Then add some of the other tables to get all the other information you need. This might result for the CMMI process template in a query as below. To create the report for the Agile process template, you have to modify some parts like the Estimation field. I have attached the rdl file, which you can use as base for your own report.

SELECT 
    [Team Project]    = tp.[Team Project]
,    [Iteration Path]    = Iteration.[Iteration Path]
,    [Area Path]    = Area.[Area Path]
,    L0_WIType    = SUBSTRING(level0.System_WorkItemType, 1, 1)
,    L0_State        = level0.System_State
,    L0_Title        = level0.System_Title
,    L0_Estimate    = ISNULL(cw_level0._Microsoft_VSTS_CMMI_Estimate, 0)
,    L0_Completed    = ISNULL(cw_level0._Microsoft_VSTS_Scheduling_CompletedWork, 0)
,    L0_ETC        = CASE WHEN level0.System_State = 'Proposed' THEN ISNULL(cw_level0._Microsoft_VSTS_CMMI_Estimate, 0) ELSE ISNULL(cw_level0._Microsoft_VSTS_Scheduling_RemainingWork, 0) END
,    L0_EAC        = CASE WHEN level0.System_State = 'Proposed' THEN ISNULL(cw_level0._Microsoft_VSTS_CMMI_Estimate, 0) ELSE ISNULL(cw_level0._Microsoft_VSTS_Scheduling_RemainingWork, 0) + ISNULL(cw_level0._Microsoft_VSTS_Scheduling_CompletedWork, 0) END
,    L0_FinishDate    = cw_level0.Osellus_Iris_Activity_EndDate
,    L0_AssignedTo    = level0_assignedto.Person
,    L0_ResolvedBy    = level0.Microsoft_VSTS_Common_ResolvedBy
,    L0_ResolvedDate    = cw_level0.Microsoft_VSTS_Common_ResolvedDate
,    L0_ClosedBy    = level0.Microsoft_VSTS_Common_ClosedBy
,    L0_ClosedDate    = cw_level0.Microsoft_VSTS_Common_ClosedDate
,    L0_Reason    = level0.System_Reason
,    L0_WI        = level0.System_Id
,    L1_WIType    = SUBSTRING(level1.System_WorkItemType, 1, 1)
,    L1_State        = level1.System_State
,    L1_Title        = level1.System_Title
,    L1_Estimate    = ISNULL(cw_level1._Microsoft_VSTS_CMMI_Estimate, 0)
,    L1_Completed    = ISNULL(cw_level1._Microsoft_VSTS_Scheduling_CompletedWork, 0)
,    L1_ETC        = CASE WHEN level1.System_State = 'Proposed' THEN ISNULL(cw_level1._Microsoft_VSTS_CMMI_Estimate, 0) ELSE ISNULL(cw_level1._Microsoft_VSTS_Scheduling_RemainingWork, 0) END
,    L1_EAC        = CASE WHEN level1.System_State = 'Proposed' THEN ISNULL(cw_level1._Microsoft_VSTS_CMMI_Estimate, 0) ELSE ISNULL(cw_level1._Microsoft_VSTS_Scheduling_RemainingWork, 0) + ISNULL(cw_level1._Microsoft_VSTS_Scheduling_CompletedWork, 0) END
,    L1_FinishDate    = cw_level1.Osellus_Iris_Activity_EndDate
,    L1_AssignedTo    = level1_assignedto.Person
,    L1_ResolvedBy    = level1.Microsoft_VSTS_Common_ResolvedBy
,    L1_ResolvedDate    = cw_level1.Microsoft_VSTS_Common_ResolvedDate
,    L1_ClosedBy    = level1.Microsoft_VSTS_Common_ClosedBy
,    L1_ClosedDate    = cw_level1.Microsoft_VSTS_Common_ClosedDate
,    L1_Reason    = level1.System_Reason
,    L1_WI        = level1.System_Id
,    L1_FirstL2_WI    = (SELECT Min(w.System_Id) FROM [Related Current Work Items] r INNER JOIN [Current Work Item] c ON c.__TrackingId = r.[Current Work Item_TrackingIdLeft] INNER JOIN [Work Item] w ON w.__ID = c.[Work Item] AND w.System_WorkItemType IN (@Level2WIT) WHERE r.[Current Work Item_TrackingIdRight] = cw_level1.__TrackingId)
,    L2_WIType    = SUBSTRING(level2.System_WorkItemType, 1, 1)
,    L2_State        = level2.System_State
,    L2_Title        = level2.System_Title
,    L2_Estimate    = ISNULL(cw_level2._Microsoft_VSTS_CMMI_Estimate, 0)
,    L2_Completed    = ISNULL(cw_level2._Microsoft_VSTS_Scheduling_CompletedWork, 0)
,    L2_ETC        = CASE WHEN level2.System_State = 'Proposed' THEN ISNULL(cw_level2._Microsoft_VSTS_CMMI_Estimate, 0) ELSE ISNULL(cw_level2._Microsoft_VSTS_Scheduling_RemainingWork, 0) END
,    L2_EAC        = CASE WHEN level2.System_State = 'Proposed' THEN ISNULL(cw_level2._Microsoft_VSTS_CMMI_Estimate, 0) ELSE ISNULL(cw_level2._Microsoft_VSTS_Scheduling_RemainingWork, 0) + ISNULL(cw_level2._Microsoft_VSTS_Scheduling_CompletedWork, 0) END
,    L2_FinishDate    = cw_level2.Osellus_Iris_Activity_EndDate
,    L2_AssignedTo    = level2_assignedto.Person
,    L2_ResolvedBy    = level2.Microsoft_VSTS_Common_ResolvedBy
,    L2_ResolvedDate    = cw_level2.Microsoft_VSTS_Common_ResolvedDate
,    L2_ClosedBy    = level2.Microsoft_VSTS_Common_ClosedBy
,    L2_ClosedDate    = cw_level2.Microsoft_VSTS_Common_ClosedDate
,    L2_Reason    = level2.System_Reason
,    L2_WI        = level2.System_Id
FROM
            [Team Project]            tp
    INNER JOIN    [Current Work Item]        cw_level0
                ON    cw_level0.[Team Project]        = tp.__ID
    INNER JOIN    [Work Item]            level0
                ON    level0.__ID            = cw_level0.[Work Item]
                AND    level0.System_WorkItemType    IN (@RootWIT)
    INNER JOIN    [Person]                level0_assignedto
                ON    level0_assignedto.__ID        = cw_level0.[Assigned To]
    INNER JOIN    Iteration
                ON    Iteration.__ID            = cw_level0.Iteration
    INNER JOIN    Area
                ON    Area.__ID            = cw_level0.Area
    LEFT JOIN    (        [Related Current Work Items]        rel_level0_level1
            INNER JOIN    [Current Work Item]        cw_level1
                        ON    cw_level1.__TrackingId        = rel_level0_level1.[Current Work Item_TrackingIdLeft]
            INNER JOIN    [Work Item]            level1
                        ON    level1.__ID            = cw_level1.[Work Item]
                        AND    level1.System_WorkItemType     IN (@Level1WIT)
            LEFT JOIN    [Person]                level1_assignedto
                        ON    level1_assignedto.__ID        = cw_level1.[Assigned To]
            )
                ON    cw_level0.__TrackingId        = rel_level0_level1.[Current Work Item_TrackingIdRight]
    LEFT JOIN    (        [Related Current Work Items]        rel_level1_level2
            INNER JOIN    [Current Work Item]        cw_level2
                        ON    cw_level2.__TrackingId        = rel_level1_level2.[Current Work Item_TrackingIdLeft]
            INNER JOIN    [Work Item]            level2
                        ON    level2.__ID            = cw_level2.[Work Item]
                        AND    level2.System_WorkItemType    IN (@Level2WIT)
            LEFT JOIN    [Person]                level2_assignedto
                        ON    level2_assignedto.__ID        = cw_level2.[Assigned To]
            )
                ON    cw_level1.__TrackingId        = rel_level1_level2.[Current Work Item_TrackingIdRight]
WHERE
    tp.[Team Project]        = @Project
AND    Area.[Area Path]        LIKE @AreaParam + '%'
AND    Iteration.[Iteration Path]    IN (@IterationParam)

WorkBreakDown.rdl (164,69 kb)

Tags:

Reporting | Work items

Comments

11/3/2009 11:33:40 AM #

trackback

TFS 2008 API - Create a link between work items

TFS 2008 API - Create a link between work items

Ewald Hofman |

12/11/2010 4:13:57 AM #

Walkman

Is this Query valid for TFS 2010 as well?

Walkman United Kingdom |

12/11/2010 4:40:00 AM #

Ewald Hofman

No this query is for TFS 2008.

In TFS 2010 you don't need this, because in there there are the hierarchical work items in which you can model your breakdown much better.

Ewald Hofman Netherlands |

3/21/2011 10:28:50 AM #

Thiago

Where i can find some Reports to download for TFS 2010?
I need some others reports in Cmmi Template... but is too dificult to develop them...


Thiago Brazil |

Comments are closed

Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen


ClusterMap

Widget Statistics not found.

There is an error in XML document (0, 0).X

Recent comments

Comment RSS