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 12:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 8:37 AM
Points: 27, Visits: 312
Comments posted to this topic are about the item The Multi-valued Parameters problem in Reporting Services
Post #701184
Posted Tuesday, April 21, 2009 1:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 1, 2014 1:26 AM
Points: 1, Visits: 143
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
Posted Tuesday, April 21, 2009 1:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 572, Visits: 107
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
Posted Tuesday, April 21, 2009 1:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 572, Visits: 107
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
Posted Tuesday, April 21, 2009 2:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Tuesday, April 21, 2009 2:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:51 AM
Points: 328, Visits: 2,001
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
Posted Tuesday, April 21, 2009 4:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, April 21, 2009 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.



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

Cheers
Post #701319
Posted Tuesday, April 21, 2009 5:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Tuesday, April 21, 2009 6:14 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 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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse