Hi,
I'm using BOXI 3.1 WebI and have a need to show multiple values of a column in a comma delimited list. I tried using a horizontal table but if the list goes over the right margin it creates a new page instead of wrapping to the next line like we want. I can do what I want in Oracle sql using the WM_CONCAT aggregate function but haven't found a way to do it in WebI reports. Has anyone figured out a way to do this?
Thanks,
John Klein
Adding a link was more helpful...http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html
one should not take credit of what she has not done.. 😊
hi,
What is the structure of your report(rows and columns)?.
Post the screen shot of what you are looking for?
Thanks,
Jothi
Hi
It is fantastic article and a great solution, I have been looking for this for a while – I am happy finally I got it …
In the first place i don't understand why developers find it difficult to include an aggregate function that should combine Text fields in group by (',' or ';' or 'space' or whatever) just like sum function adds up all numeric fields in a group.
The above solution is very thoughtful and works very well, but the last trick in the procedure doesn't always results in the desired output; i don't understand why we should use this trick instead we can reset the concatenate process in each [Line].
[VAR Concat Category] = [Category] +", "+ Previous(Self;([Lines]))
This will rest the concatenate in each [Line], so the last (max) running concatenate could be taken for each LINE without the need for the last variable in the above solution [VAR Category]
Hi John,
This article is really very useful. But I am working on BO 4.1 and the variable [VAR Concat Category] = [Category] +", "+ Previous(Self) is not giving the result the way it should have.
I have also attached the screen shot. Please help me out , as i have to implement this logic in some other report.
This is great for a standard table, but it doesn't work with a cross tab. I need a true aggregate. I've tried using WM_CONCAT() around an object, but Webi thinks it's a regular Dimension and not an Aggregate (even though I've selected it as such). The problem is that it throws it into the GROUP BY and that throws an error. I've tried surrounding that statement with an aggregate function Webi recognizes like MIN/AVG, but then it splits it out into two queries for some reason instead of a single one.
I just need a way to overwrite it and say "no matter what you think, do NOT include this in the GROUP BY". Is that possible?
Add a comment