SQL Server Reporting Services (SSRS) - Passing multi-valued parameters to DB2

I had a requirement where I wanted to pass multi-valued parameters in a report querying a DB2 database. Couldn't do it myself and posted a question in SQL Server Reporting Services, Power View forum. This nice guy, Charlie Liao came in and solved my problem and thought to share the answer with you all.

For SSRS what Microsoft says about passing multi valued parameters is, as far as you satisfy following requirements SSRS can pass multi-valued parameters.

---

You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

• The data source must be SQL Server, Oracle, or Analysis Services.
• The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.

• The query must use an IN clause to specify the parameter.

---

Unfortunately my scenario did not satisfy above requirements. I was grabbing data from a DB2 database. But does this mean I can’t use multi valued parameters against a DB2 database? No, there is a workaround, using the approach I am going to describe below, I did able to accomplish the task easily.

Let’s take this example. Let’s say I have a report and I have two parameters. One is the Company and the other is the Supplier. Supplier is a multi-valued parameter (I should be able to select many Suppliers).

First of all you will have to slightly change how you design/write the query in the report data set. That is, in the dataset properties I am not going to use the Query Designer, instead I am clicking in “fx” and writing the query as a expression.

Dataset Properties

I am not using the “Parameters” tab to insert any parameters.

Dataset Parameters

Dataset query as an Expression

Here I have wrote the query as a expression. Now you might be wondering why I have used this way. The thing is when you are using a DB2 database, in your query you should leave the parameters as “?”. If I modify the above SQL statement, it should be like the following.

SELECT somefields

FROM   sometable

WHERE  (sometable.CompanyCode=?) AND (sometable.SupplierCode IN ?)

When you wrote the above SQL query, SSRS Report designer will automatically create two parameters named “Parameter1” and “Parameter2”. You can’t change the parameter names, but you can change the prompt value.

But the thing is you can’t use these type of parameters in a query where you have a IN operator(as in multi valued parameter). You will get a error saying "A buffer passed to a system call is too small to hold return data character data right truncation". The solution is to write the query as a expression. When you write the query as a expression, you have more flexibility over everything. You can change the parameter names, you can pass multi-valued parameters etc..

So hope this helps. Again big thanks goes out to Charlie Liao for the great help.

Happy Coding.

Regards,
Jaliya