This post describes steps on how to concatenate data in multiple rows into a single cell, as a comma separated list.  


I had read a post on this in forumtopics long back, but could not find it when I needed it now.  Hence, I experimented on this for a while based on whatever I remembered and found out the way once again.  So, I thought of jotting it down here before I forget it, and hopefully someone finds it useful when they are searching for a similar requirement!!

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: