none
How to select multiple value in the parameter.

    Question

  • Hi

    I'm a newbie in SSRS reporting, i want to ask if there is a way to select multiple value in one parameter.

    Example.

    The field name is Category. In the Category there are 4 possible value. A, B, C, D

    The pull down menu which was created by the parameter inside the SSRS reporting only can selct either A, B, C, D ..

    Is there a way that the user can select A and B and C .... so on and so forth?

    thanks for the help.

    vierx
    Monday, January 25, 2010 5:22 AM

Answers

  • Yes , it is possible to have a multi value for a single parameter and in the report properties you should enable multi value check box.

    If your data set is an sql query, just doing the above steps won't be enough. You need to write a function to break the multi value parameters. Because when a user selects all the parameters A,B,C,D then the same will be sent back to the query in this fashion 'A,B,C,D' (this will be assumed as a single parameter though there are four values in it).

    Now write a function which accept the array of values and break it into list of values which will insert into some temp table, here is the function which you should use:

    ALTER

    FUNCTION [dbo].[fn_mvp_123]

     

    (@RepParam varchar(4000))

    RETURNS

    @Values TABLE (Param nvarchar(4000))AS

     

    BEGIN

     

    DECLARE @chrind INT, @Delim char(1)

     

    DECLARE @Piece nvarchar(50)

     

    SELECT @chrind = 1

     

    SET @DELIM = ','

     

    WHILE @chrind > 0

     

    BEGIN

     

    SELECT @chrind = CHARINDEX(@Delim,@RepParam)

     

    IF @chrind > 0

     

    SELECT @Piece = LEFT(@RepParam,@chrind - 1)

     

    ELSE

     

    SELECT @Piece = @RepParam

     

    INSERT @Values(Param) VALUES(Cast(@Piece AS varchar))

     

    SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)

     

    IF LEN(@RepParam) = 0

    BREAK

     

    END

     

    RETURN

     

    END

    Now the select query would also change:

    For example

    Select * from Table
    where coumn1 in (select param from [dbo].fn_mvp_123(@ABC))

    Here @ABC is a report parameter which you should pass.

    Hope this helps.


    Raju
    Monday, January 25, 2010 5:30 AM
  • Hi,

    In Report parameter Properties you will have Multiple value option 

    Select the "Allow Multiple Values" checkbox

    Your Query should be

    SELECT * FROM TableName
    WHERE  ',' + @Parameter + ','  LIKE '%,' + ParameterColumnName + ',%'


    Rajesh Jonnalagadda http://www.ggktech.com
    Monday, January 25, 2010 5:30 AM
  • or you can write the SQL query as

    SELECT *
    FROM Table
    WHERE column-name IN (@parameter)

    after ticking the 'allow multiple values' checkbox in the report parameter options.

    this stores the multiple parameter values as an array.  to display the values selected in your report layout, use something like JOIN(@parameter, ", ") as the textbox expression.
    Monday, January 25, 2010 5:52 AM
  • As I have mentioned in my previous post,  create the function in DB which i have given you and write your SP which should look like:

    USE [AdventureWorks]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_Products]    Script Date: 01/24/2010 23:37:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[usp_Products]
     @Category VARCHAR(1000)
    AS

    SELECT
     pc.Name AS 'Category',
     ps.Name AS 'Subcategory',
     p.*
    FROM
     Production.Product p INNER JOIN
     Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID INNER JOIN
     Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
    WHERE
     pc.Name IN in (select param from [dbo].fn_mvp_123(@Category))

    This stroed Prodecudre will work,

    Pass the argurments as


    ups_products 'bikes,chothing'

    In this previous query which you have posted :

    ups_products 'bikes', 'chothing'

    As ups_products only accepts one parameter and you are passing 'bikes', 'chothing' which are two parameters, as a result you are getting this error.

    Simply use the above SP which I have pasted after executing the function.


    Raju
    Monday, January 25, 2010 8:38 AM

