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!

Join sorted text code.vb.txt

Comments (3)

  1. Michele says:

    I kow it has been an eternity since you posted this, however, if I can get the code block to work you will have saved my life.  When I copy and paste the code into the code block of the report I get the following error message "There is an error on line 5 of custom code: [BC30456] 'Linq' is not a member of 'System'.  Can you tell me what I need to do to fix this?

  2. Jakub says:

    Did you add correct assembly? (in References below Code section )