SSRS: Displaying Values in a "Allow Multiple Values" Parameter as a String

This post provides two simple formulas; the first to provide something you could use in a page header (a summary of the selected values) and the other to provide a complete list of the selected values - something you could use on the final page of a report.

Let's assume you have a fairly simple dataset that contains the years 1980 to 2011. You have a parameter that uses this dataset as the source for it's "Available Values" list and allows the user to multi-select years.

Now to be helpful to your users you echo back to them the parameters they used to run the report in the reports output (always a good idea; gives helpdesk something to work with when the user wants to report a problem!).

The following block of code will convert the list of the users selected years into a English string that can be displayed;

=IIF(
  Count(Fields!KEY.Value, "LIST_YEARS") =
        Parameters!YEARLIST.Count, "All",
    IIF(
       Parameters!YEARLIST.Count = 1,
       Join(Parameters!YEARLIST.Label, ""),
       IIF(
         Parameters!YEARLIST.Count > 10,
         "Multiple",
         Replace(
           Left(
             Join(
               Parameters!YEARLIST.Label, "@@@@@"),
             InStrRev(
               Join(
                 Parameters!YEARLIST.Label,
                   "@@@@@"),
               "@@@@@") - IIF( Parameters!YEARLIST.Count < 2, 0, 1)),
           "@@@@@",
           ", ") + ", or " +
           Parameters!YEARLIST.Label(
             Parameters!YEARLIST.Count-1))))

This assumes your parameter is called YEARLIST and the dataset you are using for lookups is called LIST_YEARS and has the key field KEY.

The output follows a few simple rules; If the user has selected all records in the drop down display "All", if they have selected more than 10 items show "Multiple", otherwise display the items comma-separated and between the final two items replace the comma with ", or ".

Assuming you just want the list of all values (for the report footer) then you can use;

=IIF(Parameters!YEARLIST.Count = 1, Parameters!YEARLIST.Label(0), Replace(
    Left(
        Join(
            Parameters!YEARLIST.Label, "@@@@@"),
            InStrRev(
                Join(
                    Parameters!YEARLIST.Label,
                    "@@@@@"),
                "@@@@@") -
            IIF( Parameters!YEARLIST.Count < 2, 0, 1)),
    "@@@@@",
    ", ") +
    ", or " +
    Parameters!YEARLIST.Label( Parameters!YEARLIST.Count-1))

This will provide you with a complete list of the years selected.