Greetings!!
I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.
PROBLEM Definition
We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully
Sample Query used in sample SSRS data-set when SQL server was at back end:
SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE (PRODUCT IN (@p))
The report worked fine in this case when the user selected certain values in multi-select list.
Thank you,
Karthik
Multi-value parameter cannot be passed to SQL where clause (Oracle db)
Adam Lofts
Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:
SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)
-- Robert
R.Tutus
Peter Ritchie
Oracle can not support the query parameter with a name as your case "@p". You may change your query into:
SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE (PRODUCT IN )
Then set the parameter to one of your report parameter.
It should be work.