April 21, 2009 at 12:15 am
Comments posted to this topic are about the item The Multi-valued Parameters problem in Reporting Services
April 21, 2009 at 1:05 am
great tips!
you can also convert the array send by reporting services with this code in your stored procedure.
SET @MyArray= Char(39) + Replace(@MyArray,',',Char(39)+','+Char(39)) + Char(39)
if you choose the following value in RS : XXX,YYY,ZZZ
RS send it like this to the stored procedure : 'XXX,YYY,ZZZ'
With this code, the array is transform as 'XXX','YYY','ZZZ'
You can now use it in the query.
Olivier Moreau (Neos-SDI)
April 21, 2009 at 1:29 am
You don't have to change the XML, when RS passes the parameter it automatically converts the array of selected items to a comma delimited list.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
April 21, 2009 at 1:32 am
This is very dangerous as it allows SQL Injection, and as parameters can be passed by URL this is very very risky.
Using a split function is the safer option.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
April 21, 2009 at 2:01 am
I've always used this code in my stored procs:
charindex(',' + rtrim(fieldname) + ',',',' + @paramlist + ',') > 0
April 21, 2009 at 2:03 am
Nice article, with some practicle advice on manually editing the report XML
Your last comment though:
As a final word, I would also like to add that .NET knowledge is required if you want to be a successful SQL Server 2005 (and up) DBA.
Is this just restricted to DBA's? Surely Database Developers could benefit from this too.
Some of us are commited to development and don't see a DBA role as a step up the career ladder.
(personally, I went up my career ladder by going back into development)
April 21, 2009 at 4:58 am
Our solution was to
1) persist the parameters in the database
2) pass in reference GUID as a parameter to the report
3) stored procedure is called with said GUID
4) stored procedure pulls parameters using GUID
So, the multivalue params are already in a table, so no need to do any nasty splitting of strings.
April 21, 2009 at 5:19 am
I would not consider the problem calling a stored procedure where the parameter was set to Multi-value option an SSRS bug.
Yes true, it is not a bug - however, many would say the design is still flawed - by the simple fact that SSRS lets developers/DBAs choose to call a stored procedure as opposed to raw sql, and then not provide proper functionality by the application to cater for multi-valued parameters.
Don't get me wrong, I think SSRS 2005 is a fantastic product and use it everyday - however, I hope this particular issue is improved in later versions of SSRS - is it in 2008 ?. Having to maintain SSRS reports with the many types of hackery required to make this fundamental requirement work is a bit of a pain.
Also...
I've also experienced headaches when just checking to see if a specific value in a multi-value parameter list is actually selected, and then apply conditions to particular report objects based on that multivalue item being selected - You can use a VB function in the report to achieve this. As per the link below:
that method enables you to explictly use the parameter value as opposed to the index of the array. However, I would hope that, again, this is improved in later versions of SSRS.
🙂
Good article, glad to see this is being addressed by many others too.
Cheers
April 21, 2009 at 5:32 am
another problem is that when 'Select All' is selected, you have to deal with a massive string which slows down the performance of your proc, rather than ignoring the condition altogether.
April 21, 2009 at 6:14 am
We solved this using a generic function in the Code area that converts a multi-value parameter list to XML, passing the XML to the sproc and then using a generic Transact-SQL table function to convert to a table. It is not that much code and it covers all situations pretty well. By converting the list to a table that can be joined, it eliminates any performance problems associated with large lists.
Here is the code in case it will help anyone else with this common problem:
This goes in the Code area under Report Properties. Note that it also works with parameters that are not multi-valued.
Function StrParmToXML(ByVal obj As parameter) As String
Dim strXML as String
Dim i As Integer
strXML = ""
If obj.IsMultiValue then
For i = 0 To obj.count - 1
strXML = strXML & ""
Next
Else
strXML = strXML & ""
End IF
strXML = strXML & ""
Return strXML
End Function
Here is the SQL table function (SQL 2005 and up)
CREATE function [dbo].[rf_ParameterTable] (@parmxml xml) RETURNS TABLE
AS
RETURN
select
parmxml.item.value('@value', 'varchar(max)') as parm_value,
parmxml.item.value('@label', 'varchar(max)') as parm_label
from @parmxml.nodes('//parameter/item') as parmxml(item)
)
April 21, 2009 at 6:36 am
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
Consultant - Straight Path Solutions
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
April 21, 2009 at 6:36 am
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.
April 21, 2009 at 6:52 am
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 *
FROMMainTable 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.
April 21, 2009 at 6:59 am
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.
😉
April 21, 2009 at 7:01 am
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.
Viewing 15 posts - 1 through 15 (of 69 total)
You must be logged in to reply to this topic. Login to reply