In this post, I would like to show you how to display an object value in a single cell separated by a delimiter in a WebI report.
I have a table name called Account in Xtreme database and it has the following values, the requirement is to display all the account names in a single cell in the report.
This requirement can be fulfilled if we have access to Universe Designer in which three following objects needs to be created with the syntax shown, I am using SQL Server as my database.
Column Value Delimiter Actual Object Name – Single Line
1) Create a ColumnValue object as below:
2) Create a Delimiter object as below:
3) Create a Account Name object as below:
This step needs two things, make sure the definition of the object looks like below plus the qualification should be a measure with projection as SUM
Objects qualification and projection
All the ground work is done, now I will use the object in a sample report. Let’s see what it returns ?
The report output is shown below…. voila !!!
That is all for today.
Irfan's World 
This gets the solution what most of the folks are looking for, but more often than not we (as developers) end up having a dissatisfied users as they point out the “comma” at the end. This might not be an issue for most of the users but i have practically faced a dissatisfied user. It would be an icing on the cake if we can get this to work perfectly. In an ideal world i would have loved to have SAP come up with the “Next” function. See if you can get rid of the “,” at the end Irfan.
Comment by Adnan — June 28, 2011 @ 6:56 am
There are different ways to resolve it, After the concatenation of required field is done, In WebI, you can create a local variable to remove the comma that appears as the last character.
v_String=Left([yourdimension];Length([yourdimension])-1)
Use the above variable “v_String” to display the concatenated values without the last comma.
Hope that helps Adnan.
Comment by Irfan — June 28, 2011 @ 8:51 pm
Based on the detail post above, it is a Measure Object we end up with, isnt it? But in your recomendation you are referring to [yourdimension], is this a dimension object. Am i getting it right Or am i missing something here? I have tried your suggestion of defining v_string on the measure object you created in this post and it returns #DATATYPE error. Need some help in getting this fixed.
Our requirement is to display Counties in a state in ascending order but the comma at the end is killing me. We are using XIR2 Webi for this report.
Currently I have defined a measure object of Character datatype at the universe level with the following forumula =COUNTY.COUNTYNAME || ‘, ‘ . When i pull in STATE and County fields into my WEBI report their is a comma at the end of last county of that state. In case of District of Columbia the only county which is Washington, DC also has a comma at the end, obviouslly. How can i fix this? Need some explanation Irfan.
Thanks,
Adnan.
Comment by Adnan — June 29, 2011 @ 2:05 pm
First things first – If you are using my technique then yes it’s qualification is measure object kind but the type is Character, In the WebI report convert that measure to a dimension of string and use LEFT function as I suggested earlier.
Secondly, If you are using COUNTY.COUNTYNAME || ‘, ‘ in your Universe object, then make sure you get the final string in a local variable in the WebI report and again use LEFT function to remove the comma at the end. Hope you will resolve it with the above tips.
Good Luck.
Comment by Irfan — June 29, 2011 @ 4:13 pm
First Option (Following your post): Once i create the v_string variable in the webi report it ends up creating seperate rows for each Account Class ID. I might be doing something wrong. Could you please implement the same in your post? It is lot easier that way. 🙂
Secind Option (COUNTY.COUNTYNAME ||’, ‘ method): Created variable Max = Max([County Name]) then created v_string as suggested earlier { v_string=Left([Max];Length([Max]-1)) }. It returns an error saying “The expression/subexpression at position 19 uses an invalid data type. Specify a valid data type (Error: WIS 10037)”.
The way i see it is that both these methods end up doing the same thing, 1st method does the concatenation at univer level and the second at the report level. Both methods end up returning the same error.
Irfan do you think the forumla listed in my second comment dated “Comment by Adnan — June 28, 2011 @ 3:58 pm ” is correct? Could you please see if a WEBI version of the same formula be generated? My problem with that formula is with the Previous() function. Previous([System];([Account]);1) is this a valid Previous() statement?
Comment by Adnan — July 1, 2011 @ 7:35 am
I couldn’t get this to work at the report level. But implemented the grouping of dimension at the universe level using derived table. It is definatelly not a preety solution, but it works. The worst part is i ended up designing the universe to meet the specific reporting requirement. Thanks Irfan for all your help. Your blog is definatelly a very helpful resource. Keep up the good work.
Allah hafiz.
Comment by Adnan — July 6, 2011 @ 1:46 pm
I am working towards a similar solution. I found this formula which i think is not correct. Could you please quickly glance at it and let me know if i am missing anything? The key in this formula is the Previous Function and i am think that is the problem child. I am trying to implement this in WEBI XIR2.
=[System] + If IsNull(Previous([System];([Account]);1)) Then “” Else “;” + Previous([System];([Account]);1) + If IsNull(Previous([System];([Account]);2)) Then “” Else “;” + Previous([System];([Account]);2)
This is for the first 3 values to be grouped only. It can be extended for higher number of values to be concatenated. Please help.
Comment by Adnan — June 28, 2011 @ 3:58 pm
Hi,
Can you please post the SQL that BO generates for this sample report? I am wondering whether it’s something that can run also in Oracle database or whether it’s something SQL-Server-specific.
Thank you.
Comment by Marek — July 7, 2011 @ 7:49 am
Hi Marek,
Thanks for stopping by. Here is the SQL as you requested:
SELECT
Account.Account_Number,
Account_Type.Account_Type_ID,
( ‘ ‘ )+CAST(( Account.Account_Name ) AS VARCHAR) + ( ‘,’ )
FROM
Account INNER JOIN Account_Type ON (Account_Type.Account_Type_ID=Account.Account_Type_ID)
It is not specific to SQL Server, the same technique should work for Oracle and other databases.
Make sure you delete the report table/block first and drag objects one by one. It should concatenate the string column.
Good Luck.
Irfan
Comment by Irfan — July 8, 2011 @ 9:40 pm
I did this trick at my previous client by concatenation ‘ , ‘ at the end of object select, I got the proper results but some times the values would have repeated so have to end up using database approach.
Thanks,
Zaif
Comment by zaif — July 14, 2011 @ 3:36 pm
Hi, how to use CAST statement in Oracle. i have tried with to_char but it is not working. Here is my universe object @Select(Test\ColumnValue)+to_char(@Select(Test\Multi Show Code))+@Select(Test\Delimiter). does this work in oracle?. Thanks.
Comment by Ram — August 16, 2011 @ 3:26 pm
Sorry for my ignorance, i fixed it. Thanks.
Comment by Ram — August 17, 2011 @ 9:14 am
Only one issue i am seeing is the sorting, it is not sorting the values. is there anyway we can sort the values in the cell?
Comment by Ram — August 17, 2011 @ 11:15 am
How would this work for oracle? I have tired different variations with no luck. When I use cast() function it gives me error saying “missing right parenthesis” for what reason I cannot understand why?
Comment by BO_User — September 15, 2011 @ 8:30 am
I don’t think it should give you a problem in Oracle. You can check this link here
Irfan
Comment by Irfan — September 15, 2011 @ 10:18 pm
In oracle use like VARCHAR(4000), then it is not giving the missing right parenthesis error. Only problem I am seeing in this is solution is Sorting.
Comment by ram — September 16, 2011 @ 8:18 am
question – i followed the steps but then some of the values would be repeated. for example, instead of showing up as value1, value2, value3 in the row, it shows up as value1, value1, value2, value2, value3, value3, value3. what does this mean and how do i fix it?
Comment by Webi_User — June 6, 2012 @ 4:27 pm
Webi_User,
Make sure you applied aggregate function correctly. I provided two separate posts for two different databases, please double check.
Good Luck.
Comment by Irfan — June 6, 2012 @ 4:56 pm
Thanks Irfan.It is so simple.
Comment by ravi — June 28, 2012 @ 10:56 am
Hi Irfan, Great post and I tried to do the same but for me I see the results some time double counting like lets say a customer is part of three committee , the result I get from the above method is 6 committees separated by comma repeating the three committees.
Comment by Narain — September 12, 2012 @ 7:09 am
Hey Narain,
Make sure your SELECT and GROUP BY does not include committee ID. It looks you are bringing unique committee’s and the customer ID may get repeated more than once.
Try to use DISTINCT or use an INLINE VIEW.
Irfan
Comment by Irfan — September 13, 2012 @ 10:15 pm
avoid duplicate rows in the properties of edit query panel and table properties of navigation panel
Comment by ravi — September 14, 2012 @ 7:08 am
In my case, the measure object has two rows, one with value “Mexico” and other null. So if i drag this object in th report it displays “Mexico, Mexico”. how to correct this.
Comment by Pre — May 21, 2013 @ 1:45 pm
That worked great Irfan! Thank you very much.
Comment by Russell Orrell — April 10, 2015 @ 6:27 pm