Create custom reports with BIRT and Rational Team Concert

Use BIRT to access multiple custom work item attributes

IBM Rational Team Concert helps software development teams store and organize an enormous amount of information, including work plans and tasks, builds and tests, and source code and other files. IBM Rational Team Concert can also be customized to align with the processes and data gathering requirements of a project team. This article demonstrates how to create custom reports containing data from multiple custom attributes using Rational Team Concert with Eclipse Business Information and Reporting Tools (BIRT).

Share:

Paul Wellman (wellman@ca.ibm.com), Managing Consultant, IBM Global Business Services

Paul Wellman is a Managing Consultant at IBM Global Business Services in Toronto, Canada. He works with IBM Clients and IBM Teams to train, enable, and transform the processes, methods and tools delivery teams use. Paul's main area of expertise is the Rational Collaborative Lifecycle Management platform. Prior to Paul's current role, he worked as a Project Manager for IBM Global Business Services, managing global delivery projects. You can contact Paul at wellman@ca.ibm.com.



Paul Rinaldi (rinaldip@ca.ibm.com), Application Developer, IBM Global Business Services

Paul Rinaldi is an Enablement Consultant at IBM Global Services in Toronto, Canada. He works with IBM teams to enable and train them on the Rational Team Concert platform. He also writes custom BIRT reports for these teams.Prior to Paul’s current role, he worked as a zOS Developer and Team Lead for IBM Global Services. You can contact Paul at rinaldip@ca.ibm.com.



12 November 2013

Also available in Chinese Portuguese

Objectives

After reading this article you will have the knowledge to:

  • Understand how custom attributes are stored in the Rational Team Concert repository.
  • View and locate custom attributes in the Rational Team Concert repository.
  • Create BIRT data sets for each custom attribute data type.
  • Apply JavaScript code to each of the data sets to store custom attributes.
  • Display the values of custom attributes.

Prerequisites

Don't have Rational Team Concert?

Download a trial version of:
Rational Team Concert client and Rational Team Concert server

This article series assumes basic familiarity with reports in Rational Team Concert with BIRT. This includes how to create reports, how to work with data sources and data sets, and how to add tables to a report. These topics are covered in the following articles available on IBM developerWorks. If you are not familiar with reports in Rational Team Concert and BIRT, work through the following articles first:

To follow these articles, you will need a free account on jazz.net and the full or trial versions of Rational Team Concert server and Rational Team Concert client.


Problem statement

Standard and built-in Rational Team Concert attributes are stored in table columns. However, custom Rational Team Concert work item attributes are stored in the data warehouse in generic KEY and VAL columns. This approach is used to accommodate user-defined custom attributes that will exist on the work items. The following example shows how three custom string attributes Phase, Release, and Type, are stored for a defect work item.

Current schema used to store custom attributes

Because Rational Team Concert cannot dynamically create columns in the database for each custom attribute, a generic storage mechanism has been implemented, as shown in Table 1 below. In Table 1, three custom string attributes are added to a work item type. For two specific work items, there are a total of six rows retrieved for the three custom attributes. For each work item, one row is stored for every custom attribute.

Table 1. Example of the current custom attribute schema
WI_IDSTRING_KEYSTRING_VAL
1PhaseDevelopment
1Release2.1
1TypeCode Error
2PhaseTesting
2Release2.5
2TypeBug

Ideal schema to store custom attributes

The ideal table for reporting on custom attributes from Rational Team Concert contains columns for each attribute stored in the database. Table 2 shows the ideal schema and layout for the table.

Table 2. Example of an ideal custom attribute schema
WI_IDPhaseReleaseType
1Development2.1Code Error
2Testing2.5Bug

Location of custom attributes

Table 3 outlines which fields contain the custom attributes from within the data warehouse tables in Rational Team Concert.

Table 3. Location of select custom attribute fields
StringsTimestampsIntegersLong
Data sourceJazz Data Source
SnapshotWORKITEMS_SNAPSHOT
Data setAdvanced Data Set
Table nameLIVE_WORKITEM_CNT
Filter byPROJECT_AREA_NAME
WI_TYPE
Column to selectSTRING_KEY
STRING_VAL
TIMESTAMP_KEY
TIMESTAMP_VAL
INTEGER_KEY
INTEGER_VAL
LONG_KEY
LONG_VAL

Solution to create a BIRT report for multiple attributes

To create a custom BIRT report for multiple custom attributes, follow these steps:

  1. Create global variables to store custom attribute data.
  2. Create separate, advanced data sets for each attribute type (for example, string, integer, and timestamp).
  3. Use JavaScript to capture and store the custom attribute data.
  4. Create a final, main, summary data set to consolidate all of the custom attribute data in a usable format.

Create global variables in the report

After you start a new BIRT report, the first step is to create global variables to store the custom attribute data. For each custom attribute type (strings, integer, timestamp) create a separate global variable.

  1. Create a new BIRT report template and create the necessary data sources to your Rational Team Concert data source, select Report in the BIRT Navigator window.
  2. From the Template view, select the Script tab at the bottom to move into the JavaScript window for the report.
  3. In the script selection drop down, select the initialize category as shown in Figure 1. The code you will write in the initialize section will execute when the report is launched.
  4. Create new objects by attribute type to store the custom attributes. In this example you will create variables for both strings and timestamps. Place the following code in the initialize section as shown in Figure 1:
    Strings = new Object();
    Timestamps = new Object();
Figure 1. Initialize the report with global variables
Create variables for string and timestamps

Create a separate data set for each custom attribute type

