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

  1. Perform the steps in Step 1. Choose data to start building your report.
  2. 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

  1. On the Format results page, click Custom Expression.
  2. Select the artifact type from the Attributes of list.
  3. Optional: Select the attribute (Data Warehouse) or function and attribute (Lifecycle Query Engine) to start creating the custom expression.
    • From the Choose attributes list, select the attribute for the custom expression that you want to create and click Add Add.
      Note: You can also write the attribute manually in the Custom expression field.
    • For LQE, you can pick a frequently used function from the Choose functions list to help you choose the functions quickly without worrying about the syntax of the query language. If you add one of these functions to the custom expression, you must replace the placeholder text. In the custom expression, position the cursor at the beginning of the placeholder text <Replace with Attribute>, choose an attribute, and click Add; then, delete the placeholder text.
  4. Write the expression manually in the Custom expression field.

    Data Warehouse: If you select the Estimate attribute of a work item, the expression appears as $Work Item:Estimate$. Use this expression for custom calculations. For example, if Estimate is represented in milliseconds and you want to see it in hours, use this syntax: $Work Item:Estimate$ / 3600000

    Lifecycle Query Engine: The following example is a custom expression that uses the dateDiff function for counting the number of defects that took 6 to 10 days to be resolved:
    SUM(IF(lqe_fn:dateDiff('day', $Defect:Creation Date$, $Defect:Close Date$) > 5 && lqe_fn:dateDiff('day', $Defect:Creation Date$, $Defect:Close Date$) <= 10, 1, 0))
    You can show the owner names on all the reports open for more than 10 days with the following expression:
    GROUP_CONCAT(IF(lqe_fn:dateDiff('day', $Defect:Creation Date$, $Defect:Close Date$) > 10, $Defect:Owner$ + ', ', ''))
  5. Optional: Select the Contains an aggregate expression checkbox if your custom expression includes an aggregate expression.
    Note: Aggregate expressions calculate a single value based on a set of values such as SUM, COUNT, AVG, MIN, MAX. For example, counting (COUNT) the work items in a project area. Each database vendor has its own set of aggregate expressions. Check the vendor documentation. If you get an error during query validation, try selecting this checkbox.
  6. Optional: Specify how to display the values. You can use the default String.
  7. Validate your expression.
  8. Click Add to include the column in the report.
  9. Change the column label of the custom expression column.
  10. Optional: Click Refresh to preview the report.
    The custom expression column appears as the last column of the report table.
  11. Perform the steps in Step 3. Optimize the report, Step 4. Name and share the report, and Step 5. Run the report to complete your report.

Results

To see your custom expression in the overall report query, go to the Advanced section. The custom expressions are displayed as expressions in the SELECT clause of the generated query.

For more information, watch the following video:

Note: If you edit and save the query in the Advanced section, you can no longer use the Report Builder interface to make more changes.

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:

Data Warehouse
DAYS(CASE WHEN $Work Item:Resolved Date$ IS NULL THEN CURRENT DATE ELSE $Work Item:Resolved Date$ END) - DAYS($Work Item:Creation Date$)
This expression is included in the generated SQL query, viewable in the Advanced section, as follows:
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 ...
Lifecycle Query Engine
lqe_fn:dateDiff('day', $Work Item:Creation Date$, if(bound($Work Item:Close Date$),$Work Item:Close Date$,now()))
This expression is included in the generated SPARQL query, viewable in the Advanced section, as follows:
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)
}
Note:
When a custom expression is defined in Report Builder, the following namespace is displayed in the generated SPARQL thus allowing the prefix to be used in the custom expression:
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

Following are a couple of examples for turning off validation when creating custom expressions with 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$)) /*}*/