question

rajeeec-5608 avatar image
0 Votes"
rajeeec-5608 asked AnilKumarKathrajiakathraji-4151 answered

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

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
image.png (23.0 KiB)
image.png (18.3 KiB)
image.png (17.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

rajeeec-5608 avatar image
1 Vote"
rajeeec-5608 answered

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



image.png (40.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered rajeeec-5608 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi OlafHelper

Thanks. But when i used below one below expression in the report data set getting an error like cannot add multi value parameter for :id

46054-image.png


error :

45993-image.png



If we are giving Parameters!id.Value(0) in the parameters expression in the report Dataset , it is taking only one value.

Could you please help .

0 Votes 0 ·
image.png (14.7 KiB)
image.png (29.5 KiB)
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered rajeeec-5608 commented

Hi @rajeeec-5608 ,
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



02.jpg (20.6 KiB)
filters.jpg (32.3 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Joy,

Thanks for providing the details. I have created the parameter like same as you have given. But if we use filter in the dataset 1 instead of Parameter, query execution will take lot time . Because all the data will come to report level and it will filter at report level.

Thanks

0 Votes 0 ·

Hi, thanks for your feedback. After doing this, when you preview the report, an ID parameter drop-down menu will be displayed, and you could directly select the value you need in the drop-down menu. This is just a way to create multi-value parameters.

0 Votes 0 ·

Hi,

Issue is not with report filter drop down. We are able to get the multiple values in the drop down. But while passing to main data set it is failing. Normally it will work for SQL Server Source. But this is Oracle and not sure what would be the reason for this.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered rajeeec-5608 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi OlafHelper ,

We are using OLEDB and used : as a parameter placeholder. Now with the dynamic query we are nt using any placeholder. Directly using below query as an expression in the main dataset and it is working

="SELECT ID,CREATED_DATE FROM tablename WHERE ID IN (" & Join(Parameters!id.Value,",") & ")"

0 Votes 0 ·

Hi OlafHelper ,

Do we have any other option apart from using the expression like below to achive the requirement ? ( Multi values in the paramters. )

="SELECT ID,CREATED_DATE FROM tablename WHERE ID IN (" & Join(Parameters!id.Value,",") & ")"

0 Votes 0 ·
AnilKumarKathrajiakathraji-4151 avatar image
0 Votes"
AnilKumarKathrajiakathraji-4151 answered

Hi @rajeeec-5608
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.