IBM Support

How to use a custom expression with LISTAGG to merge multiple rows for a single artifact in Report Builder

How To


Summary

Report Builder by default will return multiple rows for a single artifact if a column is used in the report's display with multiple values. An example of this is a test case with one or more multi-valued category values or a work item with multiple approvers/approval groups.

Objective

A custom expression with LISTAGG on the attribute can be used to concatenate the values of the column so that the Report Builder returns items on a single row.

Environment

Tested with Jazz Reporting Service 6.0.6 and later

Steps

The use of LISTAGG is dependent on the database vendor. These steps are for DB2, but modification should be possible to support Oracle and SQL Server's implementation of LISTAGG.
The following is output from a Rational Quality Manager (RQM) test case report that displays a muti-value category A with values A1, A2. The test case ID 379 is returned on 2 rows, 1 row for value A1 and the 2nd row for value A2.
multvalue by default
To concatenate the values, a custom expression can be added to the multi-value category attribute in Report Builder by clicking "Custom Expression" in the "Format Results" step.
  1. Under Format Results click "Custom Expression"
  2. Chose the Attribute, in this example, the multi-value test case category "A"
  3. The attribute in this example, a multi-value test case category "A" is $Test Case:A (Custom Category)$ in the custom expression editor of Report Builder
  4. Modify that as: /*validate_off{*/ (LISTAGG($Test Case:A (Custom Category)$, ', ') WITHIN GROUP (ORDER BY $Test Case:A (Custom Category)$)) /*}*/
Here is the custom expression added in Report Builder:
from report builder
Here is the output of the report in Report Builder with the LISTAGG custom expression (values A1, A2 are concatenated on a single row):
report output with custom expression
As another example, here is the same LISTAGG  custom expression for Approver Name on Approval Task on a work item artifact.  The attribute is $Approval Task:Approver Name$ and the LISTAGG custom expression is
/*validate_off{*/ (LISTAGG($Approval Task:Approver Name$, ', ') WITHIN GROUP (ORDER BY $Approval Task:Approver Name$)) /*}*/
approver name listagg

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTU9C","label":"Jazz Reporting Service"},"Component":"","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"6.0.6 and later","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
05 April 2019

UID

ibm10879997

Page Feedback