[SOLVED] Adding comma to separate multiple values from same column

Adding comma to separate multiple values from same column

I have column with values like:

Test
test 1
test 2
test 3
test 4

I am trying to put the values in a report in Microsoft Report Builder, but I don't want it listed in a table. I would like it to be listed like below in a text box:

test 1, test 2, test 3, test 4

Is there an expression I can use? I have looked and tried Join and Split but haven't gotten the results I am looking for.

Solution

You can do this quite easily using LOOKUPSET() to get the values into an array and then using JOIN() to combine them into a string.

=Join(LookupSet(1, 1, Fields!myTestCol.Value, "DataSet1"), ", ")

As we want all the rows from the dataset, I've used 1 in the first 2 arguments as a shortcut as 1 always equals 1 , every row in "DataSet1" will be returned. Then we JOIN() them with ", " as the separator.

Note: "DataSet1" is the case-sensitive name of your dataset. You need to change this to match your dataset name or scope name (i.e. a RowGroup name) as required.