none
SSRS Parameter TEXT with Allow Multiple Values.

    Question

  • My input parameter is of type TEXT with the option allowed for "Allow multiple values".

    I noticed the user inputs a list of items, and the program automatically separates the values with single quotes and commas.

    Entered:

    Bob
    Tom
    Chris

    Variable result:

    'Bob','Tom','Chris'

    ...then SSRS just takes that variable result and sticks it into my query where the @Input location is.

    However, say the @Input is being passed to Replace() or any function which accepts strings/varchar.  Now SSRS will pass it like such:

    Coded:

    REPLACE(@Input, 'what-to-replace', 'the-replacement')

    SSRS result:

    REPLACE('Bob','Tom','Chris', 'what-to-replace', 'the-replacement')

    ...which makes an error because that is 5 parameters for the 3 parameter function REPLACE().

    The question:

    How can I pass the SSRS multi-value TEXT input into a function like REPLACE()?

    My code that works in SQL but not SSRS:

    DECLARE @InputList VARCHAR(255)
    SET @InputList = '''Bob'',''Tom'',''Chris'''

    --BEGIN SSRS CODE HERE

    SELECT InputTable.InputList.value('.', 'VARCHAR(100)') AS 'Result'
    FROM
    (
    SELECT CAST('<M>' + REPLACE(@InputList, ',', '</M><M>') + '</M>' AS XML) AS XMLList
    ) table1
    CROSS APPLY XMLList.nodes('/M') AS InputTable(InputList)

    What this code accomplishes:

    It takes a single VARCHAR(255) that is comma-delimited, such as:  'Bob','Tom','Chris'

    And results each in different rows such as:

    'Bob'
    'Tom'
    'Chris'


    Friday, August 31, 2012 3:21 PM

Answers

  • I didn't notice that in the Dataset's properties, you can input logic on how to format input variables.

    So in my Dataset's properties, I set the @Input to have this:  =Join(Parameters!InputList.Value, ",")

    Now the list of values, when passed to the function are in the form of

    'bob,tom,chris'

    rather than

    'bob','tom','chris'

    so it now works fine.

    Thanks for your help.

    Thursday, September 06, 2012 12:45 PM

All replies

  • Hi,

    Why don't you use Filters in SSRS dataset?

    You can figure it out easily and rapidly by using Filters in your dataset and select IN as operator and [@Input] as Value.

    This will cause to filter your query and only return the values that you have been filtered by your multi-value parameter.

    I suggest you take a look at the thread below:

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/ssrs_multi_value_parameters

    Please share the results.

    Cheers


    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

    • Proposed as answer by SoheilBakhshi Thursday, September 06, 2012 9:30 PM
    Monday, September 03, 2012 12:27 AM
  • I didn't notice that in the Dataset's properties, you can input logic on how to format input variables.

    So in my Dataset's properties, I set the @Input to have this:  =Join(Parameters!InputList.Value, ",")

    Now the list of values, when passed to the function are in the form of

    'bob,tom,chris'

    rather than

    'bob','tom','chris'

    so it now works fine.

    Thanks for your help.

    Thursday, September 06, 2012 12:45 PM
  • Hi,

    can you please try below things for this first you will have to create a split function which will split the comma separated values. Given below is the code for that split function just create it.

    Create FUNCTION [dbo].[fn_Split]      
    (      
    @ItemList NVARCHAR(Max),      
    @delimiter CHAR(1)      
    )      
    RETURNS @IDTable TABLE (Item VARCHAR(50))      
    AS      
    BEGIN      
    DECLARE @tempItemList NVARCHAR(Max)      
    SET @tempItemList = @ItemList      

    DECLARE @i INT      
    DECLARE @Item NVARCHAR(Max)      

    SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)      
    SET @i = CHARINDEX(@delimiter, @tempItemList)      

    WHILE (LEN(@tempItemList) > 0)      
    BEGIN      
    IF @i = 0      
    SET @Item = @tempItemList      
    ELSE      
    SET @Item = LEFT(@tempItemList, @i - 1)      

    INSERT INTO @IDTable(Item) VALUES(@Item)      

    IF @i = 0      
    SET @tempItemList = ''      
    ELSE      
    SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)      

    SET @i = CHARINDEX(@delimiter, @tempItemList)      
    END      
    RETURN      
    END



    After creating this function, use this function in your stored procedure with the code given below. Here your parameter(@ParameterName) must have the data length to be set as Varchar(Max)- because it will accept values with comma separated which would have more data length.

    select <required field name>
    from <table name>
    where <column name on which operation will happen> in (select * from <database_name>.<schema_name>.FN_Split(@ParameterName,','))

    Let me know in case of any issue.

                                               

    Thanks, Sumit

    Thursday, September 06, 2012 2:16 PM