Report Builder - Multi-value enumerations on multiple lines
Hello,
I'm trying to use Report builder to export a large amount of data in a table, and I have several attributes that are multi-value enumerations. I found a post that I can use LISTAGG to keep these in one row and not create rows for each enumeration, but I can't seem to validate this in the custom expression. I get a CRRGW5628E not a recognized built-in function name error.
Any assistance is appreciated.
sstruttmann
5 answers
Hi Shannon,
I do not know exactly what custom expression you tried and what database you are using, but I suppose you tried something like this on DB2.
LISTAGG(T2.LITERAL_NAME,', ') as ENUMERATION
or
LISTAGG(CAST($Requirement:Applicable Subsystem(s) (Custom)$ AS VARCHAR(20000)), ',')
The former one should work fine if the combined enumeration is not too long. Otherwise use latter one to define the length of total enumeration.
Comments
When I try this
LISTAGG(CAST($Requirement:Resulting Mishap (Custom)$ AS VARCHAR(8000)),','),
I get:
CRRGW5628E An com.microsoft.sqlserver.jdbc.SQLServerException error occurred when validating the input SQL string, caused by A processing error "'LISTAGG' is not a recognized built-in function name." occurred.
Am I missing something?
My previous answer is for DB2. It appears you are using SQL Server, and there is no LISTAGG for SQL Server. So you need to search for alternative. Perhaps the link below helps?
https://stackoverflow.com/questions/15477743/listagg-in-sqlserver
This will probably help, but I could use some assistance. I'm afraid I don't have any experience with SQL. How can I go from the query in report builder to using the example in the link? Here is the beginning of my query; I'm struggling with inserting the suggested code.
SELECT DISTINCT T1.REFERENCE_ID,
T4.VAL AS VAL3,
T5.VAL AS VAL2,
T6.VAL AS VAL7,
T7.LITERAL_NAME AS LITERAL_NAME1,
T8.VAL AS VAL1,
T9.LITERAL_NAME AS LITERAL_NAME,
T10.VAL AS VAL5,
T11.VAL AS VAL,
T12.VAL AS VAL6,
T13.VAL AS VAL4
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T2...
For example, below is what I am trying, and it doesn't seem to be recognizing 'for xml path', which seems to be the important part. Why is this so complicated? Seems like a pretty normal thing to want to do.
SELECT DISTINCT T1.REFERENCE_ID,
T4.VAL AS VAL3,
T5.VAL AS VAL2,
T6.VAL AS VAL7,
STUFF((SELECT ',' + T7.LITERAL_NAME
FROM RIDW.VW_REQUIREMENT T1
WHERE T7.REQUIREMENT_ID = T1.REQUIREMENT_ID FOR XML PATH('')
),1,1,'') AS LITERAL_NAME1,
T8.VAL AS VAL1,
Hi Shannon,
Hi,
Hi guys, for a SPARQL endpoint, the following custom expression works for me. It looks at the _Requirement artifact type, and concatenates all of the values in the Verification_Method enumeration:
GROUP_CONCAT(DISTINCT $_Requirement:Verification_Method$; SEPARATOR=", ")
This requires that you also show the _Requirement Id as a column, If the custom expression above is the only attribute which is showing for the _Requirement it seems to have troubles.