Contents


Create custom reports with BIRT and Rational Team Concert

Use BIRT to access multiple custom work item attributes

Comments

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

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 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
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
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
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
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
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
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
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
Data set listings in Data Explorer for report
Figure 10. Final report display
Final report layout and 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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

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