Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The Multi-valued Parameters problem in Reporting Services Expand / Collapse
Author
Message
Posted Tuesday, April 21, 2009 6:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 10,260, Visits: 13,229
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

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
Post #701373
Posted Tuesday, April 21, 2009 6:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 10:10 AM
Points: 797, Visits: 101
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.
Post #701374
Posted Tuesday, April 21, 2009 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:37 AM
Points: 18, Visits: 137
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.



Post #701393
Posted Tuesday, April 21, 2009 6:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 7:25 AM
Points: 452, Visits: 481
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.

Post #701400
Posted Tuesday, April 21, 2009 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 6:28 AM
Points: 5, Visits: 33
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.
Post #701402
Posted Tuesday, April 21, 2009 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 9, 2013 8:19 PM
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




Post #701417
Posted Tuesday, April 21, 2009 7:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 12, 2010 1:45 PM
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

Post #701422
Posted Tuesday, April 21, 2009 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 13, 2009 7:18 AM
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.
Post #701426
Posted Tuesday, April 21, 2009 7:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:03 PM
Points: 27, Visits: 307
In real project I never used comma. I am using any non-keybord character, for example ASCII = 135. The comma was used for simplisity.
Post #701428
Posted Tuesday, April 21, 2009 7:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:03 PM
Points: 27, Visits: 307
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.
Post #701430
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse