Multi-value parameter cannot be passed to SQL where clause (Oracle db)

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?

Multi-value parameter cannot be passed to SQL where clause (Oracle db)

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?

Multi-Select Value dropdown "Select All" Problem

Hi,
I hava report (RDL) which is having a parameter called Plan Owner, i
want the facality of "Select All" in the dropdown, this i can achive
through setting the parameter as Multi-Value. But the problem is if the
user select "select all" from dropdown, i uses the IN claues, i want to
avoid the use of IN clause if the "select all" is selected in
Multi-value dropdown. How can i programatically write the dataset to
avoid "select all"?
What is the internal value of "select all" which it added automatically
in the multi-value parameter dropdown.
I can provide more info if needed.
Regards,
SumitThis is my interpretation of the multivalue parameter.
The internal representation of a multivalue parameters is an array of
all the possible values for the parameter. So if your parameter has 3
values (A,B,C) if you do 'Select All', RS sees an array with values
(A,B,C)
The only way I know of to determine if someone has picked 'Select All'
is to test the array against the # of rows in the dataset populating
the parameters. It would look something like this:
=iif(Parameters!MultiValParam.Count = Count("datasetformvparam"), "",
"FIELD IN (" & join(Parameters!MultiValParam.Value, ", ") & ")")
Andy Potter|||Hi,
I tried the above solution but the count of my dataset always returns 0
even though there are records in there. Any suggestions?
=iif(Parameters!bus_group.Count = Count("business_group"),
"ALL",Join(Parameters!bus_group.Label, ", ") )
Thanks,
Melissa|||I ran into a similar problem in wanting to know if they had selected all
possible options. See the following for a further discussion, a feature
suggestion, and a workaround:
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=dfec87e1-a34d-4ff8-a480-fb1f8d4160ea
"Melissa" wrote:
> Hi,
> I tried the above solution but the count of my dataset always returns 0
> even though there are records in there. Any suggestions?
> =iif(Parameters!bus_group.Count = Count("business_group"),
> "ALL",Join(Parameters!bus_group.Label, ", ") )
> Thanks,
> Melissa
>