New to Telerik Reporting? Download free 30-day trial

How to use MultiValue Report Parameter in a SQL query

Environment

Product Progress® Telerik® Reporting
Report Item SqlDataSource

Description  

This KB article explains how to use MultiValue Report Parameter in a SQL query.

Solution

A multivalue report parameter's value is evaluated as an array of objects - Using Multivalue Parameters.

  • The value can be used directly in SQL Text commands. For example:

    SELECT * from HumanResources.Department
    WHERE (COALESCE(@SelectedValues,Null) IS NULL) OR DepartmentID IN (@SelectedValues)
    

    On configuring SqlDataSource component with the above Text command, you can map directly the @SelectedValues SQL parameter to a multivalue report parameter - SqlDataSource Wizard (step 4).

The COALESCE function is used as the multivalue parameter cannot be evaluated directly against NULL.

  • The value has to be processed in order to be used in a SQL StoredProcedure command

    For example, from the report, you can pass a string containing the comma-separated values. The SQL parameter expected by the stored procedure can be an nvarchar. The nvarchar value can be split in the SQL query:

USE AdventureWorks
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- FUNCTION TO SPLIT STRINGS.
CREATE FUNCTION splitstring
( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END
GO

-- STORED PROCEDURE.
CREATE PROCEDURE UseMultiValues
    @SelectedValues nvarchar(150) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   select * from HumanResources.Department
   where (@SelectedValues IS NULL) OR DepartmentID IN (select [Name] from dbo.splitstring(@SelectedValues))
END
GO

The above is an example of a stored procedure and a function splitting a string by a given character.

Notes

On configuring the SqlDataSource component, you can join the selected values of the multivalue report parameter into a single string by using the Join built-in function:

= IIf(Parameters.SelectedValues.Value Is Null,
       Null,
       Join(',',Parameters.SelectedValues.Value))

See Also

Using Parameters with the SqlDataSource component

Configuring Stored Procedure with Temporary Tables

In this article