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
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.
|||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
|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?
No comments:
Post a Comment