It's all about the answers!

Ask a question

Reporting on several multi-select columns at the same time


Davyd Norris (8367) | asked Jul 01 '18, 11:53 p.m.
Hi all,

I'm using Report Builder to build a report containing an artefact with several multi-select attributes, and where the attribute values need to be aggregated into a single comma separated list in one column. I have successfully used LISTAGG to merge the results of each attribute column independently, but the moment I add the second multi-select and LISTAGG, I end up with repeated values in both columns.

For example, let's say I have a single artefact with the first multi-select attribute containing A,B and the second containing F,G,H then when I try to display both attributes in my report I get A,B,A,B,A,B in the first column and F,G,H,F,G,H in the second

I have tried to add the DISTINCT keyword to the LISTAGG function but the validator throws an error. I have also tried to order the aggregated values using within GROUP (ORDER BY ...) and once again it throws an error.

This is the SQL that is generated when two aggregated fields are added (Owner and Mode are the multi-select attributes):

SELECT T1.REFERENCE_ID,
       T1.PRIMARY_TEXT,
       T2.VAL AS VAL,
       LISTAGG(T3.LITERAL_NAME, ',') AS LITERAL_NAME1,
       T4.LITERAL_NAME AS LITERAL_NAME,
       T5.LITERAL_NAME AS LITERAL_NAME2,
       LISTAGG(T6.LITERAL_NAME, ',') AS LITERAL_NAME3
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RICALM.VW_RQRMENT_STRING_EXT T2
ON T2.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T2.NAME='RefID'
  LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T3
  ON T3.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T3.NAME='Owner'
    LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T4
    ON T4.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T4.NAME='Location'
      LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T5
      ON T5.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T5.NAME='Element'
        LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T6
        ON T6.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T6.NAME='Mode'
WHERE T1.PROJECT_ID = 51  AND
(  T1.REQUIREMENT_TYPE = 'MyReq'
) AND
T1.ISSOFTDELETED = 0 AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)GROUP BY T1.REFERENCE_ID,
         T1.PRIMARY_TEXT,
         T2.VAL,
         T4.LITERAL_NAME,
         T5.LITERAL_NAME

Has anybody got two multi-select fields working in the JRS? Any help much appreciated

One answer



permanent link
Davyd Norris (8367) | answered Jul 02 '18, 1:47 a.m.
edited Jul 02 '18, 1:48 a.m.
I ended up solving my own problem - and so thought I would document it here for others.

The solution is back end database dependent, so this will work for DB2 and Oracle but won't work for SQL Server as it doesn't even have the LISTAGG function.

Basically you have to override the SQL Parser validation in order to add a couple of modifiers to the function. In the Calculated Expression dialog, you enter the following for the first multi-select:

/*validate_off{*/ LISTAGG(DISTINCT $MyReq:Owner (Custom)$, ',') within GROUP (ORDER BY $MyReq:Owner (Custom)$) /*}*/

and this for the second multi-select:

/*validate_off{*/ LISTAGG(DISTINCT $MyReq:Mode (Custom)$, ',') within GROUP (ORDER BY $MyReq:Mode (Custom)$) /*}*/

This will give you the correct result

Your answer


Register or to post your answer.