We have recently developed some SSRS reports against Non SQL Server sources and have run into a few issues regarding the use of multi-value parameters. Since, we are sure to meet this problem again (and variations of it), this post is just as much of a bookmark for ways around the problems that we’ve encountered.
So what’s the problem? Lets assume were using an MySql Source with the same data as AventureWorks, we can to pick a Subcategory and then have our report show a list of products. This is pretty straight forward. The products query uses a ? to identify a parameter, and that parameter is assigned a value to in the dataset properties;
SELECT * FROM adventure_works_2012.product WHERE (ProductSubCategoryID = ?)
You can see how this is setup in the image below. The SubCategory dataset is the source for the SubCategory parameter and that is used as a parameter for the Products query.
Unfortunately, this only works if our SubCategory Parameter DOESNOT allow multiple values. If we enable this as an option for the Parameter, we’ll get an error like the following when we try to view the report.
To make the products query run with multiple parameter values we need to dynamically generate the SQL associated with the parameter. Actually there are a couple variations of this however they all use the same theme.
So, first, we change our query to recognise the need for multiple values from the parameter;
SELECT * FROM adventure_works_2012.product WHERE ProductSubCategoryID in (?)
Then, instead of supplying the parameter value to the dataset, we use an expression that delimits parameter values.
Here, the source type is a integer so we are expecting something like 1,2,3 etc to be passed as the parameter’s value. If the source type was a string, we’d just have to append quotes around the statement.
= Join(Parameters!SubCategory.Value, "’ ,’")
We can use a few variations of this approach to generate the parameter list. For example, we could exclude the parameter definition from the Products dataset and specify the query as a function that returns an SQL string. Using this approach, the Parameter value is still called but the parameter object is not defined in the dataset.
We can also define a some VB code that returns the SQL string (I find this a little more flexible). Here the string value of the parameter is passed to the VB function. So here, I’ve created a simple function and added it to the report;
Public Function SQL(ByVal Keys as String) as String return “select * from adventure_works_2012.product where ProductSubCategoryID in (” & Keys & “)” End Function
Now, our dataset is defined by the expression
=Code.SQL( Join(Parameters!SubCategory.Value, ",") )