IBM Support

Building Custom Expressions using more than one attribute for Data Warehouse in JRS

Troubleshooting


Problem

In IBM Jazz Report Service (JRS), building custom expressions only allows the use of one attribute when many times you would like to use more than one attribute.

Cause

In JRS, you are only allowed the use of one attribute at a time when building custom expressions.

Environment

Specifically for use with Data Warehouse only.  Examples are specific for DB2 SQL, your database may support different SQL syntax.

Resolving The Problem

You can use more than one attribute in a Report Builder custom expression for a Data Warehouse sourced report.  Here are some examples of how it can be done.

Important Note: These examples only applies to Data Warehouse reports. Examples are specific for DB2 SQL, your database may support different SQL syntax.

Example 1:  Average number of days a work item was open.

  1. Build a report with all the attributes you need.  Get it running with expected data output. Open Advanced SQL to see what data attributes are available.  For example, T3.CREATION_DATE in this environment is the creation date of the workitem.  Also, this environment has a specific work item attribute for the Closed Date (in the data warehouse it is T4.VAL).
  2. Create a Custom Expression to add your formula requiring more than one attribute, add 'Closed Date' as your one attribute.  Use other SQL attributes found in Advanced SQL for the other attributes needed in your formula as shown below.  In this example we are using only one other attribute, T3.CREATION_DATE.

    Note: In a custom expression, it does require the use of one attribute, I chose the 'Closed Date' attribute associated with my work item.  Alternatively, I could have picked 'Creation Date' as my custom expression attribute and used T4.VAL ('Closed Date') as the other attribute.  Either way should work in your custom expression.  However, I try to use non-custom attributes as my one required attribute if possible, but I am not sure that it matters.

    The calculation below is the average number of days a workitem was open before it was set to closed.  So the 'Closed Date' minus the 'Creation Date' (T3.CREATION_DATE) provides the number of days a specific workitem was open.  The average function (AVG) takes a sum of all the workitems where the 'Closed Date' has a value (IS NOT NULL), calculates the number of days open, then divides this by the number of workitems in this set.

    For the second Case statement, if the workitem was closed the same day it was open, use 1 as the number of days open.

    AVG(CASE WHEN $Work Item:Closed Date (Custom)$ IS NOT NULL
     THEN CASE WHEN (CAST(DATE($Work Item:Closed Date (Custom)$) - DATE(T3.CREATION_DATE) AS INTEGER)) > 0
     THEN (CAST(DATE($Work Item:Closed Date (Custom)$) - DATE(T3.CREATION_DATE) AS INTEGER))
     ELSE 1 END
    ELSE NULL END)
image-20200417111549-1
Example 2: Using more than two work item attributes in a formula

This formula calculates the average age of an active (not closed and not withdrawn) defect work item type.  When the Closed Date (T4.VAL) and Withdrawn Date (T5.VAL) are NULL, then get the number of days between todays date and the work item creation date.   

AVG(CASE WHEN T4.VAL IS NULL AND T5.VAL IS NULL

      THEN CAST(DATE(current date) -  DATE($Work Item:Creation Date$) AS INTEGER)  ELSE NULL END )

image-20200417111549-2

The Format where the above two custom expressions are used looks like the following

image-20200417111549-3

The Advanced tab showing the SQL Query looks like the following.  Line 3-4 and line 5-9 are the custom expressions as they are inserted into the SQL Query by JRS for this report.

image-20200417111549-4

The report results look like the following

image-20200417111549-5

Another more complex and complete example with the use of multiple attributes in a formula

This report shows the number of tests executions passed and failed as a percentage of total test executions passed or failed for the past 6 weeks.  An example output is as follows

image-20200417111549-6

To create this report

  1. Make sure to set Data Source to Rational Data Warehouse
  2. Choose an artifact of type Test Case Execution Record
  3. For the Trace relationship use the following

image-20200417111549-7

  1. Set the following conditions

image-20200417111549-8

Under Format Results

  1. Add attributes

Test Case Execution Record => Current Result Verdict

Test Case Result => End Date

At this point the Format looks like the following

image-20200417111549-9

The Advanced tab shows the initial SQL Query that currently looks like the following

image-20200417111549-10

  1. Take note of the SQL for End Date and Current Result Verdict found on lines 8 and 9 of the above query, we will use this in the custom expression we are about to build.
  2. Back to the Format tab, select Custom Expression
  3. Start out by selecting the attribute Test Case Execution Record => ID and click on the green box with greater than symbol in it to place the ID attribute into our custom expression

image-20200417111549-11

  1. Using a text editor like Notepad ++, create a valid SQL query such as the following

CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 0 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) / (CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 0 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) + CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 0 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL))

Notice in the above query the use of the Test Execution Record:ID attribute.  Also, note the use of End Date (T2.END_DATE) and Current Result Verdict (T1.CURRENT_RESULT_VERDICT) from the Initial SQL Query.  This equation counts the number of passed TCER’s and divides by the number of passed or failed TCER’s.  You will need to know something about SQL to completely understand this query.

  1. Copy your query from the text editor (or the above query) and paste into the Custom Express field.  Check the box Contains an aggregate expression.  Set Show as to Percentage.
  2. Click Validate to ensure your syntax is correct.
  3. Then Click Add.

image-20200417111549-12

Note: When you add a custom expression for the first time you will see the following message appear, select OK.  This is a warning that some fields will be removed from the output and will be seen when using drill-through from the report output.  This is normal.

image-20200417111549-13

  1.  On the Format tab, rename the Column Label for out first custom express to Week 1 Passed %.  The Format tab will look like the following

image-20200417111549-14

Your Advance tab will look like the following

image-20200417111549-15

In the SQL Query, line 1 contains the complete Custom Express we added.

  1. Create the remaining custom expressions using the corresponding queries (remember, we did Week 1 Passed % above)

Week 1 Failed %

CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 0 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) / (CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 0 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) + CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 0 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL))

Week 2 Passed %

CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 8 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) / (CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 8 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) + CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 8 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL))

Week 2 Failed %

CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 8 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) / (CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 8 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) + CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 8 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL))

Week 3 Passed %

CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 15 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) / (CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 15 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) + CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 15 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL))

Week 3 Failed %

CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 15 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) / (CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 15 ) AND (T1.CURRENT_RESULT_VERDICT = 'Passed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL) + CAST(COUNT(DISTINCT CASE WHEN (DAYS(CURRENT_TIMESTAMP) - DAYS(T2.END_DATE) >= 15 ) AND (T1.CURRENT_RESULT_VERDICT = 'Failed') THEN $Test Case Execution Record:ID$ END ) AS DECIMAL))

  1. The pattern should be obvious at this point.  Repeat for the remaining 3 weeks.

  1. This is a good point to name the report and set the Default visualization to Graph

image-20200417111549-16

  1. Going back to the Format Results tab, change the view from Table to Graph
  2. Set the Y axis to Percent, specify Graph Type as Bar and set colors for each of the bar segments

Note: you cannot repeat the same color for the different bars.  To get around this, find the color code used and then increment the number by 1 and then use this as the same color.  This trick works well so the colors of green or red look all the same, but they are really different.

image-20200417111549-17

  1. Save the report and run to visualize the results.

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTU9C","label":"Jazz Reporting Service"},"ARM Category":[{"code":"a8m0z000000CbLWAA0","label":"Jazz Reporting Service"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
16 October 2020

UID

ibm16194673

Page Feedback