Create short lists using the LookupSet function
Sometimes you have a short list of values that you really don't want to show on individual rows; you'd rather just concatenate them into a list, separated by commas, and put them all in a single row.
For example, suppose instead of listing the states in each U.S. Census region each on their own row, I'd like to see something more like this:
You can do this in a Reporting Services report in SQL 2008 R2 or later, using the built-in LookupSet and Join functions together, like this:
=Join(LookupSet(thisfield, otherField, returnField, datasetName), delimiter)
Note that the datasetName will be the same as the dataset to which the table is bound. So, for example, in the above case I did the following:
=Join(LookupSet(Fields!Region.Value, Fields!Region.Value, Fields!StateCode.Value, "StateRegions"), ", ")
If the values are not sorted the way you like them, or they include duplicates, you can use the following very similar functions by inserting them into your report's Code block:
=Code.JoinSorted(LookupSet(...), ...)
=Code.JoinDistinctSorted(LookupSet(...), ...)
The code for these functions is attached.
Hope this helps!