Dependent User Parameters in Oracle Reports

[edbobsall]

I am working with Oracle Reports 9i and am not that familiar with it. I have a report that I'm trying to develop that has two user parameters on the parameter form. I want to take what the user selects from the list of values for the first one and use it to limit the LOV for the second parameter. I have tried using the first parameter as a bind variable in the select statement for the second item's LOV, but I get an error that says that a bind variable is not allowed in the select statement. The same error happens when I try a lexical expression. Does anyone know a work around for this problem? It would really be appreciated! Thanks. Ed

[Anusree]

Hi,
I am also having the same problem.Kindly help me anyone.It's very urgent.
Regards
Anusree

[chandu031]

I am working with Oracle Reports 9i and am not that familiar with it. I have a report that I'm trying to develop that has two user parameters on the parameter form. I want to take what the user selects from the list of values for the first one and use it to limit the LOV for the second parameter. I have tried using the first parameter as a bind variable in the select statement for the second item's LOV, but I get an error that says that a bind variable is not allowed in the select statement. The same error happens when I try a lexical expression. Does anyone know a work around for this problem? It would really be appreciated! Thanks. Ed

Hi,

Have you tried using Dynamical SQL. Bind variables are used with dynamic SQL. If you post your code, then I may be able to help you better.

[nilgunagat]

hello!
i am having the same problem too. can you help me to solve it.
i have two tables in db cities and counties and i have two parameters in my form
i want the counties parameter to be limited when the cities parameter is changed. whan i use select county from counties where city_id=:cities_parameter
it gives the same error that bind variables cannot be used in select statement
how can i solve it
thanks

[paramale]

Does not Look like Oracle report has this feature. .
I made following attempts:

at validation trigger of first parameter, inserted a value to the database field(say report_parameter table) to hold the value of my first parameter.

In my second parameter list I included the value from report_parameter table.

This appeared to be working fine for the default value(when parameter form was run for the first time) However, subsequent selection of parameter did not change the second list. It was still showing the old list.

My conclusion is that report parameter form does not dynamically update the list. Just does a one time retrieval of the list while parameter form is run for the first time.

[amitpatel66]

I am working with Oracle Reports 9i and am not that familiar with it. I have a report that I'm trying to develop that has two user parameters on the parameter form. I want to take what the user selects from the list of values for the first one and use it to limit the LOV for the second parameter. I have tried using the first parameter as a bind variable in the select statement for the second item's LOV, but I get an error that says that a bind variable is not allowed in the select statement. The same error happens when I try a lexical expression. Does anyone know a work around for this problem? It would really be appreciated! Thanks. Ed

This functionality can be achieved in ORACLE APPLICATIONS 11i. When you register a concurrent program, you would have select value sets one each for both the parameters. Now what you do is for SECOND parameter, select the Default Type -> SQL Statement and write a SQL statement for Default Value ->

SELECT <column_name> FROM <table_name> WHERE <column_name> = :$FLEX$.<value_set Name of the first parameter>
 

[amitpatel66]

I am working with Oracle Reports 9i and am not that familiar with it. I have a report that I'm trying to develop that has two user parameters on the parameter form. I want to take what the user selects from the list of values for the first one and use it to limit the LOV for the second parameter. I have tried using the first parameter as a bind variable in the select statement for the second item's LOV, but I get an error that says that a bind variable is not allowed in the select statement. The same error happens when I try a lexical expression. Does anyone know a work around for this problem? It would really be appreciated! Thanks. Ed

Let me know where you are trying to write a SELECT statment for second parameter?. Is it in BEFORE REPORT TRIGGER OR BEFORE PARAMETER FORM TRIGGER?

YOU should be able to reference a user parameter as a bind variable in REPORT TRIGGERS

[akothari]

Hi..

I was able to incorporate the list of values in 10g - based on the same sample from the Metalink.

I had the need to do it from three tables and also incorporate the "ALL" value in the p2 and p3 params so the user can either select a particular one or just choose "ALL" to run it for all the values.

I was able to modify the code - after some trial and errors - it now works perfectly..