All replies

  • Yes , it is possible to have a multi value for a single parameter and in the report properties you should enable multi value check box.

    If your data set is an sql query, just doing the above steps won't be enough. You need to write a function to break the multi value parameters. Because when a user selects all the parameters A,B,C,D then the same will be sent back to the query in this fashion 'A,B,C,D' (this will be assumed as a single parameter though there are four values in it).

    Now write a function which accept the array of values and break it into list of values which will insert into some temp table, here is the function which you should use:

    ALTER

    FUNCTION [dbo].[fn_mvp_123]

     

    (@RepParam varchar(4000))

    RETURNS

    @Values TABLE (Param nvarchar(4000))AS

     

    BEGIN

     

    DECLARE @chrind INT, @Delim char(1)

     

    DECLARE @Piece nvarchar(50)

     

    SELECT @chrind = 1

     

    SET @DELIM = ','

     

    WHILE @chrind > 0

     

    BEGIN

     

    SELECT @chrind = CHARINDEX(@Delim,@RepParam)

     

    IF @chrind > 0

     

    SELECT @Piece = LEFT(@RepParam,@chrind - 1)

     

    ELSE

     

    SELECT @Piece = @RepParam

     

    INSERT @Values(Param) VALUES(Cast(@Piece AS varchar))

     

    SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)

     

    IF LEN(@RepParam) = 0

    BREAK

     

    END

     

    RETURN

     

    END

    Now the select query would also change:

    For example

    Select * from Table
    where coumn1 in (select param from [dbo].fn_mvp_123(@ABC))

    Here @ABC is a report parameter which you should pass.

    Hope this helps.


    Raju
    Monday, January 25, 2010 5:30 AM
  • Hi,

    In Report parameter Properties you will have Multiple value option 

    Select the "Allow Multiple Values" checkbox

    Your Query should be

    SELECT * FROM TableName
    WHERE  ',' + @Parameter + ','  LIKE '%,' + ParameterColumnName + ',%'


    Rajesh Jonnalagadda http://www.ggktech.com
    Monday, January 25, 2010 5:30 AM
  • or you can write the SQL query as

    SELECT *
    FROM Table
    WHERE column-name IN (@parameter)

    after ticking the 'allow multiple values' checkbox in the report parameter options.

    this stores the multiple parameter values as an array.  to display the values selected in your report layout, use something like JOIN(@parameter, ", ") as the textbox expression.
    Monday, January 25, 2010 5:52 AM
  • or you can write the SQL query as

    SELECT *
    FROM Table
    WHERE column-name IN (@parameter)

    after ticking the 'allow multiple values' checkbox in the report parameter options.

    this stores the multiple parameter values as an array.  to display the values selected in your report layout, use something like JOIN(@parameter, ", ") as the textbox expression.


    numbatau,

    i crated a query from adventure works for example.

    STORED PROCEDURES:

    USE [AdventureWorks]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_Products]    Script Date: 01/24/2010 23:37:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[usp_Products]
     @Category VARCHAR(1000)
    AS

    SELECT
     pc.Name AS 'Category',
     ps.Name AS 'Subcategory',
     p.*
    FROM
     Production.Product p INNER JOIN
     Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID INNER JOIN
     Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
    WHERE
     pc.Name IN (@Category)



    I tested the query without using the stored procedures (only: select,from,where) and it works but when i tested it using stored procedures: ups_products 'bikes', 'chothing' --- i received an error value in SSMS.

    ERROR:
    Msg 8144, Level 16, State 2, Procedure usp_Products, Line 0
    Procedure or function usp_Products has too many arguments specified.

    Kindly help... thanks.

    Monday, January 25, 2010 7:52 AM
  • As I have mentioned in my previous post,  create the function in DB which i have given you and write your SP which should look like:

    USE [AdventureWorks]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_Products]    Script Date: 01/24/2010 23:37:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[usp_Products]
     @Category VARCHAR(1000)
    AS

    SELECT
     pc.Name AS 'Category',
     ps.Name AS 'Subcategory',
     p.*
    FROM
     Production.Product p INNER JOIN
     Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID INNER JOIN
     Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
    WHERE
     pc.Name IN in (select param from [dbo].fn_mvp_123(@Category))

    This stroed Prodecudre will work,

    Pass the argurments as


    ups_products 'bikes,chothing'

    In this previous query which you have posted :

    ups_products 'bikes', 'chothing'

    As ups_products only accepts one parameter and you are passing 'bikes', 'chothing' which are two parameters, as a result you are getting this error.

    Simply use the above SP which I have pasted after executing the function.


    Raju
    Monday, January 25, 2010 8:38 AM