469,267 Members | 1,108 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Dependent User Parameters in Oracle Reports

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
Apr 24 '07 #1
7 10402
Hi,
I am also having the same problem.Kindly help me anyone.It's very urgent.
Regards
Anusree
May 14 '07 #2
chandu031
78 Expert
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.
May 14 '07 #3
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
Jun 4 '07 #4
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.
Sep 14 '07 #5
amitpatel66
2,367 Expert 2GB
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 ->

Expand|Select|Wrap|Line Numbers
  1. SELECT <column_name> FROM <table_name> WHERE <column_name> = :$FLEX$.<value_set Name of the first parameter>
  2.  
Sep 17 '07 #6
amitpatel66
2,367 Expert 2GB
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
Sep 17 '07 #7
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..
Sep 24 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by kristoff plasun | last post: by
reply views Thread by Rohit Dhawan | last post: by
1 post views Thread by rishka | last post: by
133 posts views Thread by jonathan | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.