SSRS Result on Multiple Value Parameters

Question

I have an SSRS report that takes multiple valued parameter, when I try to generate the sp's with the multiple values, I get the desired result on the contrary when I'm on the SSRS env (test), even if I select multiple values, I only get the top row as a result, below is my source;

sp to generate the multiple rows

SELECT RIGHT('0'+StoreCode,4) + ' - ' + LTRIM(RTRIM(StoreName)) AS Result
FROM tblInterface_Branch
WHERE StoreCode IN (SELECT * FROM list_to_tbl(@var))

function, which i've copied from a tutorial

-- from http://www.sommarskog.se/arrays-in-sql-2005.html
-- original name: iter$simple_intlist_to_tbl
ALTER FUNCTION list_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos        int,
       @nextpos    int,
       @valuelen   int 

SELECT @pos = 0, @nextpos = 1 

WHILE @nextpos > 0
BEGIN
  SELECT @nextpos = charindex(',', @list, @pos + 1)
  SELECT @valuelen = CASE WHEN @nextpos > 0
                          THEN @nextpos
                          ELSE len(@list) + 1
                     END - @pos - 1
  INSERT @tbl (number)
     VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
  SELECT @pos = @nextpos
END
RETURN
END

Is there something I'm missing?

Try removing any grouping and filtering to double-check what the dataset is returning. — StevenWhite, May 20 '13 at 16:03
It was my mistake, I didn't use a table to display the multiple rows. Thanks! — mirageservo, May 21 '13 at 05:58

SSRS Result on Multiple Value Parameters

0 Answers0