on 10-01-2012 9:09 PM
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
Hi Jothi,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ok john,
Got it.
Below is the example that helps you to concatenate the row values into single column.
Let us take an example from eFashion universe. I am picking [LINES] and [CATEGORY] objects from the Product class for my example:
In the result set, I am restricting the rows (for simplicity) for LINES = Dresses, Jackets and Leather.
For the above data, what needs to be achieved would look something like below:
A method on how this can be achieved at report level in WebI is explained below:
· Create a variable [VAR Max Category] = Max([Category]) In ([Lines]) to find the maximum category, with respect to each Line
· Create a variable [VAR Concat Category] = [Category] +", "+ Previous(Self), to concatenate the category value with it's previous row value
The records would look like this:
· Create a 3rd variable [VAR Max Concat Category] = [VAR Concat Category] Where ([Category]=[VAR Max Category]). This variable basically gives only those rows of [VAR Concat Category], where the value of the category is maximum for that particular Line
On replacing VAR Concat Category with VAR Max Concat Category column in the above screenshot, we get:
I have displayed Category and Var Max Category only for illustration purposes. It is actually not required to be present / displayed in the report. On removing those two columns:
Now, the only pending thing to do is to display those categories that belong to their Lines, against each of their respective Lines. The below formula would do the trick:
· Create a variable [VAR Category] =If(IsNull(Previous([VAR Max Concat Category]));Substr([VAR Max Concat Category];1;Length([VAR Max Concat Category])-2);Substr([VAR Max Concat Category];1;Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))-3))
Basically, the formula checks if the previous value of VAR Max Concat Category is NULL.
If TRUE, then it just displays VAR Max Concat Category.
In the above screenshot, this scenario occurs for LINE = Dresses. There is no value for VAR Max Concat Category prior to LINE = Dresses and hence it is NULL.
If FALSE, then previous row value of VAR Max Concat Category is removed from the present row value of VAR Max Concat Category.
This scenario occurs for LINE = Jackets and LINE = Leather (and all the rows that may occur below). This is done with the help of POS (for finding the position where the previous value of VAR Max Concat Category starts), and SUBSTR functions
This is the final output what we intended to achieve at the beginning of this post:
Somehow not able to insert any images. so only go with text pls.
Try this.. It should work.
Thanks,
Jothi
This solution doesn't seem to work in 4.0.
It seems to fail at 'var Concat Category'
If you don't specify your 'Line' <> the previous 'Line', it will include Lines fro the Previous Category,
Once that's handled, nothing is returned if there is only 1 value. That can be handled also, but the last bit, I haven't found a way around. Each occurrence of 'var Concat Category' only returns the last previous and the current... so if I have 4 values only 2 are ever displayed,
Has anyone managed to replicate this solution id 4.x?
Jody
Hello Jyothirmayee,
Great work. Appreciated.
I am trying to do it in reverse.
The output looks like below
Column | aaaaa;bbbbb;ccccc;ddd;eeeeeee;ffffff
I want to display the same column like below
Collumn
------------
aaaaaa
-----------
bbbbb
----------
ccccc
---------
ddd
----------
eeeeeee
------------
ffffffffffffff
Can you please provide me the solution if any
Thanks in Advance,
Sukumar
Hi Experts,
i am using BI 4.1 SP 05
The database is sql server 2008
i am trying to achieve the same functionality,
i followed all the steps you mentioned above but i end up getting only first row where the desired concatenated filed is concatenated but for the rest of the rows is get #UNAVILABLE error.
See the screenshot for more information.
Please let me know if you need anymore information.
Thanks in advance for the help.
Athar
HI Krupa,
DId you get solution to your query.
even I am facing similar issue.
Can one one let me know the solution for this.
Converting single row to multiple rows
Ex: Not assigned; domestic; commercial
Output
Not assigned
Domestic
Commercial
after etting the data in multiple rows I should assign keys to them-
If(value= Not assigned) then "123"
Else if (value = Domestic) then "234" etc etc
Thanks
For those following this thread, please support:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
use ReportFilter([State])
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
i did something in my previous project , i dont really remember , but i do have the code with me , its like 4 or 5 variables u have to make . I am just copying and pasting over here. May be helpful to you , so they are two way
WAY 1 :
Var_Ctegory C9
=If(IsNull(Previous([Var_ Max concatnate C9]));Substr([Var_ Max concatnate C9];1;Length([Var_ Max concatnate C9])-2);Substr([Var_ Max concatnate C9];1;Pos([Var_ Max concatnate C9];Previous([Var_ Max concatnate C9]))-3))
Var_ Max concatnate C9
=[Var concate Project C9] Where ([Project Concat-9] =[Var_Max_Project C9])
Var_Max_Project C9
=Max([Project Concat-9]) In([Country Name])
Var concate Project C9
=[Project Concat-9]+", "+ Previous(Self)
Project Concat-9
=([Country (Final Global ICF Available)].[Project Number] )Where (Not(IsNull([Country (Final Global ICF Available)].[Final Global Icf Avail Ac])) And Not(IsNull([Var_Country (Final Global ICF Available).First Site Initiated Ac])))
OR
WAY 2:
Var_Ctegory 8 NEW
=If(IsNull(Previous([Var_Ctegory 8]));Substr([Var_Ctegory 8];1;Length([Var_Ctegory 8])-2);Substr([Var_Ctegory 8];1;Pos([Var_Ctegory 8];Previous([Var_Ctegory 8]))-3))
Var_Ctegory 8
=Substr([Var_ Max concatnate 8];3;Length([Var_ Max concatnate 8])-2)
Var_ Max concatnate 8
=[Var concate Project 8] Where ([Project Concat-F] =[Var_Max_Project 8])
Var_Max_Project 8
=Max(([Project Concat-8])) In([Country Name])
Var concate Project 8
=[Project Concat-8] +", "+ Previous(Self)
Project Concat-8
=If Not(IsNull([Var_Final Protocol Available])) And Not(IsNull([Country (Final Protocol Available) (1)].[1st Site Selected - Actual]) )Then[Project Number]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Hari,
Use the below link. This may be helpful.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vinod,
Since Category field is part of the table/report you wouldn't see the desired output; Please remove the [Category] field from your table and you will get the output you excpected.
If you want to keep the [Category] then you can read my swuggestion here;
In my previous comment i made a little modification to the solution suggested in this article in odered to get an easier solution.
I put everything here to avoid confussion with other good solutions given above;
[VAR Max Category] = Max([Category]) In ([Lines])
[VAR Concat Category] = If IsNull(Previous(Self;([Lines])))Then [Category] Else [Category]+", "+Previous(Self;([Lines]))
This will prevent concatenating Category fields from other [Lines] and ignor null values hence you don’t need the trick of [VAR Category] as suggested in this article; you need to modify [VAR MaxConcat Category] as follows;
[VAR MaxConcat Category]= [VAR Concat Category] where ([Category]= [VAR Max Category])
The Qualification of this variable should be ‘detail’ of the [Lines]; in the variable editor window set the qualification of this variable to 'detail' and the associated dimension to [Lines].
The above three steps are pretty much working from me in all situations; another advantage of making the last variable a 'detail' of [Lines] is you can use the variable in any report where [lines] is used. This just a little work around on the solution provided by Jothi and all creadit should be given to the orginal solution.
Thanks,
Hailu
for me its not working , i have attached the screen shot , please advise
Project | Var_Max_Project 8 | Var Project Concat | Var_ Max concatnate 8 |
---|---|---|---|
105569 | 206415 | 105569, | |
206361 | 206415 | 206361, | |
206415 | 206415 | 206415, | |
206415 | , | , , , 206415, 206361, 105569, |
Hello Hailu,
Thanks for this post. It really helped me, see the example below:
However, I do got one last issue with this.
I want to count the number of customers that took a certain path (see the table example from below):
I don't know how to reach this solution in BO4, because the computation in BO4 is based on the previous row values and a concatenation. Next to that, I don't think BO4 understands that certain Routes are the same and it therefore cannot count the number of routes.
Does anyone knows a solution to this?
Hello! Tim
If I understand you correctly, you need to make a path base 'Brand' and 'Date' which are the two fields you want to control your Routes.
I can’t see any easier solution than creating a new variable by combining these two fields; then you can apply the Technique in this discussion to concatenate the new variable but don’t forget to remove the date part from the new variable… again since the new variable will have a text date type, if your Date data involves different years then you may need to format the date to achieve proper sorting based on Date (like "yyyy/MM/dd")..
I hope the steps that follow will give you the output you are looking for..
Create a New Variable
Route=FormatDate([Date];"yyyy/MM/dd")+[Brand]
While concatenating the rows you can remove the date (first 10 chars from Route to
Max_Route = Max([Route]) In ([Customer Name])
Concat_Route = If IsNull(Previous(Self;([Customer Name])))Then Right([Route];Length([Route])-10) Else Right([Route];Length([Route])-10)+", "+Previous(Self;([Customer Name]))
then your Routes will be
Routes=[Concat_Route] where ([Route]= [Max_Route]) which is detail of the [Customer Name].
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]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jothi, Having trouble adding an image - what formats does this forum take?
Thanks,
John
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi John,
You may also try to use these option to format the cells :
In the Properties pane: check "autofit width" and "autofit height" and then "wrap text" boxes .
I hope it helps.
Fadoua
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi,
What is the structure of your report(rows and columns)?.
Post the screen shot of what you are looking for?
Thanks,
Jothi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jothi,
See the Countries box below. The problem is the countries come from multiple records (shown lower down under Variable Text) and I haven't figured out how to aggregate the countries all into a single string. If they were in a single string I could use autofit height. Alternatively, I tried horizontal tables but could not figure out how to get them to wrap.
Thanks,
John
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.