Custom reporting using work item relationship links

Using Rational Reporting for Development Intelligence or Rational Insight software to report on related work items

If you have adopted the Rational solution for Collaborative Lifecycle Management (CLM), there are two reporting applications that are based on IBM® Cognos® Business Intelligence software to help you produce reports: IBM® Rational® Reporting for Development Intelligence and IBM Rational Insight. This article details a flexible mechanism to use in creating report templates that rely on relationship links between work items

Share:

Mark Prout (mark.prout@uk.ibm.com), IBM Rational Technical Professional, IBM

author photoMark is a member of the IBM Rational technical presales team. He specializes in Jazz technology-based products, along with delivery metrics and analytics.



23 April 2013

Also available in Vietnamese

Prerequisites

  • This technique requires that you have an operational instance of the Rational solution for Collaborative Application Lifecycle (CLM), with at least the Jazz™ Team Server and change and configuration (CCM) applications.
  • You also need an operational instance of either IBM® Rational® Reporting for Development Intelligence or Rational® Insight configured to work with the CLM server and the appropriate data warehouse.
  • Finally, ensure that work items have been saved and that the data warehouse update jobs are completing successfully.

Technique overview

Within the Reporting Data Model (data warehouse) package included with the Rational solution for Collaborative Lifecycle Management (CLM) applications (see Resources for more information), there are multiple subfolders for the Request Area folder in the Operational Data Source (ODS) section:

  • Request Related Requests
  • Request Related Requests (by Relational Link)
  • Request Relational Link

You can use these to interrogate work items, based on their relationships with other work items. Figure 1 shows the location of each of these areas within the default Rational CLM Reporting package.

Figure 1. Location of work item link areas within CLM Reporting package
The 3 folders cited are flagged under Request Area

The most flexible mechanism is provided by the Request Relational Link option, which is the subject of this article.

Figure 2 shows a representation of a work item link. Each link has a Link Type Identifier that is used to determine what the relationship is between the linked head and the tail work items.

Figure 2. Diagram of an inter- work item link
A work item link has a head, a tail and a type

Figure 3 shows a parent-child link between work item 23 (parent) and work item 45 (child).

Figure 3. Example representation of a parent-child link
Parent work item is Head, child is the Tail

Table 1 lists the work item link types that are recorded and the link type identifiers used.

Table 1. Link types and identifiers
Link typesIdentifiers
com.ibm.workitem.linktype.blocksworkitem Blocks
com.ibm.workitem.linktype.duplicateworkitem Duplicate, duplicated by
com.ibm.workitem.linktype.parentworkitem Parent, child
com.ibm.workitem.linktype.relatedworkitem Related
com.ibm.workitem.linktype.resolvesworkitem Resolved by
com.ibm.workitem.linktype.tracksworkitem Contributes to
com.ibm.workitem.linktype.trackedworkitem Tracks

Link representations stored within the data warehouse are exposed through data items within the Request Relational Link query subject (ODS > Request Area > Request Relational Link). This allows the creation of queries that require work item link information. For each of these link types, you can obtain the information shown in Table 2 through query items contained within the query subject.

Table 2. Query items and descriptions
Query ItemDescription
Request Relation ID Identifier within the data warehouse for this work item link
External Key2 Jazz URI of the link
Is Deleted Flag denoting whether the link has been deleted: 0 = no, 1 = yes
Name Jazz link type identifier for the link
Request1 ID data warehouse specific identifier for the work item at the head of the link
Request2 ID data warehouse specific identifier for the work item at the tail of the link
URL URL to the Jazz link object

Those query items in bold text are essential for stitching together related work items and their specific information. The head and tail work items are specified by using their unique data warehouse identifiers. These are different from the Jazz work item identifiers.

Figure 4 shows example work items and their link relationships.

Figure 4. A work item hierarchy example
Diagram, parent and child work item relationships

For this data set example, the Request query subject, indicated by R hereafter (ODS > Request Area > Request), provides the crucial data shown in Figure 5, where Request ID is the data warehouse-specific identifier for the work item, and External Key1 is the actual CLM work item ID.

Figure 5. Request query subject exposes all work items with relationship links
Columns list Request ID and External Key1

The Request Relational Link query subject, shown as RRL hereafter (ODS > Request Area > Request Relational Link), provides the data shown in Figure 6.

Figure 6. Request Relational Link query subject exposes all work item links that exist
Request Relational Link has head and tail ID

By using a Join action, it is possible to find parent work item information (with Join Link being RRL.Request2 ID to R.Request ID). A subsequent Join action adds the child work item information to the query results (with Join Link being RRL.Request1 ID to R.Request ID). This is shown in Figure 7.

Figure 7. Using Join to create a parent-child work item query result
Join Request, Request Relational Link areas

The compressed file in the Download section of this article contains sample reports with details about both of these scenarios. The reports also show how you can report on more than one level of the relationship by using the hierarchy between work items types of Epic, Story, and Task (within the Scrum process template).


Build a report combining parent and child related work items

This section guides you through how to create a report that displays one level of parent-child relationships for work items. To help define the necessary Join actions, the report writing tool used is IBM® Cognos® Report Studio. All data items used are located within the Operational Data Store > Request Area section of the CLM package.

Step 1. Create a query that returns a list of all work items

  1. From the Request query subject, add the following query items to the query:
    • Request ID
    • External Key1
    • Name
  2. Name this query AllWorkItems.

Step 2. Create a query to get all parent links

  1. Create a new query.
  2. From the Request Relational Link query subject, add these query items to the query:
    • Request1 ID
    • Request2 ID
    • Name
  3. Define a detail filter that restricts the returned data set to only links of the parentworkitem type. The expression definition should be similar to this:
    Business View.Request
    Relational Link
    .Name='com.ibm.team.workitem.linktype.parentworkitem'
  4. Rename the query ParentLinks.

Step 3. Use a Join action to create a query with parent work item information

  1. Define a new Join action with contributing queries: AllWorkItems and ParentLinks. Ensure that the link definition is between AllWorkItems > Request ID and ParentLinks > Request2 ID.
  2. Define the output query of the join to include the following data items from the contributing queries:
    • AllWorkItems > External Key 1
    • AllWorkItems > Name
    • ParentLinks > Request1 ID
  3. To assist with distinguishing the purpose of each of these data items, rename as follows:
    • AllWorkItems > External Key 1 to Parent work item ID
    • AllWorkItems > Name to Parent work item Summary
    • ParentLinks > Request1 ID to Child work item Request ID
  4. Rename the join output query to Parent WI Detail + Child Ref ID.

Step 4. Join the child and parent work item details

  1. Define a new Join action with these contributing queries: AllWorkItems and Parent WI Detail + Child Ref ID (WI stands for work item). Ensure that the link definition is between AllWorkItems > Request ID and Parent WI Detail + Child Ref ID > Child Work Item Request ID.
  2. Define the output query of the join to include the following data items from the contributing queries:
    • Parent WI Detail + Child Ref ID > Parent Work Item ID
    • Parent WI Detail + Child Ref ID > Parent Work Item Summary
    • AllWorkItems > External Key 1
    • AllWorkItems > Name
  3. Rename the following data items as indicated:
    • AllWorkItems > External Key 1 to Child Work Item ID
    • AllWorkItems > Name to Child Work Item Summary
  4. The final query structure shown in the Query Explorer work area will resemble Figure 8.
Figure 8. All defined queries and joins
Two joins to create parent-child result set

Step 5. Create a Report page

  1. On the Report page, add a List object.
  2. Modify the Data > Query list property o that it is associated with the Parent and Child WI Details query.
  3. Add the data items from the Parent and Child WI Details query to the List object in the following order:
    1. Parent Work Item ID
    2. Parent Work Item Summary
    3. Child Work Item ID
    4. Child Work Item Summary
  4. Group the contents of the Parent Work Item ID column.
  5. Group the contents of the Parent Work Item Summary column.

Running the report against a data warehouse populated from JKE Banking will result in output similar to that shown in Figure 9.

Figure 9. Report output showing parent and child work item listing
Example of report-generated output

Based on this example, you can extend these steps through additional joins to provide a query result that incorporates additional tiers of work item relationships. You need to know the number of hierarchy tiers that you want to report on and define the report query structure . There is not a mechanism to achieve this recursively.


Download

DescriptionNameSize
Useful example reportsUseful-Example-Reports.zip9KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational, DevOps
ArticleID=877528
ArticleTitle=Custom reporting using work item relationship links
publish-date=04232013