Concatenate multiple rows data in single cell in a WEBI report

Question

Need advice for a webi report in BO 4.1 Sorry about the alignment.

I have a report as follow:

The result should look like:

Country	Agent	Customer	Products
Country	Agent	Customer	Products
UK	Mo	Sara	Bag
UK	Adam	Jill	Bag
UK	Tim	Kim	Shoes, Bag
US	John	Sam	Belt, Bag

I have 1 query that pull customers, products and country and another query that pulls Agent and country. I have made Agent as my detail object associated with country. I get following output

Country	Agent	Customer	Products
UK	Mo	Sara	Bag
UK	Adam	Jill	Bag
UK	Tim	Kim	Shoes
UK	Tim	Kim	Bag
US	John	Sam	Belt
US	John	Sam	Bag

I tried to follow instructions from http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html

[VAR Max Products] = Max ([Products] In [Customer])
    
[VAR Concat Products] = [Products]+", "+Previous(Self)
    
[VAR Max Concat Products]= [VAR Concat Products] Where ([Products]=[VAR Max Products])
    
[VAR Product] = If(IsNull(Previous([VAR Max Concat Products]));Substr([VAR Max Concat Products];1;Length([VAR Max Concat Products])-2);Substr([VAR Max Concat Products];1;Pos([VAR Max Concat Products];Previous([VAR Max Concat Products]))-3))

But that give me results like

Country	Agent	Customer	Products	[VAR Product]
UK	Mo	Sara	Bag	Bag
UK	Adam	Jill	Bag
UK	Tim	Kim	Shoes,	Bag, Shoes
UK	Tim	Kim	Bag
US	John	Sam	Belt	Bag, Belt
US	John	Sam	Bag

If I remove the Product Column, the result is as follows:

Country	Agent	Customer	Products	[VAR Product]
UK	Mo	Sara	Bag
UK	Adam	Jill
UK	Tim	Kim
UK	Tim	Kim
US	John	Sam
US	John	Sam

I can hide the Product Column but it still won't show the correct result. Need some advice. Appreciate the help in advance.

Answer 1

I changed following these two variables and it worked fine:

[VAR Concat Products] = Previous(Self)+", "+[Product]

[VAR Product] = Substr([VAR Max Concat Products];3;Length(([VAR Max Concat Products])-2)