It's all about the answers!

Ask a question

Report Builder - Multi-value enumerations on multiple lines


0
1
Shannon Struttmann (13211) | asked Jan 19 '18, 1:51 p.m.

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



permanent link
Kenji Sarai (96029) | answered Jan 22 '18, 12:39 a.m.
edited Jan 22 '18, 1:01 a.m.

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
Shannon Struttmann commented Jan 22 '18, 9:00 a.m.

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?


Kenji Sarai commented Jan 22 '18, 9:20 p.m.

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


Shannon Struttmann commented Jan 23 '18, 11:28 a.m.

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...



Shannon Struttmann commented Jan 23 '18, 12:15 p.m.

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,


permanent link
Michael Laurendi (105944) | answered Apr 19 '18, 4:10 p.m.

 Hi Shannon,


Did you ever get your question answered or resolved? I have a similar need. I get SQL errors when trying to edit the query in the Advanced custom expression part of JRS Report Builder. Neither FOR XML PATH nor STRING_AGG commands are recognized. Apparently the sqlserver version I am using is prior to (2012?) the addition of these SQL commands. I welcome an alternative way to concatenate multi-enumeration values onto a single artifact id row in the report without using FOR XML PATH or STRING_AGG or STUFF. Does anyone have any examples? Thanks.


Comments
Shannon Struttmann commented Apr 19 '18, 4:16 p.m.

I have not gotten this working as of yet. I'd also love to see examples if anyone knows how to fix it.


permanent link
Steve Dennehy (4115) | answered Jul 17 '18, 7:54 p.m.

 Hi All

I just tried a custom expression:
LISTAGG(CAST($Requirement:Apportionment (Custom)$ AS VARCHAR(100)), ',')
and it worked!


permanent link
Rohith Kuntumalla (111) | answered Dec 04 '19, 6:32 p.m.
edited Dec 04 '19, 6:39 p.m.

 Hi,


IBM has posted a support article here in regards to this question.

However basically the following expression should work. And it can be validated as well since the order will always stay the same. I can't copy and paste it here because the asterix (*) symbol seems to keep disappearing.




permanent link
Harrison Vince (35115) | answered Dec 12 '19, 4:43 p.m.

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.

Your answer


Register or to post your answer.