SQL Clone
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
Alex Grinberg-230981
Alex Grinberg-230981
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 415
Comments posted to this topic are about the item The Multi-valued Parameters problem in Reporting Services
Olivier Moreau
Olivier Moreau
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 150
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
Simon Sabin
Simon Sabin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1070 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
Simon Sabin
Simon Sabin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1070 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
Jon Spink
Jon Spink
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 945
I've always used this code in my stored procs:

charindex(',' + rtrim(fieldname) + ',',',' + @paramlist + ',') > 0
Samuel Vella
Samuel Vella
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 2144
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)
i.jones
i.jones
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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.
hayden-673486
hayden-673486
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
Jon Spink
Jon Spink
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 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.
Keith Dunn
Keith Dunn
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 42
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)
)
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