Report Builder - Multi-value enumerations on multiple lines
Hello,
|
5 answers
Hi Shannon,
LISTAGG(T2.LITERAL_NAME,', ') as ENUMERATION
or
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
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?
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.
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.
|
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.
|
Hi All
I just tried a custom expression:
LISTAGG(CAST($Requirement:Apportionment (Custom)$ AS VARCHAR(100)), ',')
and it worked!
|
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.
|
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
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.