Report Studio Reports with Parent / Child Work Items
Background:
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
Comments
Mike Leipe
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
Jan 14 '15, 3:10 p.m.I learned a lot about joins here.
Mike Leipe
Jan 14 '15, 3:47 p.m.And I may have solved my second issue by "refiltering" the final list on "Planned For".