Query to get workitem with link type as CHILD

Question

For each bug we found, we have several child task:

[Bug]

   [Child Task]

   [Child Task]....

I'm making a report to collect the remaining hours for all the task within a Bug.

Can someone please shine some light how to make a query for this.

I'm using Report Builder 2.0 and I can either get to TFS Datawarehouse (TFSReportDS) and TFS Analysis Service (TFSOlapReportDS)

Thanks

Answer 1

Hi Garynguyen, 

Thanks for your post.

I think you can create the Work Items and Direct Linksquery to query that child tasks for your bugs, then open the query in Excel to collect the remaining hours.   

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 2

I want to do this in Report so it can collect data into dashboard and draw a chart on  dashboard as well.

Can we do something like that?

Thx

Answer 3

Hi Garynguyen, 

Thanks for your reply.

To customizing reports in TFS 2010, please refer to the detailed information in this document: http://msdn.microsoft.com/en-us/library/ff730838.aspx.     

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Well, we know how to do report for simple query.

But for what we need to get a list of Link Type as Child, is what we're looking for.

Thx.

Answer 5

Hi Garynguyen, 

Thanks for your reply.

To get a list of Like Type as Child, you can use the Link Nameattributes to filter work items, please refer to the Filter Based on an Attribute of a Link Type section in this document: http://msdn.microsoft.com/en-us/library/ms244678.aspx#linked_Work_Item.  

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Gary,

You can run a query something like below on your TFS Warehouse database.

SELECT * FROM DimWorkItem DWI
INNER JOIN FactWorkItemLinkHistory FWILH ON DWI.System_Id = FWILH.TargetWorkItemID
WHERE FWILH.WorkItemLinkTypeSK IN (SELECT DWILT.WorkItemLinkTypeSK FROM DimWorkItemLinkType DWILT WHERE DWILT.LinkName = N'Child')
AND DWI.System_WorkItemType = N'Task'
AND DWI.System_Rev = (SELECT MAX(DWI1.System_Rev) FROM DimWorkItem DWI1 WHERE DWI1.System_Id = DWI.System_Id)
AND FWILH.SourceWorkItemID IN (SELECT DWI2.System_Id FROM DimWorkItem DWI2 WHERE DWI2.System_WorkItemType = N'Bug');

Tables involved: DimWorkItem, FactWorkItemLinkHistory, DimWorkItemLinkType.

You may also want to look into limiting the query results for your particular Team Project (DimTeamProject table).

---

Please remember to mark the replies as answers if they help Thanks, --Kuldeep

Answer 7

Yep, thank you very much Kuldeep.

This is exactly what we're looking. We would never figure out these join table and their relationship in the database.

With this query, would you please show us how to get the related "Remaining hours" for those tasks?

I don't see a WorkItem table that contain the "Remaining hours" column.

Thanks,

Gary

Answer 8

Nevermind, from yours start up query, i'm be able to get the query:

SELECT     DWI.System_Id, DWI.System_Title, dbo.FactCurrentWorkItem.Microsoft_VSTS_Scheduling_RemainingWork, dbo.DimArea.AreaPath, dbo.DimDate.Date
FROM         dbo.DimWorkItem AS DWI INNER JOIN
                      dbo.FactWorkItemLinkHistory AS FWILH ON DWI.System_Id = FWILH.TargetWorkItemID INNER JOIN
                      dbo.FactCurrentWorkItem ON DWI.WorkItemSK = dbo.FactCurrentWorkItem.WorkItemSK INNER JOIN
                      dbo.DimArea ON DWI.AreaSK = dbo.DimArea.AreaSK CROSS JOIN
                      dbo.DimDate
WHERE     (FWILH.WorkItemLinkTypeSK IN
                          (SELECT     WorkItemLinkTypeSK
                            FROM          dbo.DimWorkItemLinkType AS DWILT
                            WHERE      (LinkName = N'Child'))) AND (DWI.System_WorkItemType = N'Task') AND (DWI.System_Rev =
                          (SELECT     MAX(System_Rev) AS Expr1
                            FROM          dbo.DimWorkItem AS DWI1
                            WHERE      (System_Id = DWI.System_Id))) AND (FWILH.SourceWorkItemID IN
                          (SELECT     System_Id
                            FROM          dbo.DimWorkItem AS DWI2
                            WHERE      (System_WorkItemType = N'Bug')))

Answer 9

Good to know that my post was helpful.

---

Please remember to mark the replies as answers if they help Thanks, --Kuldeep