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:
- A is related to B
- 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)
Subscribe