SSRS Multi value parameter with OLE DB Oracle provider

Question

Hi,
I have been facing following issues in SSRS,

1. Selecting single value in drop-down (parameter), report works fine. But when I select multiple values/ select all option in drop-down, only table headers get displayed. where clause used is,
   where company_id in (:company_id)
2. I need to have 'ALL' as one of the options in drop-down, if I select 'ALL', again the datas are not loading. And I get error when I use the following code
   where (company_id in (:company_id) or 'ALL' IN (:company_id))

Kindly help. Since I'm new to SSRS, I really need guidance to solve this issue

Answer 1

Hi @R S ，
1） Add or ('All' in (@Parameter)) after the Where clause in the main dataset query;---DataSet1

SELECT        company_id,FieldsB,FieldsC  
FROM            TableName  
WHERE        ('All' IN (:company_id)) OR  (company_id IN (:company_id))                       

2）Add the "All" option to the query dataset. --DataSet2

SELECT        'All' AS company_id  
UNION ALL  
SELECT        company_id  
FROM          TableName  

Then set the parameter properties, select 'Get Values from a query' in Available Values, select Dataset 2 and the corresponding field in the drop-down list.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

Hi Joy,
I have tried the same procedure as u given. I'm getting the following error

Answer 3

Hi Joy,
It is actually working. Problem is with OLE Db, when I use Oracle database. It perfectly works.
I need one more help,
I want to display company list like this,
All
101
102

(All is the first option, followed by company_id in ascending order)