Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Multi-valued Parameters problem in Reporting Services


The Multi-valued Parameters problem in Reporting Services

Author
Message
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11054 Visits: 14859
As Simon mentioned the parameter is passed as a comma-delimited list by default so I would just split the list in the stored procedure into a table and join on it. If you have a Numbers/Tally table splitting a string is pretty fast unless it is an extremely large list and there is an article being written that will address this as well.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Irina Ostrovskaya
Irina Ostrovskaya
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 112
I would like to know how to exclude "All" from multi-valued parameter and restrict users to choose not more than 5(for example) values in parameter list.
If somebody knows the answer please help. Thank you.
RSCERO
RSCERO
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 138
We found a pretty easy to use and reliable way of doing this using a split function we built that returns the parameter array as a table. This way, we don't have to worry about dynamic SQL or SQL injection. Here's the function code and how we use it. The nice part about this function is that we can pass it whatever is needed for the delimiter. In this case, becuase SSRS returns multi-select parms as a comma seperated list, we use the comma. The following example will only return results from the main table where the category matches those selected in the parameters, whether it's 1 or 100 categories selected.

Example:

DECLARE @Categories VARCHAR(MAX)
SET @Categories = 'VALUE1, VALUE2, VALUE3, ..., VALUE100' (this would be sent to the sproc by SSRS)

SELECT *
FROM MainTable a
inner join Reporting.dbo.ParmSplit(@Categories,',') b on a.Category = b.Items


-- =============================================
-- Description: Function splits a delimited string into
-- seperate records and returns a table
-- containing the individual values
-- =============================================
CREATE FUNCTION [dbo].[ParmSplit](@String varchar(max), @Delimiter char(1))
RETURNS @Results TABLE (Items varchar(max))
AS
BEGIN
DECLARE @INDEX INT, @CNT INT
DECLARE @VALUE varchar(max)
SELECT @INDEX = 1, @CNT = 1 -- Initialize the variables
WHILE @INDEX !=0
BEGIN
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- Find the first split location
IF @INDEX !=0
SELECT @VALUE = LEFT(@STRING,@INDEX - 1) -- Slice it up
ELSE
BEGIN
SELECT @VALUE = @STRING
SELECT @CNT = 10000
END
INSERT INTO @Results(Items) VALUES(@VALUE) -- Store the results
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- Remove used slice
SELECT @CNT = @CNT + 1 -- See if we are done
IF LEN(@STRING) = 0 BREAK
IF @CNT > 8000 BREAK -- Limit to 8000 items
END
RETURN
END


Addressing the last limit question, although not the cleaneast method, you could change the 8000 limit in the function (or make it a parmater of the function), and set the limit to 5, thereby limiting the selection to the first 5 selected in the list. This wouldn't be very clear for the users, but it would work to limit the result set.



Alasdair Thomson
Alasdair Thomson
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 506
We struggled with this exact same issue and came up with a function which is used/called in the sp you are wanting to run;

Function:
USE [LIVE]
GO

/****** Object: UserDefinedFunction [dbo].[funcParseInputStringToTable] Script Date: 04/21/2009 13:43:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*** BEGIN CREATE FUNCTION [dbo].[funcParseInputStringToTable] ***/

CREATE FUNCTION [dbo].[funcParseInputStringToTable]
(@InputString nvarchar(255))

/* SPECIFY TEMPORARY TABLE*/
RETURNS @InputStringTable TABLE
(InputValue nvarchar(255))

BEGIN

/* DECLARE VARIABLES*/
DECLARE @Index1 AS nvarchar(255),
@ParameterValue AS nvarchar(255)


/* SET VARIABLES */
SET @Index1 = CHARINDEX(',', @InputString)

/* LOOP THROUGH THE INPUT STRING AND IDENTIFY THE INDIVIDUAL VALUES */
WHILE (@Index1 > 0 OR LEN(@InputString) > 0)
BEGIN
/* PARSE OUT EACH INDIVIDUAL PARAMETER AND STORE IN THE TEMPORARY TABLE */
IF @Index1 > 0
BEGIN
SET @ParameterValue = Left(@InputString,@Index1 - 1)
SET @InputString = Right(@InputString,Len(@InputString) - @Index1)
END
ELSE
BEGIN
SET @ParameterValue = @InputString
SET @InputString = ''
END

INSERT @InputStringTable (InputValue)
VALUES(CAST(@ParameterValue AS nvarchar(255)))

/* PREPARE TO LOOP */
SET @Index1 = CHARINDEX(',', @InputString)

END


/* RETURN THE VALUES FROM THE INPUT STRING */
RETURN
END

Then by calling this function in the SP itself we were able to correct this issue with SSRS and Multivalued parameters. By using this method we kept the security and flexibility a function offers without touching XML etc.

;-)
Keith Dunn
Keith Dunn
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 38
We ran into problems with parsing the comma delimited string into a table because some data contains the comma delimiter. That is why we went to an XML approach.
richteel
richteel
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 23
I have been able to use multi-value parameters in a report and pass them to a stored procedure without doing any heavy lifting like this. You need to create a split function shown below and use the function and the IN statement in the WHERE clause and your done.

Report Parameter
Name: FilterRegionId
Data type: Integer
Prompt: FilterRegionId
Hidden: unchecked
Internal: unchecked
Multi-value: checked
Allow null value: unchecked
Allow blank value: checked (disabled checkbox)
Available values: Non-queried
Default values: Non-queried


Stored Procedure
Parameter: @FilterRegionId AS varchar(max)
WHERE Clause: WHERE ORG.OrganizationId IN(SELECT Item FROM dbo.Split(ISNULL(@FilterRegionId, ORG.OrganizationId), ','))

Split Function
------------------------
CREATE FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

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





christina.hughes
christina.hughes
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 64
Alex

I am so glad you posted this article. It pertain specifically to an issue I am trying to resolve in two reports I am finalizing today. I am going to try what you have suggested here but am bot familar yet with CTE, the WHILE loop, or as Simon mentioned in the reply post a Split function but I will try each and hopefully come up with a solution....Thanks for the help!

Christina
i.jones
i.jones
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 73
But it feels so dirty doing string manipulation in a database to me with all that iterative processing... tally tables would be nicer, but quite a mindset jump.
Alex Grinberg-230981
Alex Grinberg-230981
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 402
In real project I never used comma. I am using any non-keybord character, for example ASCII = 135. The comma was used for simplisity.
Alex Grinberg-230981
Alex Grinberg-230981
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 402
Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search