SSRS: Working With Multiple Value Parameters

It is generally a good idea when writing reports to provide some space on the report to display the parameter values the user is using to run the report. It makes it a lot easier to deal with helpdesk requests along the lines of "Why's it doing this?" accompanied by a screen shot or a saved report - if you include the parameter values in the report then it should be fairly easy to duplicate it.

The problem comes when you're dealing with Multiple Value Parameters. For example if I look at the General Ledger Accounts we have setup in our ERP System we have around 804. Most users will select a couple, all, or one to report on. How do we make sure that enough meaningful information is displayed to help us debug any issues with the report?

The following function follows some simple rules;

• If the number of selected records is the same as the number of records available in the dataset then display "All",
• If a single record is selected then display it,
• If more than 10 records are selected display "Multiple", and otherwise
• Build a comma-separated list of the selected labels making sure that the last item is separated by ", or " rather than a just a "," (it does this by joining together all the selected items separated by "@@@@@", removing the last item and then adding it back in with the separator)

The code to process these rules is;

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