How to use Multivalue string parameters?

[Lok]

Hi All,

I'm trying to build a report that accepts parameters during run time and the parameter is a String, i.e the user needs to select the Type of Transaction from a list of available types in the database and then I run a query to fetch all the records from the database which belong to the types of transactions selected by the user. When I enable MultiValue property in the Report Parameter Collection Editor I get the following error: "Object must implement IConvertible"
Please help, I need to fix this issue ASAP.
Thanks in advance!

[Peter]

Hi Lok,

Do you use Stored Procedure with MultiValue Report Parameter? If this is the case then the DataSource Components do not support MultiValue Report Parameter for Stored Procedures because it is based on ADO.NET and does not allow you to pass a collection of values (set, array). The SqlDataSource component supports parametrized SQL queries by associating the parameters you add to the Parameters collection with placeholders in the SelectCommand query. Parameter values can be evaluated with any expression which conforms to the common expression syntax supported by the reporting engine. This grants you a great deal of flexibility on how you can supply your SQL queries with parameters. Unfortunately the above approach is not applicable for stored procedures.

As a workaround our suggestion is to set the SqlDataSource parameter to a comma separated string of the values. I have attached a sample report as an example. In order to run it you will need the AdventureWorks database that came with your installation of Telerik Reporting. Additionally you will need GetByProductNames stored procedure:

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetByProductNames]
@Names nvarchar(MAX)
AS
BEGIN
        SELECT p.ProductID, p.Name, p.Color FROM Production.Product AS p WHERE CHARINDEX( ',' + p.Name + ',', ',' + @Names + ',' ) > 0

END

Regards,
Peter
the Telerik team

[Meckie Bello]

Would you have the sample files for VB?

[Steve]

Hello Meckie,

We have added out of the box support for queries for multivalue report parameters (see Using Multivalue Parameters) for our SqlDataSource Component. Stored procedures are still not supported directly as ADO.NET does not support passing a multivalue parameter array to a stored procedure.

To convert the provided project to VB.NET, please use the Telerik Code Converter.

Kind regards,
Steve
the Telerik team