CODEWITHSUNDEEP
business-objectswebi
Aaron
Aaron

Reputation: 29

Concatenate multiple rows data in single cell in a WEBI report

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

I have a report as follow:

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

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.

Upvotes: 2

Views: 33063

Answers (3)

Aaron
Aaron

Reputation: 29

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)

Upvotes: 1

Enric Naval
Enric Naval

Reputation: 183

I made it work by changing one formula:

[VAR Concat Products] = Previous(Self)+ (If IsNull(Previous(Self)) Then "" Else ", ") +[Products]

(I changed the concatenation order, and added "If" to prevent unnecessary commas)

This breaks in long result sets. Maximum variable length is approx. 5000 characters and the concatenation variables will get filled after a few hundred rows. Try displaying in the table the values of "Concat Products" and "Max Concat Products".

Upvotes: 0

Malvineous
Malvineous

Reputation: 27340

I converted some rows into a string based on this SAP blog post like this:

_Concat_MyField = Previous(Self) + (If IsNull(Previous(Self)) Then "" Else ", ") + [MyField]

Final = Last([_Concat_MyField] ForEach ([MyField]))

The _Concat_MyField variable joins each row with the previous row's value, and the Final variable grabs the last entry, which is the one where all the rows have been joined into the string.

It makes more sense if you put [MyField] and [_Concat_MyField] into the same table in a sample report.

Upvotes: 1

Related Questions