Adding custom expressions to reports
If you are a report manager, and if your data source is configured to allow report managers to edit queries, you might want to add or modify custom expression columns. To show the data in the form that you need in your report, you can add columns that use custom expressions written in the query language of your data source. If you are using Data Warehouse the query language is SQL and if you are using Lifecycle Query Engine the query language is SPARQL.
Before you begin
- Perform the steps in Step 1. Choose data to start building your report.
- Optional: Add attribute and calculated value columns to your report. For more information, see Step 2a. Show the report as a table.
About this task
Custom expression report column values are derived by evaluating an expression that involves at least one attribute of an artifact. You can display combined data from multiple properties of the same artifact and properties of multiple artifacts from the traceability paths in report columns. For example, you can add a column that calculates the difference between two dates or combines multiple fields in string format.
Procedure
Results
For more information, watch the following video:
Example
Add a column to display the number of days a work item is open. This column can be calculated by the difference in days between Creation Date and Resolved Date (Data Warehouse) or Close Date (Lifecycle Query Engine). If the work item is not closed or resolved, resolution date must be set as the current date. The expression to be used is as follows:
DAYS(CASE WHEN $Work Item:Resolved Date$ IS NULL THEN CURRENT DATE ELSE $Work Item:Resolved Date$ END) - DAYS($Work Item:Creation Date$)
SELECT DISTINCT T1.PROJECT_NAME,
T1.REFERENCE_ID,
T1.NAME AS URL1_title,
T1.URL AS URL1,
T1.CREATION_DATE,
T1.RESOLVED_DATE,
DAYS(CASE WHEN T1.RESOLVED_DATE IS NULL THEN CURRENT DATE ELSE T1.RESOLVED_DATE END) - DAYS(T1.CREATION_DATE) AS
CREATION_DATE1
FROM RIDW.VW_REQUEST T1
WHERE ...
lqe_fn:dateDiff('day', $Work Item:Creation Date$, if(bound($Work Item:Close Date$),$Work Item:Close Date$,now()))
PREFIX process: <http://jazz.net/ns/process#>
.
.
SELECT DISTINCT
?rtc_cm_WorkItem1_projectArea
?rtc_cm_WorkItem1_shortId
?rtc_cm_WorkItem1_title
?rtc_cm_WorkItem1
?rtc_cm_WorkItem1_created_CUSTOM
WHERE{
.
.
.
BIND(lqe_fn:dateDiff('day', ?rtc_cm_WorkItem1_created, if(bound(?rtc_cm_WorkItem1_closeDate),?
rtc_cm_WorkItem1_closeDate,now())) as ?rtc_cm_WorkItem1_created_CUSTOM)
}
PREFIX lqe_fn: <http://jazz.net/ns/lqe/function/>
Avoiding custom expression syntax errors
When you use advanced custom expressions based on your business needs, Report Builder might
display syntax errors. You can turn off validation by adding *validate_off{*
before
the custom expression to avoid such errors.
Example
LISTAGG
to merge multiple rows for a single artifact in Report Builder./*validate_off{*/ (LISTAGG($Test Case:A (Custom Category)$, ', ') WITHIN GROUP (ORDER BY $Test Case:A (Custom Category)$)) /*}*/
/*validate_off{*/ (LISTAGG($Approval Task:Approver Name$, ', ') WITHIN GROUP (ORDER BY $Approval Task:Approver Name$)) /*}*/