Display SSRS multiple value parameter as a List in Table

I'm trying to accomplish a very simple task. I am trying to create a  report in SSRS. I don't even want to pull data from SQL. All I want to accomplish is to define a Multil-Value Parameter and when the user clicks "view report", to spit out the list they entered.

So I define the Parameter @MyList and set it to multi-value. I created a dataset with the query  select (@MyList). This only works when you type in one line. It crashes when there is more than one. I've also tried various parameter splitter functions I found online with no help.

The reason I'm doing this is because I have a nifty SSRS barcode rendering dll I put place. I want to create a self service barcode generating report for end users. It will generate an image for every value they enter.
mossmisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
You should be able to accomplish this with the Join() function, something like:

=Join(Parameters!ReportParameter1.Value, vbcrlf)
0
nishant joshiTechnology Development ConsultantCommented:
Hello mossmis,

You will get comma separated list using join() function but if you want to get tabular data as you mentioned you have to use it in barcode dll.

You need to created stored procedure for same.Please follow below step to accomplish your needs:

Create function to split string

 
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
    (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
 -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
            ),                          --10E+1 or 10 rows
   E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
   E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                 -- for both a performance gain and prevention of accidental "overruns"
             SELECT 0 UNION ALL
             SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
            ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
             SELECT t.N+1
               FROM cteTally t
              WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
            )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
    Item       =    SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
   FROM cteStart s
;

Open in new window


Create stored procedure to return table from function

CREATE PROCEDURE dbo.DelimitedSplit8K_SP (@MyList VARCHAR(MAX))
AS
BEGIN
SELECT 
	ItemNumber,
	Item
FROM dbo.DelimitedSplit8K(@MyList, ',') AS x
END

Open in new window


Created Dataset in report and pass multi value parameter to sp.

Hope this helps...Have a great day.
0
Nico BontenbalCommented:
Join will return one string with all the values in the parameter. If you need those values in a table you can use SQL to spit the values. This piece of SQL can do that:
declare @str varchar(max) = '123;456;789'
declare @sep varchar(1) = ';'

;WITH a AS(
		SELECT CAST(0 AS BIGINT) AS idx1,CHARINDEX(@sep,@str) idx2
		UNION ALL
		SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
		FROM a
		WHERE idx2>0
	)
SELECT convert(int,LTRIM(RTRIM(SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1)))) AS value
FROM a

Open in new window


In the sample report I've attached I use a expression for the query text. Using the values of the parameter as input for the @str variable like this:

="declare @str varchar(max) = '" & join(Parameters!MyList.Value,";") & "' " &
" declare @sep varchar(1) = ';' " &
";WITH a AS( " &
"		SELECT CAST(0 AS BIGINT) AS idx1,CHARINDEX(@sep,@str) idx2 " &
"		UNION ALL " &
"		SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1) " &
"		FROM a " &
"		WHERE idx2>0 " &
"	) " &
"SELECT convert(int,LTRIM(RTRIM(SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1)))) AS value " &
"FROM a"

Open in new window


Then you have a table with the values entered for the parameter.
To use my sample report you need to point the data source of the report to your own SQL Server.
MultiTable.rdl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Nico BontenbalCommented:
@nishant joshi, sorry I didn't see your comment until I posted mine.
0
ValentinoVBI ConsultantCommented:
Nishant, you stated the following: "You will get comma separated list using join() function"

Well actually, you won't!!  The outcome of Join() depends on what separator you specify in the second parameter.  My solution specifies vbcrlf which is the equivalent of carriage return/line feed.  So the result is each parameter item on a separate line in the report, as requested by asker.  In the future please do thorough investigation before posting, this only confuses the asker.
0
mossmisAuthor Commented:
This solution was the first I got working. It preferred this one as I didn't need to create stored procedures for it. I wasn't going to join the data with database tables.  I did some mods as the end user is typing in strings, not only integers. Then I integrated my barcode function into it and got a nice self service barode sheet generator. Thank you.
0
Nico BontenbalCommented:
That's nice to hear. Are you still using the ; as a separator. Because then you get the wrong result when a user types in the ; in a string. So you might want to use another separator like a tab or linefeed character.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.