If you want to pass multiple parameter values back to a data source by using query, the following requirements must be satisfied:

• The data source must be supported. For a list see SqlDataSource Component
• The data source cannot be a stored procedure. Telerik Reporting does not support passing a multivalue parameter array to a stored procedure.
• Use an IN clause in the query to specify the parameter.

The Data Source Components are based on ADO.NET and ADO.NET does not allow a collection of values to be passed. The SqlDataSource Component however supports parametrized SQL queries by associating the report parameters 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 a great deal of flexibility on how to supply SQL queries with parameters.

The example below shows the use of an IN keyword in the WHERE clause of a T-SQL statement. More information about the IN keyword or the results returned by this query, see IN (Transact-SQL) .

CopySQL

SELECT        ProductID, Name, Color
FROM            Production.Product
WHERE        ProductID IN (@ProductID)

To experiment with this in a report, add SqlDataSource Component using this query. Create a new report parameter as Value for the @ProductID data source parameter. Set its Multivalue and Visible properties to true, set the Type property to Integer. In AvailableValues, set the DataSource for the parameter to a new SqlDataSource Component with the following query:

CopySQL

SELECT        ProductID, Name
FROM            Production.Product

Note
The report engine rewrites queries for data sources that cannot process parameters as an array. The rewrite logic is triggered when a parameter is defined as multivalue and the database query uses an IN statement to specify the parameter.

In order to have multiple values selected, you should set the Value property of the parameter to an IEnumerable instance containing the desired values. This can be done using the code behind or an expression.

The data source which the Value property expression is evaluated against is the data source of the available values. For example if the AvailableValues data source has a field Name, as a Value you may use an expression like =First(Fields.Name). The same approach may be used to have all available values returned. This is achieved with the help of a simple User Aggregate Functions:

Example

CopyC#

[AggregateFunction(Description = "Collects all values", Name = "AllValues")]
public class AllValuesAggregateFunction : IAggregateFunction
{
    ArrayList values;

    public void Accumulate(object[] values)
    {
        var value = values[0];
        this.values.Add(value);
    }

    public object GetValue()
    {
        return this.values;
    }

    public void Init()
    {
        this.values = new ArrayList();
    }

    public void Merge(IAggregateFunction aggregateFunction)
    {
        var otherFunction = (AllValuesAggregateFunction)aggregateFunction;
        this.values.AddRange(otherFunction.values);
    }
}

CopyVB.NET

<AggregateFunction(Description:="Collects all values", Name:="AllValues")> _
Public Class AllValuesAggregateFunction
    Implements IAggregateFunction
    Private values As ArrayList

    Public Sub Accumulate(values As Object()) Implements IAggregateFunction.Accumulate
        Dim value = values(0)
        Me.values.Add(value)
    End Sub

    Public Function GetValue() As Object Implements IAggregateFunction.GetValue
        Return Me.values
    End Function

    Public Sub Init() Implements IAggregateFunction.Init
        Me.values = New ArrayList()
    End Sub

    Public Sub Merge(aggregateFunction As IAggregateFunction) Implements IAggregateFunction.Merge
        Dim otherFunction = DirectCast(aggregateFunction, AllValuesAggregateFunction)
        Me.values.AddRange(otherFunction.values)
    End Sub
End Class

Other Resources