Wednesday, October 30, 2013

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.

Untitiled
Dataset Properties
I am not using the “Parameters” tab to insert any parameters.
Untitiled2
Dataset Parameters
Untitiled1
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

3 comments:

  1. can i use parameter for database e.g.
    i need to use parameter for servername db2name.tablename where db2name get the value from parameter

    ReplyDelete
  2. Hi,

    Good evening. I have some question in SSRS.

    Question :--


    I am using SSRS.

    I have below requirement from the client side.

    1.) In report viewer , We want filter the result set of report viewer from

    user end like excel sheet filter.


    Is it Possible ?

    2) In Report view , I am showing only five column from dataset into table

    but While export reports to excel then I want to export all coumns of

    dataset.

    Dataset has 25 columns.

    3.)Generate a drop down list from report view result set column and after

    that apply filter on that.

    I want to make a drop down list from the report view table result set .

    I want to column name as value of drop down list .

    After that I want to filter result set on selected value filed + operator

    like,=,>, + text box value.

    expression like --

    ddl value + operator = 100;

    salary(ddl value) >= 2000(text box value) .


    Is it Possible ?

    4.) show report header above the report parameters in report view using SSRS?




    Please suggest.

    ReplyDelete
  3. thank you for this explanation, this solved my problem running SSRS against DB2 and requiring a multi-value parameter to pass to IN ('x','y','z') SQL with normally only a ?. I am sure a code function could also be used, and it is a little cumbersome to wrap a long and complex SQL in quotes and maintain as an expression, but it does work.

    Thanks again for sharing,

    Don Morrison

    ReplyDelete