The Multi-valued Parameters problem in Reporting Services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Alex Grinberg » The Multi-valued Parameters problem in...

66 posts, Page 1 of 7

1 2 3 4 5 »»»

The Multi-valued Parameters problem in Reporting Services

Rate Topic

Display Mode

Topic Options

Author

Message

Alex Grinberg-230981

Posted Tuesday, April 21, 2009 12:15 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:48 PM
Points: 22, Visits: 243

Comments posted to this topic are about the item The Multi-valued Parameters problem in Reporting Services

Post #701184

Olivier Moreau

Posted Tuesday, April 21, 2009 1:05 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 6:18 AM
Points: 1, Visits: 129

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)
http://www.omrbi.com

Post #701197

Simon Sabin

Posted Tuesday, April 21, 2009 1:29 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:18 AM
Points: 573, Visits: 95

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

Post #701205

Simon Sabin

Posted Tuesday, April 21, 2009 1:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:18 AM
Points: 573, Visits: 95

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

Post #701207

Jon Spink

Posted Tuesday, April 21, 2009 2:01 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
Points: 530, Visits: 945

I've always used this code in my stored procs:

charindex(',' + rtrim(fieldname) + ',',',' + @paramlist + ',') > 0

Post #701218

Samuel Vella

Posted Tuesday, April 21, 2009 2:03 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:22 AM
Points: 328, Visits: 1,852

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)

Post #701219

i.jones

Posted Tuesday, April 21, 2009 4:58 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 13, 2009 7:18 AM
Points: 18, Visits: 73

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.

Post #701305

hayden-673486

Posted Tuesday, April 21, 2009 5:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 4:13 PM
Points: 2, Visits: 108

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:
http://www.theruntime.com/blogs/thomasswilliams/archive/2008/09/29/hiding-and-showing-columns-based-on-a-parameter-in-reporting.aspx

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.

Smile

Good article, glad to see this is being addressed by many others too.

Cheers

Post #701319

Jon Spink

Posted Tuesday, April 21, 2009 5:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
Points: 530, Visits: 945

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.

Post #701328

Keith Dunn

Posted Tuesday, April 21, 2009 6:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 04, 2012 9:33 PM
Points: 5, Visits: 32

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 & " strXML = strXML & "value = " & Chr(34) & Cstr(obj.Value(i)) & Chr(34) & " "
strXML = strXML & "label = " & Chr(34) & Cstr(obj.Label(i)) & Chr(34) & " "
strXML = strXML & "/>"
Next
Else
strXML = strXML & " strXML = strXML & "value = " & Chr(34) & Cstr(obj.Value) & Chr(34) & " "
strXML = strXML & "label = " & Chr(34) & Cstr(obj.Label) & Chr(34) & " "
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)
)

Post #701360

« Prev Topic | Next Topic »

66 posts, Page 1 of 7

1 2 3 4 5 »»»

Permissions