locked
need field that links user story to all child tasks RRS feed

  • Question

  • I am creating some reports and need to understand how the child tasks, bugs, etc. are related to a parent user story.  There must be one field across the tables that provides this relationship...does anyone know what it is?  As an example for what I am doing, I need to get task level completed work to roll up to the user story where I can calculate the costs by client.  Any help is appreciated.
    Monday, October 16, 2017 4:36 PM

All replies

  • They are related through link types.  The tasks would have a parent link type to the user story.  The user story will have a child link type to its children (tasks, bugs).
    Wednesday, October 18, 2017 1:40 AM
  • Hi!

    if you want to use SSRS you can find the report "Requirements Progress" and use it as base. https://docs.microsoft.com/en-us/vsts/report/sql-reports/requirements-progress-report-cmmi

    Wednesday, October 18, 2017 9:56 AM
  • Thanks Brian, I have looked through the tables, specifically FactWorkItemLinkHistory, and still can't find a common connection.  For example, if I pull SourceWorkItemID, TargetWorkItemID, WorkItemLinkHistorySK and WorkItemLinkTypeSK, TFS returns a match for a user story and related task, but the data is also the same for unrelated user stories and tasks.  You can see below where only the System_ID is unique.  Is there any other table and field in all of Tfs_Warehouse which has that unique connection data, or am I approaching this all wrong?

    System_Id WorkItemLinkTypeSK WorkItemLinkHistorySK TeamProjectCollectionSK TargetWorkItemID SourceWorkItemID
    14543 11 442 32 1156 1125
    14671 16 31 32 891 1049
    347147 16 374 32 932 949
    347472 16 374 32 932 949
    347474 16 374 32 932 949
    347478 16 374 32 932 949
    348030 16 374 32 932 949
    349085 16 374 32 932 949
    347658 16 374 32 932 949
    349082 16 374 32 932 949
    349087 16 374 32 932 949

    Wednesday, October 18, 2017 6:19 PM
  • Have you tried joining that table to DimWorkItem and and also DimWorkItemLinkType in addition to FactWorkItemLinkHistory?

    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');


    Wednesday, October 18, 2017 9:54 PM