It's all about the answers!

Ask a question

Report Studio Reports with Parent / Child Work Items

Mike Leipe (346) | asked Jan 14 '15, 10:09 a.m.
I hope I'm asking the right place for this one.  I did some Googling without much luck as the question is a hard one to ask of a search engine.  I'm fairly new to RRDI and currently find myself stuck on a couple of problems with a report I'm trying to write.


We have a highly-customized RTC-based bug reporting system.  At the top level we have PR work items, which report problems, and a PR can have multiple CR work items, which request code changes, as children.

The PR contains a "Found In" field and the CR contains a "Planned For" field.

I'm attempting to create a report in RRDI / Report Studio that contains all PRs that have "Found In" set to a certain release or that have child CRs that have their "Planned For" fields set to this same release.

I want to create a summary column chart that graphically depicts the number of such PRs that were opened per week along a timeline, plus a table that lists all of these PRs with specific details of both the PRs and their child CRs.

I'm capturing the parent / child relationships and work items using:

A) a list of PRs, filtered on Request Type
B) a list of Request Relational Links, filtered on the "parentworkworkitem" link type
C) a join between A & B above
D) a list of CRs, filtered on Request Type
E) a join between C & D above

Issue #1:

This gives me a list of all PRs with their child CRs, and this is where my first problem arises:  This implicitly (and understandably filters out all PRs that do not have children!  Recall my goal - I want to include in my report output:
  • all PRs that are "Found In" a given release, regardless of whether they have child CRs or not, and
  • all PRs that have child CRs that are "Planned For" the same release, regardless of what the PR was "Found In"

This is generally working, but, as noted above, I'm not getting the PRs "Found In" the release that do not have children.

Is there a way that I can include both PRs with children and those without children in my report?

Issue #2:

The other problem I'm having is that I'm getting multiple records for the same parent PR when I only want one for each.  This of course means that I'm over-counting in my summary table.  Let me try to explain what I see happening here.

I've got a list of PRs with child CRs linked to them.  I've got PR XYZ that is "Found In" 1.2.3 and it has a child that's "Planned For" 1.2.3, too, so it appears in the list.

However, PR XYZ also has another CR child, one that's "Planned For" 4.5.6.  Because of the way my query is structured, this CR also appears in my list and so I get two entries for PR XYZ, one for the CR that's "Planned For" 1.2.3 and one for the CR that's "Planned For" 4.5.6.

Is there any way to only display the entry for the PR / CR tuple that exactly matches my search criteria rather than every single tuple, regardless of the child CR's "Planned For", which is what I'm doing now?

Thanks in advance for any help, guidance, advice, etc. that anyone can provide!


Mike Leipe commented Jan 14 '15, 3:10 p.m. | edited Jan 14 '15, 3:14 p.m.

I've solved the first part of my problem.  Here's how.

I started by creating two queries.  Both are joins of the PR list query and a query that exposes the parent / child relationships, as per Jackie's link below.

The first query, "PRs with Children", is a simple, default join that returns all of the PRs that have a parent / child relationship with another work item.

The second query, "PRs with No Children", is more complicated.  It uses a left outer join to ensure that even those PRs without a parent relationship with one or more other work items (i.e. without a "match" in the join) are returned.  This gives a list of PRs, some of which have relationship information and some that don't.  I filter on the child work item Request ID being NULL and that leaves me with a list of PRs that do not have children.

I then created a third query that is just a simple union between "PRs with Children" and "PRs with No Children".  This gives me all of the PRs in both lists, with child  information where available.

Mike Leipe commented Jan 14 '15, 3:10 p.m.

I learned a lot about joins here.

Mike Leipe commented Jan 14 '15, 3:47 p.m.

And I may have solved my second issue by "refiltering" the final list on "Planned For".

One answer

permanent link
Jackie Albert (1.6k14946) | answered Jan 14 '15, 12:17 p.m.
You may want to check out this article on reporting on workitem relationship links:

Mike Leipe commented Jan 14 '15, 2:56 p.m.

Thanks, Jackie, that is an exceptionally helpful link.

I've actually figured out how to solve the first part of my problem, in part due to that link.  I'll share my solution once my meetings are done.

Your answer

Register or to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.