How do I concatenate a single field from multiple rows in BOXI 3.1 WebI?
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?
Jyothirmayee A replied
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.