Create custom reports with BIRT and Rational Team Concert
Use BIRT to access multiple custom work item attributes
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.
- Display the values of custom attributes.
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:
- Creating custom reports with BIRT and Rational Team Concert: Part 1. Basic reports
- Creating custom reports with BIRT and Rational Team Concert: Part 2. Complex reports and nested data sets
- Creating custom reports with BIRT and Rational Team Concert: Part 3. Advanced reporting techniques
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.
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
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
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
|Data source||Jazz Data Source|
|Data set||Advanced Data Set|
|Column to select||STRING_KEY|
Solution to create a BIRT report for multiple attributes
To create a custom BIRT report for multiple custom attributes, follow these steps:
- Create global variables to store custom attribute data.
- Create separate, advanced data sets for each attribute type (for example, string, integer, and timestamp).
- 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.
- 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.
- 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.
- 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 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
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
Fetch the custom attribute data for the strings advanced data set
- Select the advanced data set for strings that we created and select the Script tab as shown in Figure 4.
Strings[row["WI_ID"] + row["STRING_KEY"]] = row["STRING_VAL"];
Fetch the custom attribute data for the timestamp advanced data set
- Select the advanced data set for timestamps that we created and access the Script tab as shown in Figure 5.
Timestamps[row["WI_ID"] + row["TIMESTAMP_KEY"]] = row["TIMESTAMP_VAL"];
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"
- Create a dynamic text element and add it to your report.
- Select the dynamic text element and select the Binding tab to set some data binding parameters.
- 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
- 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
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:
- Create a final advanced data set named Main.
- Select the appropriate column values. Select the
WI_IDvalue because it is needed for the following steps. Do not select the KEY and VAL attributes for string or timestamp.
- 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.
- 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
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
Figure 10. Final report display
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.
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.
- Read the 3-part article series that serves as a primer to this article:
- More about Rational Team Concert:
- Find Rational Team Concert articles and links to many other resources on IBM developerWorks, and check the product overview page, features and benefits, system requirements, and the user information center.
- Check the Rational Team Concert page on Jazz.net.
- Watch the Using Rational Team Concert in a globally distributed team webcast or a demonstration of the Dashboards and reports, or listen to the podcast about IBM Rational Team Concert and Jazz.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Improve your skills. Check the Rational training and certification catalog, which includes many types of courses on a wide range of topics. You can take some of them anywhere, anytime, and many of the Getting Started ones are free.
- 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.