Multi Select Parameter Problem

I have a multi-select parameter for a report, but If I select more than one value the report does not return any data. However, if only one value is selected data for that value is returned. I am using the IN option for the SP parameter.

SELECT Code,Description FROM Product

WHERE Code > 0 AND Description IN (@.Description)

I am using SP2 on SQL2005, also I do not have the option of Select-All which I expected to be present.
Any help would be most appreiated.

Thanks

JohnJames

Did you try running the same scenario in SQL query analyser.

A sample data with your executable statements what you have tried in sql server to get the result might help to focus on the issue

|||Hi Raj

Thanks for your reply.
Two values I am selecting are Radio,Television.

These are selected from the Report Viewer.
I have captured values in Temp table and the appear to be correct.

I suspect it is the way SQL Server is interpreting the csv list of values.
|||It does appear to be a problem with the SP.
If I copy the SQL to query designer in the report
the multi-select parameters work fine.
I have written a T-SQL Split function to overcome the problem,
but I am a little surprised that the SP cannot handle the
multi-select parameter.
Is this a problem other developers have come across ?
or is it something wrong I am doing.

JohnJames

Multi Select Parameter in RS2005

I'm using the possibility to define some parameters of my reports having the
property multi-select. On all my reports I always give information on the
first page on the parameters used to generate the output. For parameters that
do not have the property multi-select this works by using expressions
= Parameters!id_status.Value and = Parameters!id_status.Label
where id_status is a parameter
For a multi-select parameter I want to do the same; how ?I guess the new release of RS 2005 does support multi value select. I was
reading FAQ section on Microsoft site, that it is possible to pass multi
value parameters into the report. The way it is implemented is like this.
In a dropdown which lists the values, check all the values and then view the
report. RS constructs the SQL behind the scene.
Hope this helps..
Suresh
"RDC" wrote:
> I'm using the possibility to define some parameters of my reports having the
> property multi-select. On all my reports I always give information on the
> first page on the parameters used to generate the output. For parameters that
> do not have the property multi-select this works by using expressions
> = Parameters!id_status.Value and = Parameters!id_status.Label
> where id_status is a parameter
> For a multi-select parameter I want to do the same; how ?|||I known it works; I want to display the selected values in my report by using
something like
= Parameters!parameter_name.Value and = Parameters!parameter.Label
"Suresh" wrote:
> I guess the new release of RS 2005 does support multi value select. I was
> reading FAQ section on Microsoft site, that it is possible to pass multi
> value parameters into the report. The way it is implemented is like this.
> In a dropdown which lists the values, check all the values and then view the
> report. RS constructs the SQL behind the scene.
> Hope this helps..
> Suresh
> "RDC" wrote:
> > I'm using the possibility to define some parameters of my reports having the
> > property multi-select. On all my reports I always give information on the
> > first page on the parameters used to generate the output. For parameters that
> > do not have the property multi-select this works by using expressions
> > = Parameters!id_status.Value and = Parameters!id_status.Label
> > where id_status is a parameter
> > For a multi-select parameter I want to do the same; how ?|||If you change a report parameter to be multi value, the .Value property will
return an object[] rather than an object. Hence you can no longer e.g. write
expressions like =Parameters!MVP1.Value.ToString().
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"RDC" <RDC@.discussions.microsoft.com> wrote in message
news:4CBB1232-BEC3-4D0F-BC55-F1FD732F3AB8@.microsoft.com...
>I known it works; I want to display the selected values in my report by
>using
> something like
> = Parameters!parameter_name.Value and = Parameters!parameter.Label
> "Suresh" wrote:
>> I guess the new release of RS 2005 does support multi value select. I was
>> reading FAQ section on Microsoft site, that it is possible to pass multi
>> value parameters into the report. The way it is implemented is like this.
>> In a dropdown which lists the values, check all the values and then view
>> the
>> report. RS constructs the SQL behind the scene.
>> Hope this helps..
>> Suresh
>> "RDC" wrote:
>> > I'm using the possibility to define some parameters of my reports
>> > having the
>> > property multi-select. On all my reports I always give information on
>> > the
>> > first page on the parameters used to generate the output. For
>> > parameters that
>> > do not have the property multi-select this works by using expressions
>> > = Parameters!id_status.Value and = Parameters!id_status.Label
>> > where id_status is a parameter
>> > For a multi-select parameter I want to do the same; how ?

Multi select parameter and code module

How do I pass a multi-select parameter to a code module?Depending on what you want to do with it, probably the easiest way to pass
its values is to use the Join() method.
join(Parameters!myMultieValueParam.Value, ",")
"Asher_N" <ashernat@.gmail.com> wrote in message
news:Xns97E875A63AC991203214562@.207.46.248.16...
> How do I pass a multi-select parameter to a code module?|||That worked, Thanks.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in
news:eBAQtOIlGHA.4512@.TK2MSFTNGP04.phx.gbl:
> Depending on what you want to do with it, probably the easiest way to
> pass its values is to use the Join() method.
> join(Parameters!myMultieValueParam.Value, ",")
>
> "Asher_N" <ashernat@.gmail.com> wrote in message
> news:Xns97E875A63AC991203214562@.207.46.248.16...
>> How do I pass a multi-select parameter to a code module?
>
>