How to pass multi value parameters in SSRS for the Oracle Database using OLEDB Connection

Rajesh Chittineni 141 Reputation points
2020-12-07T09:28:55.863+00:00

Hi ,

We have created a parametrized report that has to take multiple values. Created two data sets ( One for report Data Set and the other for report paramarter dataset)
Below is the screenshot for report data set where :id is the parameter.

45640-image.png

Added parameter value for the report dataset like below

45741-image.png

Created report dataset like below .

45575-image.png

But while doing preview it is working for only single value and when i selected multi values in the parameters is not working.
Could you please help me on this.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,813 questions
0 comments
Accepted answer
  1. Rajesh Chittineni 141 Reputation points
    2020-12-09T06:22:56.133+00:00

    Hi ,

    Thanks for providing the valuable infromation. If we are using expression in the main dataset and pass the paramter in the expression it is working

    Below is the expression used in main dataset instead of query

    46562-image.png

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,001 Reputation points
    2020-12-07T12:30:05.597+00:00

    Why are you using the JOIN function for the parameter? If you define the parameter as multi-value, then SSRS already creates the value as comma separated list for you, no need for JOIN nor the reg ex in the PL/SQL query, a simple IN (:id) in the WHERE clause already works.

    1 person found this answer helpful.
  2. Joyzhao-MSFT 15,566 Reputation points
    2020-12-08T02:04:48.313+00:00

    Hi @Rajesh Chittineni ,
    In fact, we can assign values to parameters through Dataset2, and then use the obtained parameters to filter Dataset1 to complete your needs.

    1. First, create a new Dataset2 (here you can use the where clause to conditionally delete the ID field, such as where ID between 174129 and 476800), create a new parameter, and select "Allow multiple values" in the parameter properties, Available Values and On the Default Values option page, select "Get values from query", drop down options to select "Dataset 2", and select the "ID" field.
      46101-allow-multiple-values.jpg
      46025-02.jpg
    2. Select Dataset 1, right click to open DatasetProperties, select Filter, set as shown below:
      46003-filters.jpg
      Hope this helps.
      Regards,
      Joy

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    What can I do if my transaction log is full?--- Hot issues November
    How to convert Profiler trace into a SQL Server table -- Hot issues November

  3. Olaf Helper 41,001 Reputation points
    2020-12-09T07:46:24.707+00:00

    Which Oracle data provider are you using, .NET, OleDB or ODBC?
    E.g. ODBC supports only the question mark (?) as parameter placeholder.

  4. Anil Kumar Kathraji (akathraji) 1 Reputation point
    2021-06-22T17:46:28.213+00:00

    Hi @Rajesh Chittineni
    I'm facing a similar issue with multiple parameters and am trying to use the expression you mentioned here but in my case the values are in Text so I need to pass quotes(') along with values like below.
    ('value','value2')..
    May I know how to include single quotes here.I tried a couple of expressions but they're not working.

    0 comments