Advanced Report Creation in Jazz Reporting Service 5.0.2
Steven Shaw, Senior Software Developer, Jazz Reporting, IBM Rational
Last updated: [December 03, 2014]
Build basis: Jazz Reporting Service 5.0.2
This article discusses some advanced reporting creation capabilities of the Jazz Reporting Service. Before you begin, you should have some knowledge about basic capabilities of the Jazz Reporting Service. These videos introduce you to how it works: Reporting across CLM projects using the Jazz Reporting Service, Part 1 Reporting across CLM projects using the Jazz Reporting Service, Part 2 Reporting across CLM projects using the Jazz Reporting Service, Part 3 You should have some basic understanding of database concepts and some exposure to Structured Query Language (SQL), which is used to query for information in a relational database. You can find a general introduction to SQL at www.w3schools.com/ sql / . Finally, since the Jazz Reporting Service creates reports from the tools in the IBM Rational solution for Collaborative Lifecycle Management (CLM), it is useful to have some understanding of the different capabilities of the tools and the fundamental data model they manage. CLM tools include the Change and Configuration Management application (Rational Team Concert), the Requirements Management application (DOORS Next Generation), and the Quality Management application Rational Quality Management). You can find an introduction to CLM at http://www-03.ibm.com/software/products/en/ratlclm .
The Jazz Reporting Service is designed for non-technical practitioners who simply want to find information about their software projects and socialize that information in their enterprise through dashboards. There is no assumption of advanced understanding of databases or SQL syntax (even though it’s mentioned in the prerequisites for this article). This user-friendly approach works well for building straightforward reports, but if you want to dive into more complex reports, you might need to use more than the UI framework. This article explains how to go beyond UI limitations to use the advanced functions possible with the report builder, where you can directly modify the generated SQL commands to get additional information from the data warehouse.
To understand how to access the advanced functions, you first need to create a simple report. Open the Jazz Reporting Service, and click Build. You get a wizard-like interface that guides you through the report creation process. In the first step you are asked to choose an artifact as the main focus for the report. For this example, you’ll select Work Item, specifically a Defect, as in Figure 1.
Figure 1. Selecting an artifact type
At this point your report finds all work items in the database that are defects. You can then narrow down the report criteria by adding additional conditions for the work items.
Next, click the Format results tab. Here you can adjust which columns appear in the report table, in what order, and how they should be sorted. For now, scroll down to see the collapsed Advanced section. Click it to expand it to see the generated SQL query. It’s usually a good idea to do as much of the report as possible with the wizard UI and then start with a functional SQL query before you use the advanced SQL capability. The generated query in Figure 2 is based on the current selections in the wizard UI.
Figure 2. The generated SQL query in the Advanced section
Now, take a look at the generated query to understand what it is doing before you try to modify it. A typical SQL query has three main parts: 1. What you select from the data – the set of variables that will become columns in the report are defined in the SELECT part of the query. 2. Where you select the data from – the tables are defined in the FROM part of the query. 3. How you select the data in the table – the set of conditions around which data is retrieved from the table, as defined in the WHERE part of the query. If you have a more complex query that includes table joins, conditions can also be part of the ON statement in the join where you define how the join connects the two tables.
In this query you select the reference ID (T1.REFERENCE_ID), the name (T1.NAME), the URL (T1.URL), and the type of work item (T1.REQUEST_TYPE). However, when you preview this query, you only see three columns.
Figure 3. Preview of the generated query for work item defects
When displaying the report as a table, Jazz Reporting Service automatically combines the Name and URL fields to create a link to the actual resource. The service recognizes a pattern in the variable names, based on the types of the variables. If the URL variable has the URL type and the name variable has the same name as the URL field except with a postfix of “_title”, this triggers combining the columns to create a link. If you prefer to keep them separate, the best way is to change the variable name in the SELECT statement so it doesn’t match the URL variable name.
To see this in action, you modify the query. To put the query into edit mode, click Edit Query in the right corner of the Advanced section. Be aware that when you go to query edit or manual mode, you can no longer return to the full wizard UI. When you put a report into manual mode and save it, the setting is permanent, so it is a good idea to save and duplicate the wizard report beforehand for reference.
After you click Edit Query you can open the Columns section to see that the query is still active. You can still control the relative order of the columns in the report here. You can also set the column headers and the variable type, which can affect the formatting of the result because of the type and sometimes the browser’s localization settings.
Figure 4. The Columns section for a manual query
Now you can verify the pattern for combining the name and the URL in the report. In the Advanced section, change the variable of the name field to “Name1” instead of “URL1_title” and then click Validate Query. Notice that the name and URL fields become separate columns in the report preview.
Column aggregation and totals
Why would you ever need to modify the generated SQL when you have such robust report wizard function? Your organization might need functions that are beyond the generalized wizard options. For example, the wizard currently doesn’t aggregate rows and total columns. The advanced SQL functions can help you with this.
To see this in action, create a report that provides a count and a sum total for all open work items in the database. To start, first use the wizard UI to create a report for all open work items.
First, create the SQL template to start from:
1. In the Choose artifacts section, select Work Item, but don’t select the type unless you only want to aggregate and count a few of the types. 2. Limit the scope if you want, and then go down to the Set conditions section. In the Choose an attribute section, search for Status Group Category. Then in the Choose values step, select a state of Open. 3. Save the condition and click Continue to move to the Format results tab. 4. Here you need to add to the columns the fields you want to count. Since you want to count the number of open types, add a column for the Type attribute. Search for Type in the Add columns section and add it. 5. Open the Advanced section and click Edit Query.
Your query lists all of the work items in the system that are open. To count the number of types, use the SQL COUNT function. It will count the number of duplicate rows and use that sum as the value for the variable in the SELECT statement. The example query now includes a number of unique column values that will be different for each row of the table. If you don’t remove these, no rows will be counted and the count value for the type will be “1”.
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T1.REQUEST_TYPE, T1.REQUEST_STATUS FROM RIDW.VW_REQUEST T1 WHERE ( T1.REQUEST_TYPE = 'Defect' AND T1.REQUEST_STATUS = 'Open' ) AND (T1.ISSOFTDELETED = 0)
The red variable fields in the query will be unique. You need to remove these variables before proceeding. The work item status is also defined as a variable here – since the value is always Open, you should remove the redundant variable as well.
It produces a table that is a long list of work item types with many duplicates. To make it useful you can add an additional variable that does a count of the type as follows:
SELECT DISTINCT T1.REQUEST_TYPE, COUNT(T1.REQUEST_TYPE) AS Request_Type_Count FROM RIDW.VW_REQUEST T1 WHERE ( T1.REQUEST_TYPE = 'Defect' AND T1.REQUEST_STATUS = 'Open' ) AND (T1.ISSOFTDELETED = 0) GROUP BY T1.REQUEST_TYPE
It is also necessary to add a “GROUP BY” construct at the end of the query so that the COUNT function can add consecutive rows to get meaningful values. Click Validate Query to ensure that you’ve entered the query properly and haven’t made any syntax errors.
Afterwards, you can go to the Columns section and adjust the column order and rename the fields be more user-friendly.
Figure 5: Columns section
Finally, if you switch to the Preview section you see that the report is producing the results you want – a total count for each type of work item that is currently open.
Figure 6: Preview of an aggregation report
Nested condition groups
When creating a report it is quite common to organize and separate your conditions based on whether they are optional or required because of other conditions. You might be looking for whether the artifact has a certain characteristic OR whether it has a different characteristic. At the same time it might be critical to have another characteristic all of the time. In cases like this, you might want to nest and group conditions together. For example, a report might include defects that are deferral candidates for a given release by finding all defects that are set with a low priority OR were created more than a year ago.
This is relatively easy to accomplish in the Jazz Reporting Service report builder UI. First you select Work Item / Defect in the Choose artifacts section. Then in the Set conditions section, add conditions for low priority and a creation date of more than a year ago. These conditions will look like this:
But the results won’t satisfy our original report requirements. You wanted to find defects that were low priority OR were older than a year old. You have to use group the conditions. Simply changing the condition type to OR between the priority and the creation date is not adequate, because in a group all the condition types must be the same (either all AND or all OR). The top-level set of conditions is considered to a group itself, so you need to create a new nested group to accomplish this. You want the priority and creation data conditions in their own OR group. To group them, you need to select both conditions – by selecting the check box beside them, and then clicking Group. Now you’ll notice parentheses surrounding those two conditions. This represents a new group, so you can safely change the condition type to OR without affecting the outer condition type. Now the conditions should look like this:
You might now have enough to generate the report to see the results. However, you might want to have more complex criteria for the report. For instance, you might want to exclude the older work items from deferral if they have had lots of traffic (such as comments). So in your example, you might only want to defer defects older than a year that have fewer than three comments. This criterion has to be specific to the creation date condition because in this example you want to defer defects that are low priority, no matter how many comments they have. You need another nested group with the Creation Date condition, however, the UI doesn’t support more than one level of nested groups. You will have to see if the advanced SQL functionality can do this. You don’t have to start from scratch; you can start with what you’ve done so far.
First, add a new condition on the Build page that will be appended to the nested group that you just added. Before adding the condition, select the check box beside the group:
Next, add a condition to make the comment count less than or equal to 3:
Switch to the Advanced section to see and manipulate the generated SQL query because the logic is not what you want in the final report.
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T1.REQUEST_TYPE, T1.REQUEST_PRIORITY, T1.CREATION_DATE, T1.COMMENT_COUNT FROM RIDW.VW_REQUEST T1 WHERE ( T1.REQUEST_TYPE = 'Defect' AND ( T1.REQUEST_PRIORITY = 'Low' OR YEAR(CURRENT_DATE - T1.CREATION_DATE) > 1 OR T1.COMMENT_COUNT <= 3 ) ) AND (T1.ISSOFTDELETED = 0)
You need to combine the Comment Count and Creation Date conditions into a nested AND group so that they are considered together, independent of the priority condition.
In the WHERE clause you can see the conditions you added so far in a fairly intuitive manner. Click Edit Query to adjust the SQL so that the creation date and comment count conditions are enclosed in parentheses and separated by an AND clause, as shown in blue:
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T1.REQUEST_TYPE, T1.REQUEST_PRIORITY, T1.CREATION_DATE, T1.COMMENT_COUNT FROM RIDW.VW_REQUEST T1 WHERE ( T1.REQUEST_TYPE = 'Defect' AND ( T1.REQUEST_PRIORITY = 'Low' OR ( YEAR(CURRENT_DATE - T1.CREATION_DATE) > 1 AND T1.COMMENT_COUNT <= 3 ) ) ) AND (T1.ISSOFTDELETED = 0)
At this point you can adjust the column information for the report, save, and publish.
Traceability between artifact types
One of the main capabilities of the report building workflow is traceability between different types of artifacts. You can navigate through links from a particular artifact type such as requirement to the associated test cases through to the execution records. However, you are limited to tracing only one link type per artifact in the chain. So you can only trace from requirement to test case and from test case to work item, but not directly from requirement to test case and requirement to work item. If you need more than one link type per artifact, you can use the advanced report capability.
For this exercise, you will create a report that traces all requirements that are validated by a test case and might be implemented by a work item. Here again you can take advantage of the generated SQL code from the Build page as a starting point. To clarify what tables are used and how the links are constructed, you'll create two reports and then merge them into a single report using the advanced SQL functions.
In the first report, select requirement as the core artifact to report on. Select any relevant types if you want to narrow down the results. Then in the Traceability links section you can create a relationship from requirement to test case.
Figure 7. Traceability from requirements to test cases
Add any relevant filters in the Set conditions section and continue to the Advanced section in the Format results tab. You can see that it generated the following SQL code (no additional filters were added):
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T2.REFERENCE_ID AS REFERENCE_ID1, T2.NAME AS URL2_title, T2.URL AS URL2 FROM RIDW.VW_REQUIREMENT T1 INNER JOIN RIDW.VW_REQUIREMENT_TESTCASE_LOOKUP LT1 ON T1.REQUIREMENT_ID = LT1.REQUIREMENT_ID INNER JOIN RIDW.VW_TESTCASE T2 ON T2.TESTCASE_ID = LT1.TESTCASE_ID WHERE (T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0)
You want to find where the Requirements table (RIDW.VW_REQUIREMENT) is joined or combined with the Test Case through a lookup table (RIDW.VW_REQUIREMENT_TESTCASE_LOOKUP). This lookup table is the intermediary join between the Requirements table and the Test Case table (RIDW.VW_TESTCASE). There's no further action at this point other than to take note and store the SQL query for future reference.
Now you'll create a report to trace from requirement to work item. Again, select requirement in the Choose artifacts section and then proceed to the Traceability links section. This time you will trace the requirement to the work item and make the traceability link optional.
Figure 8. Traceability of requirements to work items
You can skip ahead to the generated query and notice that it follows a similar pattern to the first report:
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T2.REFERENCE_ID AS REFERENCE_ID1, T2.NAME AS URL2_title, T2.URL AS URL2 FROM RIDW.VW_REQUIREMENT T1 LEFT OUTER JOIN RIDW.VW_REQUIREMENT_REQUEST_LOOKUP LT1 ON T1.REQUIREMENT_ID = LT1.REQUIREMENT_ID LEFT OUTER JOIN RIDW.VW_REQUEST T2 ON T2.REQUEST_ID = LT1.REQUEST_ID WHERE (T1.ISSOFTDELETED = 0)
The requirement and work item are joined again through a lookup table (RIDW.VW_REQUIREMENT_REQUEST_LOOKUP). The difference here is that the join is a LEFT OUTER JOIN instead of an INNER JOIN. The INNER JOIN from the first report combines the tables so that only exact matches with the join condition are kept. This condition ensures that the traceability code only returns results where the requirement has links to test cases. When LEFT OUTER JOIN is used instead, all the results from the left side of the join are returned. Table values from the right side of the join are combined if they exist. For the second report this condition ensures that all requirements are returned, and if they have linked work items, those work items are also returned in the report.
With these two generated queries you can now consider how to merge them into one consolidated query that meets your original goal of reporting on requirements that are validated by test cases and might be implemented by a work item. To combine the two you need to go back to the first report and click Edit Query to put it into manual mode.
Then in the SELECT portion you have to add the variables for the work item that is retrievable from the second report. You need to rename the short table name as T3 because T2 already exists from the first query. Also, you need to make the variable names unique so there are no conflicts. You can see this in the blue text in the query:
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T2.REFERENCE_ID AS REFERENCE_ID1, T2.NAME AS URL2_title, T2.URL AS URL2, T3.REFERENCE_ID AS REFERENCE_ID2, T3.NAME AS URL3_title, T3.URL AS URL3 FROM RIDW.VW_REQUIREMENT T1 INNER JOIN RIDW.VW_REQUIREMENT_TESTCASE_LOOKUP LT1 ON T1.REQUIREMENT_ID = LT1.REQUIREMENT_ID INNER JOIN RIDW.VW_TESTCASE T2 ON T2.TESTCASE_ID = LT1.TESTCASE_ID LEFT OUTER JOIN RIDW.VW_REQUIREMENT_REQUEST_LOOKUP LT2 ON T1.REQUIREMENT_ID = LT2.REQUIREMENT_ID LEFT OUTER JOIN RIDW.VW_REQUEST T3 ON T3.REQUEST_ID = LT2.REQUEST_ID WHERE ((T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0) AND (T1.ISSOFTDELETED = 0))
The next step is modifying the JOIN section. Here again you can copy the JOIN description from the second report after the JOIN from the first report and replace the short table names so that they don't conflict, using LT2 instead of LT1 and T3 instead of T2. This change is shown in the purple text in the SQL code.
Click Validate Query to ensure that you didn't make any syntax errors and then adjust the columns labels to be more user-friendly in the Columns section, and you're done! Now you can preview the report and see traceability for requirements to test cases and also work items.
Figure 9. Traceability report from requirements to test cases and work items
Traceability on particular link types
When creating a traceability report between artifact types in the Jazz Reporting Service, a number of options are available. You can trace between the same artifact type (such as from requirement to requirement), in which case the types of links are usually fully descriptive and intuitive (such as elaborated by). However, when tracing across types of artifacts (such as from requirement to test case), the level of granularity is not specific to the link type. The report builder only lets you report on the fact that there is a link between those artifact types (such as related to test case). If you want to report on a particular link type (if more than one exist between the different artifact types), you need to use the advanced SQL mode.
The way to do this is to use the report builder to create a basic traceability report between the two artifacts you're interested in. For this example, you can create a report between requirement and test case just like the first report in the previous section.
Then open the Advanced section and click Edit Query. Here you need to add an extra column to the query results so that you can see the different link types that exist in your data.
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T2.REFERENCE_ID AS REFERENCE_ID1, T2.NAME AS URL2_title, T2.URL AS URL2, LT1.LINK_TYPE FROM RIDW.VW_REQUIREMENT T1 INNER JOIN RIDW.VW_REQUIREMENT_TESTCASE_LOOKUP LT1 ON T1.REQUIREMENT_ID = LT1.REQUIREMENT_ID INNER JOIN RIDW.VW_TESTCASE T2 ON T2.TESTCASE_ID = LT1.TESTCASE_ID WHERE (T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0)
Add the blue line (LT1) to the query so you can see the different link types that are available in your data. Save the report and click the Run report tab to get some idea of the different link types that are used. As you page through the results, observe the "link_type" column and find the one that interests you. If you notice only one, then this section is not really relevant as you can rely on the traceability in the report builder.
Figure 10. Traceability from requirements to test cases with link types
For this example assume that you see more than one link type and take note of the "com.ibm.clm.validatesRequirement" link type name. Go back to the Advanced section, where you can now modify the query to add a condition to the JOIN. In the SQL code below in blue, add a condition of the JOIN of the lookup table based on the link type name that you captured from the previous result.
SELECT DISTINCT T1.REFERENCE_ID, T1.NAME AS URL1_title, T1.URL AS URL1, T2.REFERENCE_ID AS REFERENCE_ID1, T2.NAME AS URL2_title, T2.URL AS URL2 FROM RIDW.VW_REQUIREMENT T1 INNER JOIN RIDW.VW_REQUIREMENT_TESTCASE_LOOKUP LT1 ON T1.REQUIREMENT_ID = LT1.REQUIREMENT_ID INNER JOIN RIDW.VW_TESTCASE T2 ON T2.TESTCASE_ID = LT1.TESTCASE_ID AND LT1.LINK_TYPE = 'com.ibm.clm.validatesRequirement' WHERE (T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0)
Reporting on custom attributes
A powerful feature of the Collaborative Lifecycle Management (CLM) suite is the ability to customize your artifact to add your own custom attributes. Both Rational Team Concert (RTC) and DOORS Next Generation (DNG) support this concept. In the report builder when you add conditions to the report artifacts they show up in the attributes list with a suffix indicating that they are custom attributes.
If you're looking for a custom attribute that you don't see in the list of condition attributes, there might be several explanations. The set of custom attributes are determined from a query of the data warehouse data to find them all. The data warehouse doesn't store an explicit list of custom attributes anywhere so the list is derived from the available data. If a particular custom attribute has no data, the query can't find that custom attribute type at all.
The workaround for this case is to create a dummy instance of the particular artifact type (work item, requirement, or whatever) and set a value for the custom attribute for it. Then you can delete the artifact (so it doesn't affect real production data). When you run the ETL Data Collection process (synchronization of application data with the data warehouse), the custom attribute should show up as expected in the conditions attribute list.
If you recently added the custom attribute, Jazz Reporting Service might not yet be aware of the modification to the data warehouse metadata. When Jazz Reporting Service starts, it builds a model of the data warehouse metadata and keeps this information in a cache because it is relatively time-consuming to update. In this case you, a Jazz Reporting Service administrator can either restart the Jazz Reporting Service server or use the navigation bar to open the Data Sources tab and open the "Rational Data Warehouse" data source. When you click Refresh in the top right corner, the Jazz Reporting Service updates its cache of data warehouse metadata.
In general you should try to use the Build page to build your reports, without resorting to the Advanced section. Use the SQL functionality if absolutely necessary, because support might be limited. The advanced tips in this article can help overcome some limitations of the existing report builder UI for particular circumstances.
For more information
You might find these references on the data dictionaries and the tables stored in the data warehouse to be valuable when venturing into advanced SQL queries.
- 1. Data reference: CLM data dictionaries
- 2. One of many tutorials on SQL: SQL Tutorial
- 3. SQL reference: IBM DB2 SQL Reference
- 4. Article: Discover cross-project reporting with the IBM Jazz Reporting Service
About the author
Steven R. Shaw is a Senior Software Designer at IBM. He can be contacted at email@example.com.
© Copyright 2014 IBM Canada