Freigeben über

Create short lists using the LookupSet function

  • Artikel

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

  • Anonymous
    June 15, 2013
    Useful article ..
  • Anonymous
    August 12, 2014
    The comment has been removed
  • Anonymous
    August 14, 2014
    Did you add correct assembly? (in References below Code section )