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