Multiple Selection on Report Parameters

dkaco · July 23rd, 2004, 10:19 AM

Hi,
I need to allow users to do a multiple selection on a list, and that list it's a Report Parameter.

Supposing that parameter that is shown as a list and has the following name:
@NamesList

So then in the dataSet I do the following:

Select name, salary from Clients where name in @NamesList

I will really appreciate if someone could answer this or at least someone please tell me if this it's possible.

Thanks...

AHFMR · August 24th, 2004, 08:18 AM

Did you find out how do a multiple selection list? Before we can move to this new product we must be able to do multiple selection lists.

Thanks for any information

AHFMR

mirela_navodaru · September 8th, 2004, 04:49 AM

hi,

i'm interessted too in the multiple selection on the list in Reporting Services. but i have another problem to add at this: if you make more than one selection how could you use it for generate a table for each one of selections that you had made ( in layout you create one table).

thank you in advance for yours answears.

BE · September 23rd, 2004, 06:13 PM

I Hope these findings help:

Parameter lists do not allow multi-select.
Options for multi-select:
  Change the value of the parameter to a string, and let the user type in a list of values.
  For numeric values, change the query to an expression that incorporates the list into an IN clause. To do this, you must switch to the Generic Query Designer and then convert the query to a string expression that concatenates the parameter with the rest of the query. The folloing example shows how to achieve this:
    ="SELECT * FROM vProduct
      WHERE MonthNumber IN ("+ Parameters!Month.Value +")"

  For string values, create a user-defined function (UDF)
    that can split the multi-select parameter string into an inline table that you can use in an IN clause, or that can be joined with the main view.

When you create a query as a string expression, you must first create and execute the expression as a standard query to allow Report designer to identify the fields in the resulting data set. Then u can convert the expression to a string so that you can concatenate elements.

mlapoint · October 1st, 2004, 11:05 AM

I took what you posted (THANKS!!!) and built a Custom Code function to handle IN Lists. Here is a more detailed way to handle multiple values. I typed this up for another forum but thought I'd share.

Quote:

quote:

Eureka! Ok, I didn't solve the "multiple selctions" thing but I DID figure out how to use IN Lists if someone types in a comma separated list of values into a free text developer.

1) Place this piece of Custom Code to put in the report:

public function InValue(InString as String, _
            Optional InType as String = "S", _
            Optional InDelim as String = ",")

   ' Author: Mike La Pointe
   ' Date: 10/01/2004
   ' Descr: Accepts a delimited value and returns
   ' a quoted delimited value

   Dim newString
   Dim delim = "'"

   if InType = "N" then
      delim = ""
   end if

   newString = delim _
        + join(split(InString,InDelim), delim + "," + delim ) _
         + delim

   return newString

end Function

2) Build a simple query as follows in the Generic Query Builder and execute it to the fields:

select * from <table>

3) Build a parameter (I call it MyParam in this example)

4) Change the query to the following:

="select * from <table> where <field> in ( " + Code.InValue(Parameters!MyParam.Value) + ")"

5) Run the Query:
Comma is the standard delimiter to pass into the query but others can be used (3rd optional parameter)