October 18, 2016 at 5:39 am
I'm trying to create a report with multi-valued parameter, using a pipe delimited string. The stored procedure runs as expected. The report runs fine if I choose only one parameter but, returns no values if I choose multiple values. Any thoughts?
October 18, 2016 at 6:35 am
For multi-valued parameters SSRS will pass a comma-delimited string to a stored procedure.
Could you elaborate a bit, are you trying to split the parameter in the stored procedure by a pipe?
Multi-value parameters are stored as an array of the chosen values. Before reporting services passes the array to a procedure it concatenates the values into a comma-delimited string, by default, since SQL Server doesn't have any array-types.
What you could do is concatenate the parameter values yourself as a pipe-delimited string and reporting services can pass that string to the stored procedure.
Open your dataset properties and on the parameters tab you change the parameter value (rightmost textbox) to an expression that joins the parameter values using a pipe-delimiter, i.e.
=Join(Parameters!ReportParameter1.Value, "|")
October 18, 2016 at 6:51 am
This is the function to parse the parameter string. The parameters are doctors' full names that contain commas so, I need to use a pipe or semi colon or something as the delimiter.
ALTER FUNCTION [dbo].[fn_MVParam]
(
@RepParam varchar(MAX),
@Delim varchar(1)
)
RETURNS @Values TABLE (strList varchar(MAX)) AS
/*
Usage: Use this in your report SP
where ID in (SELECT Param FROM fn_MVParam(@PlanIDList,','))
*/
BEGIN
select @RepParam = replace(@RepParam,'''''','''')
DECLARE @chrind INT
DECLARE @Piece varchar(MAX)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(strList) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,DATALENGTH(@RepParam) - @chrind)
IF DATALENGTH(@RepParam) = 0 BREAK
END
RETURN
END
October 18, 2016 at 7:03 am
Sure. So if you can do what I said above (apologies if the edit was after you started your post) then you would call the function in your stored procedure as:
SELECT Param FROM fn_MVParam(@ReportParameter1, '|')
October 18, 2016 at 7:28 am
K. Thanx.
October 18, 2016 at 8:00 am
Works like a charm. Thanx.
October 18, 2016 at 8:08 am
NineIron (10/18/2016)
This is the function to parse the parameter string. The parameters are doctors' full names that contain commas so, I need to use a pipe or semi colon or something as the delimiter.
ALTER FUNCTION [dbo].[fn_MVParam]
(
@RepParam varchar(MAX),
@Delim varchar(1)
)
RETURNS @Values TABLE (strList varchar(MAX)) AS
/*
Usage: Use this in your report SP
where ID in (SELECT Param FROM fn_MVParam(@PlanIDList,','))
*/
BEGIN
select @RepParam = replace(@RepParam,'''''','''')
DECLARE @chrind INT
DECLARE @Piece varchar(MAX)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(strList) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,DATALENGTH(@RepParam) - @chrind)
IF DATALENGTH(@RepParam) = 0 BREAK
END
RETURN
END
As splitters go this is about as bad as they come. The performance of a loop based splitter is awful. Add to that the usage of a multi-statement table valued function and you have a performance time bomb on your hands. This works fine on really small sets but it will NOT scale well at all. For about the fastest splitter outside of CLR take a look in my signature for a much better approach to splitting strings. If you truly can't fit the delimited list in under 8,000 characters you can view this link. http://sqlperformance.com/2012/07/t-sql-queries/split-strings There you will find several other options that are also much better than looping.
And if you are using 2016 there is now a built in string splitter. https://msdn.microsoft.com/en-us/library/mt684588.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2016 at 8:18 am
Thanks for the input. I'll take a peek at your stuff.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply