locked
work item query with descriptions RRS feed

  • Question

  • First, let me mention I am aware I ***should*** not query the TfsWorkItemTracking database directly.  Unfortunately I have needs that cannot be met by using the warehouse data; I need to create a simple list of product backlog items (work items) that are currently not assigned to a sprint (iteration), including the product backlog item's title, description, baseline work, and priority.  As I mentioned I do not believe the description is available in the warehouse databases due to it being text, please correct me if I have been mislead.

     

    Now, my question is regarding the WorkItemLongTexts table.  I need to grab the description for a work item and I cannot find anywhere that stores the newest revision of a record in WorkItemLongTexts per entry in the WorkItemsAre table or WorkItemsLatestUsed view.  If there is a way for me to avoid having to calculate the Max(Rev) of each entry in WorkItemLongTexts per WorkItemsLatestUsed please enlighten me.

     

    Here is a query that may help demonstrate what I am after:

    Code Snippet

     

    SELECT

    w.[System.Title],

    W.[System.State],

    W.[Microsoft.VSTS.Scheduling.BaselineWork] AS IdealTeamDays,

    W.[Microsoft.eScrum.Common.Order] AS Priority,

    w.[System.TeamProject],

    w.[System.NodeName] AS TheProduct,

    n.TheNewestRevision,

    t.Words AS Description

    FROM dbo.WorkItemsLatestUsed w

    JOIN (

    SELECT t.id, Max(t.Rev) AS TheNewestRevision

    FROM dbo.WorkItemLongTexts t

    JOIN dbo.WorkItemsLatestUsed w

    ON w.[System.ID] = t.id

    WHERE t.FldId = 52 -- Description field

    AND [System.WorkItemType] = 'eScrum Product Backlog Item'

    AND [System.State] not in ('Complete', 'Deleted')

    AND w.[System.NodeName] = 'reporting'

    GROUP BY

    t.id

    ) n

    ON n.id = w.[System.ID]

    JOIN dbo.WorkItemLongTexts t

    ON t.id = w.[System.ID]

    AND t.Rev = n.TheNewestRevision

    WHERE t.FldId = 52 -- Description field

    AND [System.WorkItemType] = 'eScrum Product Backlog Item'

    AND [System.State] not in ('Complete', 'Deleted')

    AND w.[System.NodeName] = 'reporting'

     

     

    Thanx for your help in advance.
    Wednesday, August 8, 2007 12:29 AM

Answers

  • >> If there is a way for me to avoid having to calculate the Max(Rev) of each entry in WorkItemLongTexts per WorkItemsLatestUsed please enlighten me.
    Sorry, there isn't a place where we store this information.

     

    You may be able to optimize your query by removing one join from it. (Look at the code in italics) You should verify it by testing on your system, but I think it will help.


    SELECT

    w.[System.Title],

    W.[System.State],

    W.[Microsoft.VSTS.Scheduling.BaselineWork] AS IdealTeamDays,

    W.[Microsoft.eScrum.Common.Order] AS Priority,

    w.[System.TeamProject],

    w.[System.NodeName] AS TheProduct,

    n.TheNewestRevision,

    t.Words AS Description

    FROM dbo.WorkItemsLatestUsed w

    JOIN (

    SELECT t.id, Max(t.Rev) AS TheNewestRevision

    FROM dbo.WorkItemLongTexts t

    JOIN dbo.WorkItemsLatestUsed w

    ON w.[System.ID] = t.id

    WHERE t.FldId = 52 -- Description field

    AND [System.WorkItemType] = 'eScrum Product Backlog Item'

    AND [System.State] not in ('Complete', 'Deleted')

    AND w.[System.NodeName] = 'reporting'

    GROUP BY

    t.id

    ) n

    ON n.id = w.[System.ID]

    JOIN dbo.WorkItemLongTexts t

    ON t.id = w.[System.ID]

    AND t.Rev = n.TheNewestRevision

    WHERE t.FldId = 52 -- Description field

    AND [System.WorkItemType] = 'eScrum Product Backlog Item'

    AND [System.State] not in ('Complete', 'Deleted')

    AND w.[System.NodeName] = 'reporting'

     

    Thanks,
    Amit

    Dev-TFS

     

    Thursday, August 9, 2007 10:49 PM

All replies

  • Hi there, You're right that Description cannot be moved into the warehouse since it is PlainText - this is due to size issues. I've moved your query to the WIT forum since they have knowledge of the Work Item Tracking db schema.

     

    Thursday, August 9, 2007 12:57 AM

  • As a workaround i think it is possible to add the description to the warehouse.

    Disclaimer: Never tried it though, and probably wouldn't do it myself.

    Just trying to show you all your options. :-)

    Check this KB article http://support.microsoft.com/kb/921312

    If i remember correctly Conchango's Scrum template (www.scrumfoteamsystem.com) has a product backlog report. Perhaps you can take a look how they did it. (probably didn't included the description, only the title).
    Thursday, August 9, 2007 4:21 PM
  • >> If there is a way for me to avoid having to calculate the Max(Rev) of each entry in WorkItemLongTexts per WorkItemsLatestUsed please enlighten me.
    Sorry, there isn't a place where we store this information.

     

    You may be able to optimize your query by removing one join from it. (Look at the code in italics) You should verify it by testing on your system, but I think it will help.


    SELECT

    w.[System.Title],

    W.[System.State],

    W.[Microsoft.VSTS.Scheduling.BaselineWork] AS IdealTeamDays,

    W.[Microsoft.eScrum.Common.Order] AS Priority,

    w.[System.TeamProject],

    w.[System.NodeName] AS TheProduct,

    n.TheNewestRevision,

    t.Words AS Description

    FROM dbo.WorkItemsLatestUsed w

    JOIN (

    SELECT t.id, Max(t.Rev) AS TheNewestRevision

    FROM dbo.WorkItemLongTexts t

    JOIN dbo.WorkItemsLatestUsed w

    ON w.[System.ID] = t.id

    WHERE t.FldId = 52 -- Description field

    AND [System.WorkItemType] = 'eScrum Product Backlog Item'

    AND [System.State] not in ('Complete', 'Deleted')

    AND w.[System.NodeName] = 'reporting'

    GROUP BY

    t.id

    ) n

    ON n.id = w.[System.ID]

    JOIN dbo.WorkItemLongTexts t

    ON t.id = w.[System.ID]

    AND t.Rev = n.TheNewestRevision

    WHERE t.FldId = 52 -- Description field

    AND [System.WorkItemType] = 'eScrum Product Backlog Item'

    AND [System.State] not in ('Complete', 'Deleted')

    AND w.[System.NodeName] = 'reporting'

     

    Thanks,
    Amit

    Dev-TFS

     

    Thursday, August 9, 2007 10:49 PM