Related (linked) work item query / report

Question

How do I create a query or a report that would group related (linked) work items? I guess its not possible to do it in query but I have a webservice which takes the output from a query and feeds it to a report where I can group by a common field if any. Seems like this would be not too uncommon a request but I didn't find the solution online.

Update: I found the canned 'Related Work Items' report and there is data in the TfsWarehouse db's Related Work Items table but the report doesn't show any WIs. I selected 'Select All' in all the options before running it.

Answer 1

Hello,

You are right that it is not an uncommon feature and it is a big improvement included in TFS 2010. Both WI query and report support WI hierarchy. It also supports Link Queries. For detail information, please see: http://blogs.msdn.com/bharry/archive/2009/05/18/tfs-2010-work-item-tracking.aspx

For TFS 2008 and 2005, one workaround is using TFS webservice API to get the information. Here is blog with a sample code for this requirement:
http://blogs.msdn.com/noahc/archive/2007/01/26/tfs-work-item-hierarchy.aspx

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

But there is an out-of-the-box 'Related Work Items' report. Problem is that it doesn't work. I checked the TfsWarehouse db's Related Work Items table and the data is there but the report doesn't return any rows.

Btw thanks for providing the workaround info.

Answer 3

Please make sure if the filters are set correctly.

In order to verify if the OLAP data is right, please execute the following MDX query:

SELECT 
{
[Measures].[Related Current Work Items LinkCount]
} ON COLUMNS,
NONEMPTY(
        [Work Item].[System_Id].[System_Id].Members *
        [Related Work Item].[System_Id].[All].Children,
        [Measures].[Related Current Work Items LinkCount]
)
ON ROWS
FROM [Team System]

If it is not same as the data in TfsWarehouse relational db, please follow the instructions about force OLAP cube update and if it doesn't work, rebuild the OLAP cube in the blog: http://blogs.msdn.com/alanh/archive/2008/05/12/some-helpful-administrative-operations.aspx

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

You better can use the T-SQL TfsWarehouse database. For an example how to use the linked work items in a report see a blog post of me: http://www.ewaldhofman.nl/post/2009/05/13/Report-on-Work-breakdown-in-TFS.aspx

---

Ewald - Please remember to mark the replies as answers if they help.


Blog: www.ewaldhofman.nl

Answer 5

You rock!
Although when I copy/pasted the query I got the following error:
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@Level2WIT".

I am going through the query to see what I need to tweak but if you have any suggestion I appreciate it. We are using CMMI template.

Update: I think I might be missing this piece of the puzzle :

"

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

"What do you mean by this?

Answer 6

The linking of the work items is bidirectional, which means (and you see that in the "related work items" report) that work item A is linked to work item B, but also vice versa. So when you create the report, you would see:

WI A
  +- WI B
WI B
  +- WI A

When you want an hierarchy one of the two work items is the parent and the other the child, but you cannot indicate that in the link. So what I did is that the highest level is a Change Request or Requirement (and so on), so when WI A is a Change Request and WI B is a task, then you the query will show you only

WI A
  +- WI B

The types that are valid on the different levels are defined in the @RootWIT and @LevelnWIT.

---

Ewald - Please remember to mark the replies as answers if they help.


Blog: www.ewaldhofman.nl

Answer 7

Yes the above query returns the rows... but this one that comes out the box doesn't:

SELECT
{
    [Measures].[Current Work Item Count],
    [Measures].[Work Item Url]
} ON COLUMNS,
TOPCOUNT( 
    NONEMPTYCROSSJOIN
    (
        [Work Item].[System_Id].[System_Id],
        [Work Item].[System_Title].[System_Title],
        GENERATE(STRTOSET(@AssignedToParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Assigned To].[Person].[Person])),
        GENERATE(STRTOSET(@StateParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[System_State].[System_State])),
        GENERATE(STRTOSET(@PriorityParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority])),
        GENERATE(STRTOSET(@WorkItemTypeParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Work Item].[System_WorkItemType].[System_WorkItemType])),
        [Related Work Item].[System_Id].[System_Id],
        [Related Work Item].[System_Title].[System_Title],
        [Related Assigned To].[Person].[Person],
        [Related Work Item].[System_State].[System_State],
        [Related Work Item].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority],
        GENERATE(STRTOSET(@RelatedWorkItemTypeParam) AS s, DESCENDANTS(s.CURRENTMEMBER,[Related Work Item].[System_WorkItemType].[System_WorkItemType])),
        [Measures].[Current Work Item Count],
        12
    )
    ,500
) ON ROWS
FROM [Team System]
WHERE
(
    STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),
    STRTOSET(@IterationParam),
    STRTOSET(@AreaParam),
    STRTOSET(@ReasonParam),
    STRTOSET(@IssueParam),
    STRTOSET(@ExitCriteriaParam),
    STRTOSET(@FoundInBuildParam),
    STRTOSET(@FixedInBuildParam)
)

Answer 8

P_Shah - have you found an answer to your question?  As Ewald mentions the [Related Current Work Items] table in the Relational Warehouse has the work item to work item links.  The [Current Work Item_TrackingIdLeft/Right) key to the [Current Work Item] __TrackingId column.

If you are generally querying these links you need to know that they are directed acyclic graphs.  So if you are, for example using a CTE, then you need to have a stopping condition to break the recursion.  If you have a process that doesn't require this (such as Ewald's) then you can just do a fixed number of joins to the table.

Answer 9

I modified Ewald's report and it seems to be working. Both the options are way too complicated for getting simple information that I need for the report. I am not a dba nor familiar with analytical services... Didn't really get an answer to why doesn't the 'Related Work Items' canned report work?