Create separate advanced data sets for each of the custom attribute types. In this example you will be report on both strings and timestamp custom attributes. Therefore, you need to create two advanced data sets; one for strings and one for timestamps. The following steps assume you are familiar with creating a data set in BIRT, which is connected to the respective Rational Team Concert database.

Create an advanced data set for strings

To create an advanced data set for the string attribute type, use the following values, which are also shown in Figure 2:

  • Table: LIVE_WORKITEM_CNT
  • Columns to incude: WI_ID, STRING_KEY, STRING_VAL
  • Filter by: PROJECT_AREA_NAME and WI_TYPE
Figure 2. Advanced data set for strings
Edit window to create data set for strings

Advanced data set for timestamps

To create an advanced data set for the timestamp attribute type, use the following values, which are also shown in Figure 3:

  • Table: LIVE_WORKITEM_CNT
  • Columns to inc:lude WI_ID, TIMESTAMP_KEY, TIMESTAMP_VAL
  • Filter by: PROJECT_AREA_NAME and WI_TYPE
Figure 3. Advanced data set for timestamps
Edit window to create data set for timestamps

Use JavaScript to capture and store the custom attribute data

After creating the advanced data sets for the string and timestamp custom attributes, use JavaScript to store the data in the global variables. Within each data set, fetch each custom attribute value and store it in the respective global variable as an array.

Fetch the custom attribute data for the strings advanced data set

  1. Select the advanced data set for strings that we created and select the Script tab as shown in Figure 4.
  2. In the onFetch Script section add the following JavaScript code:
    Strings[row["WI_ID"] + row["STRING_KEY"]] = row["STRING_VAL"];
Figure 4. JavaScript for the strings advanced data set
Create script for onFetch for strings

Fetch the custom attribute data for the timestamp advanced data set

  1. Select the advanced data set for timestamps that we created and access the Script tab as shown in Figure 5.
  2. In the onFetch Script section add the following JavaScript code:
    Timestamps[row["WI_ID"] + row["TIMESTAMP_KEY"]] = row["TIMESTAMP_VAL"];
Figure 5. JavaScript for the timestamp advanced data set
Script for onFetch for timestamps

Result of the JavaScript used to capture and store data

When each row in the advanced data set is fetched, the data is stored in the global one-dimensional array for each of the custom attribute data types. For example, the following information stored in Table 1 is stored in the one-dimensional array with the combination of WI_ID and STRING_KEY as the index value.

Listing 4. Example of a global, one-dimensional array
	Strings [1Phase] = "Development"
	Strings [1Release] = "2.1"
	Strings [1Type] = "Code Error"
	Strings [2Phase] = "Testing"
	Strings [2Release] = "2.5"
	Strings [2Type] = "Bug"

Bind the data to the report to run the JavaScript

To ensure the JavaScript code you created is run when the report displays, you need to bind a dynamic text element to both the report itself and to the advanced data sets.

  1. Create a dynamic text element and add it to your report.
  2. Select the dynamic text element and select the Binding tab to set some data binding parameters.
  3. In the Binding tab, select the strings data set to ensure the strings advanced data set is run when the report is displayed, as shown in Figure 6.
Figure 6. Bind the dynamic text to the report
Binding options for the dynamic text element
  1. Hide the dynamic text element so that it is not displayed on the report. With the dynamic text box selected, access the visibility settings and select the option Hide Element, as shown in Figure 7.
Figure 7. Hide the dynamic text box
Visibility settings for the dynamic text element

Display the values of the custom attributes

We have created two advanced data sets that store the values of the custom attributes. Next, we create a main data set to display the custom attributes in an organized manner. The first step is create computed columns that reference the global array to display the respective custom attributes:

  1. Create a final advanced data set named Main.
  2. Select the appropriate column values. Select the WI_ID value because it is needed for the following steps. Do not select the KEY and VAL attributes for string or timestamp.
  3. Within the main advanced data set, create a computed column. Computed columns are used to display the values of the custom attributes stored in the global arrays.
  4. In the computed column, add the following expression to reference the global array and necessary attribute value:
    Strings[row["WI_ID"] + "<id of custom attribute>"];

In this example, there are three computed columns for the three custom attributes: Phase, Release, and Type, as shown in Figure 8.

Figure 8. Computed columns for strings
Set computed columns properties

Note:
Because the arrays are bound to the report layout, these fields show up blank when you preview the results in the data set. The values will show up correctly when you run the report.

After finishing the data sets, as shown in Figure 8, display the fields from the main data set on the BIRT report. Upload your report to Rational Team Concert and run your report. The custom attributes are displayed in an orderly manner, as shown in Figure 9.

Figure 9. Completed data set
Data set listings in Data Explorer for report
Figure 10. Final report display
Final report layout and display

Summary

The article explained how to capture and display values of custom attributes in an orderly manner. By using this method, you can easily create reports based on custom attributes you add to Rational Team Concert.


Acknowledgements

The authors would like to thank their teammates Amin Eskooch and Karen Steele for their support and guidance. The authors also thank the numerous Rational Jazz community members who offer advice and support on jazz.net and the jazz.net forums.

Resources

Learn

Get products and technologies

  • Download Rational Team Concert from Jazz.net and try it free on up to 10 developers for as long as you want (requires registration). If you'd prefer, you can try it in the sandbox instead, without installing it on your own system.
  • Evaluate IBM software in the way that suits you best: Download it for a trial, try it online, or use it in a cloud environment.

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
ArticleID=952255
ArticleTitle=Create custom reports with BIRT and Rational Team Concert
publish-date=11122013