Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

Report Builder - Multi-value enumerations on multiple lines

Questions
Tags
Users
Badges
Shannon Struttmann
(13●3●11) 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

1

0 votes

5 answers
9,015 views
0 votes

5 answers

Permanent link
Kenji Sarai
(960●3●9) 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. 

0 votes

Comments
Shannon Struttmann
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
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
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
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
(105●18●45) 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.

0 votes

Comments
Shannon Struttmann
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
(41●2●5) 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!

0 votes

Permanent link
Rohith Kuntumalla
(11●1) 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.



0 votes

Permanent link
Harrison Vince
(45●1●17) 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.

0 votes

Your answer

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 12,143

Question asked: Jan 19 '18, 1:51 p.m.

Question was seen: 9,015 times

Last updated: Dec 12 '19, 4:43 p.m.

Confirmation Cancel Confirm