[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5382
  • Last Modified:

Semi Urgent - Reporting Services 2005 - pass multi-value parameter to function in code behind

In reporting services 2005, how would I  call a function in the code behind, and successfully pass a multivalue parameter?
If i have a parameter defined for state that is multi-value, and i want to do something like this the command text window:


where the parameter "StateParameter" is a multi-value dropdown...and then in the code window have something like this:

 Public Shared Function BuildSQLQuery(ByVal strState as string) As String
        Dim sql As String = ""
        sql = " SELECT  * from SOME_TABLE WHERE state_id IN " + strState
        BuildSQLQuery = sql
End Function

When I set the state parameter to multivalue, i get a generic error - when i un-check multivalue, it passed the value to the function just fine....any ideas?
  • 3
1 Solution
That's the syntax how to extract a list of values from a mutli-value parameter:

=Join( Parameters!MyParam.Value, ", " )

You may want to modify your query as well:

"... WHERE state_id IN (" & strState & ")"

As you're using VB.NET syntax, it's better use & to concatinate rather than +

Why would you do this kind of thing though? I mean create a sql query in the embedded function? You can have this kind of query in your dataset and pass a multi-valued parameter EASILY there, just like that:

SELECT  * from SOME_TABLE WHERE state_id IN ( @MyParam )

Just tested multi-value params, as in terms of passing values to your function, this syntax should work fine:

=Code.BuildSQLQuery( Join( Parameters!StateParameter.Value, ", " ))

Whether or not it's going to be a working SQL query ;) - is a different matter.


friskyweaselAuthor Commented:
thanks a lot Yurich - As far as the functions in the code behind, in some of the past reports I worked on we had the need for somewhat more detailed logic...which the code behind allowed us to use (case statements, for loops, etc...) - We just continued to do it this same way on the current reports as well I guess, in case the need for more detailed programming functionality arose again. I agree it's not the most elegant solution though - In the mean time I actually did it the way you suggested in your first post ("WHERE state_id IN ( @MyParam )").
But it's nice to know I can use the JOIN() functionality in case I need to go back to using a function in the code behind. Once, again - thanks for the perfect answer and the good tips.
My pleasure :)

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now