Multiple string values parameter passing to SP in SSRS

Question

I have read and tried most of the related topics on this forum, but most of them don't have a lot of feedback.

So I am using SSRS 2008 R2 with Report Builder 3.

I have a simple SP as follows that filters my data using the IN.

But for some reason its not passing the parameters correctly to my sp and only filter's by the first option I select, hope you can assist.

--SP

@Warehouse varchar(max)

AS
BEGIN
SELECT WBal.Warehouse ,WBal.StockCode,((WBal.[Current] - WBal.Bal1)) CMov,((WBal.Bal1 - WBal.Bal2)) P1Mov,((WBal.Bal2 - WBal.Bal3)) P2Mov 
,((WBal.Bal3 - WBal.Bal4)) P3Mov,((WBal.Bal4 - WBal.Bal5)) P4Mov,((WBal.Bal5 - WBal.Bal6)) P5Mov,((WBal.Bal6 - WBal.Bal7)) P6Mov
,((WBal.Bal7 - WBal.Bal8)) P7Mov,((WBal.Bal8 - WBal.Bal9)) P8Mov,((WBal.Bal9 - WBal.Bal10)) P9Mov,((WBal.Bal10 - WBal.Bal11)) P10Mov
,((WBal.Bal11 - WBal.Bal12)) P11Mov   
FROM
(
SELECT [StockCode]
      ,[Warehouse]
      ,(QtyOnHand  * UnitCost)  [Current]
      ,(OpenBalCost1 * OpenBalQty1) Bal1
      ,(OpenBalCost2 * OpenBalQty2) Bal2
      ,(OpenBalCost3 * OpenBalQty3) Bal3
      ,(OpenBalCost4 * OpenBalQty4) Bal4
      ,(OpenBalCost5 * OpenBalQty5) Bal5
      ,(OpenBalCost6 * OpenBalQty6) Bal6
      ,(OpenBalCost7 * OpenBalQty7) Bal7
      ,(OpenBalCost8 * OpenBalQty8) Bal8
      ,(OpenBalCost9 * OpenBalQty9) Bal9
      ,(OpenBalCost10 * OpenBalQty10) Bal10
      ,(OpenBalCost11 * OpenBalQty11) Bal11
      ,(OpenBalCost12 * OpenBalQty12) Bal12

  FROM [SysproCompanyR].[dbo].[InvWarehouse]
  Where Warehouse in (SELECT * FROM dba_parseString_udf((@Warehouse), ' '))
  ) WBal


END

I have the following udf that apparently will split of the string, but im not that good with this so I don't know if my problem perhaps lies with the udf

UDF

ALTER FUNCTION [dbo].[dba_parseString_udf]
(
          @stringToParse VARCHAR(8000)  
        , @delimiter     CHAR(1)
)
RETURNS @parsedString TABLE (stringValue VARCHAR(128))
AS
/*********************************************************************************
    Name:       dba_parseString_udf

    Author:     Michelle Ufford, http://sqlfool.com

    Purpose:    This function parses string input using a variable delimiter.

    Notes:      Two common delimiter values are space (' ') and comma (',')

    Date        Initials    Description
    ----------------------------------------------------------------------------
    2011-05-20  MFU         Initial Release
*********************************************************************************
Usage:      
    SELECT *
    FROM dba_parseString_udf(<string>, <delimiter>);

Test Cases:

    1.  multiple strings separated by space
        SELECT * FROM dbo.dba_parseString_udf('  aaa  bbb  ccc ', ' ');

    2.  multiple strings separated by comma
        SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ',');
*********************************************************************************/
BEGIN

    /* Declare variables */
    DECLARE @trimmedString  VARCHAR(8000);

    /* We need to trim our string input in case the user entered extra spaces */
    SET @trimmedString = LTRIM(RTRIM(@stringToParse));

    /* Let's create a recursive CTE to break down our string for us */
    WITH parseCTE (StartPos, EndPos)
    AS
    (
        SELECT 1 AS StartPos
            , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
        UNION ALL
        SELECT EndPos + 1 AS StartPos
            , CharIndex(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
        FROM parseCTE
        WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
    )

    /* Let's take the results and stick it in a table */  
    INSERT INTO @parsedString
    SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
    FROM parseCTE
    WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
    OPTION (MaxRecursion 8000);

    RETURN;   
END

So what im trying to achieve is, I have a sp that populates my values for my parameter as did the setup like follow in ssrs

SELECT DISTINCT (Warehouse)
FROM [SysproCompanyR].[dbo].[InvWarehouse]

And then in my Dataset for my report where I need to apply this looks I have tried various alternatives in the parameter option:

expression

=JOIN(Parameters!Warehouse.Value,",")

Answer 1

When you pass multiple values from SSRS, it sends the values as comma delimited value1,value2,value3....

In you query you are splitting on white string ' '

Where Warehouse in (SELECT * FROM dba_parseString_udf((@Warehouse), ' '))

It should be using comma , instead of the white space ' '

Where Warehouse in (SELECT * FROM dba_parseString_udf((@Warehouse), ','))