Ole DB Multi value named parameters problem.

I'm trying to use cascading report parameters using the mult-value select option. The first parameter is used as input for second parameter.

The first dataset called Employee,
select employeeid from humanresources.employee

Using SQL Server you can use multi named parameters to create your second dataset. For example,
select employeeid, loginid from humanresources.employee where employeeid
in (@.Employee)

However, I'm sadly not using sql server. I'm trying to do the same thing using an OleDB (non SQL Server) interface which doesn't support named parameters. So I used an expression. For example,

="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"
This works in Reporting Services if you do not have multi-value select option checked. Once you check the multi-value select option I get an error creating the second data set.
"Cannot set the command text, Error during process of command text of expression"
How do I do multi-value expressions.

Data providers do not support multi value parameters natively. Consequently, we have chosen to implement query statement rewrite for the managed SQL and the managed Oracle provider. Multi value parameters are also supported for AS 2005 data sources.

The OleDB standard in particular only allows single value parameters. It is not possible to write a "generic" multi value parameter query rewrite algorithm that works with any database server behind an arbitrary OleDB provider.

If you need multi value parameter functionality for a specific OleDB provider, you could write a custom RS data extensions which implements in particular the Microsoft.ReportingServices.DataProcessing.IDataMultiValueParameter interface. Then you could implement the query rewrite yourself in a way that is understood by your target database server.

Hope this helps,
Robert

|||Thanks for the education on Ole DB Providers. The testing I did validates what you have told me.

I'm tyring to do a work around. Is it possible to create a data set expression when multi-value parameters is selected. So I could do an expression query similar to this where I concatanate everything into the in portion of the query?
="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"

I believe Reporting Services would construct the query and would therefor the OleDB provider would not have to support multiple parameters. Thus a work around.

Todd|||If you change a report parameter to be multi value, the .Value property will return an object[] rather than an object. Hence you cannot just use regular string concatenation.

In your particular case, you may want to try the following expression:
="select employeeid, loginid from humanresources.employee where employeeid in (" & Join(Parameters!Employee.value, ", ") & ")"

Below is more information about using multi value parameters in reports. 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