referencing report parameters

[tsquare74]

dear all,

i m working in oracle-developer6. in reports builder, in report parameter form, how can I refer the value selected in one parameter, in LOV select statement of other parameter or is there any other way out for this purpose

waiting for immidiate help
thanks

[keyun21]

Hi i am having kind of same problem and i am waiting for the answer.
Have you found the answer for your question.
the problem that i am having is that i have to ask user to enter DATE and CUSTOMER_NAME so i am passing two parameters do you know who to work with REPORTS BUILDER?
i will appriciated your help.
Thanks

[bhattnirav]

From where you want to pass value to parameter. You want to pass it from FORM or ...?

Explain in detail.

[keyun21]

Thanks Nirav,
Actually, the way i start my report is. I choose to use query for my report. In my query i pass the parameter. I attached my query so you can have better idea. The parameter that i am passing in in BOLD format. so when i open Report Builder i choose paper layout report and i click next and at the stage where i have to import table or query i choose option SQL QUERY and basicaly i copy and past the query. so now when i try to do the PREVIEW it is giving me an error. so after reading several thread i realize that i have to create a FORM like you mention.

The problem is i never done it before and i was thinking that i some one can give me some direction that can help me out. I will be very thankful to you nirav if you can give me little direction.

Here is my Query:

SELECT DISTINCT (SSN), first_name+' '+last_name as Full_Name, application_datetime, a1.property_name, a.bedroom_size, h.MOVE_IN_DATE, h.unit_id, u.NUM_OF_BEDROOMS, a1.property_name, a1.privateco_name
FROM PMCO.applicant a,
pmco.aha_property a1,
pmco.household h,
pmco.unit u
WHERE a.report_month = '&3' -- PASSING PARAMETER
AND h.report_month=u.report_month
AND h.changed_by=u.changed_by
AND h.UNIT_ID=u.unit_id
AND a.bedroom_size=u.NUM_OF_BEDROOMS
AND a.property_id =u.property_id
AND a.report_month=h.report_month
AND h.MOVE_IN_DATE >= a.application_datetime
AND h.MOVE_IN_DATE < a.report_month
AND h.PROPERTY_ID=a.property_id
AND h.property_id !=216
AND a1.privateco_name LIKE '%&4%' AND h.move_out_date IS NULL
AND ssn IN (
SELECT DISTINCT (a.SSN)
FROM PMCO.applicant a
WHERE a.report_month = '&3'
AND a.move_in_date IS NULL
AND h.PROPERTY_ID=a.property_id
AND a.property_id=a1.property_number
AND h.property_id !=216
AND a.ssn IN (
SELECT r.ssn
FROM pmco.residents r
WHERE r.REPORT_MONTH = '&3'
AND UPPER(a.last_name) = UPPER(r.last_name)
AND UPPER(a.first_name) = UPPER(r.first_name)
AND r.unit_id=h.unit_id
AND (2||SUBSTR(r.unit_id,0,2) != a.property_id -- 20060725 mburch - to exclude applicants to a different property
OR a.bedroom_size != u.NUM_OF_BEDROOMS) -- or a different bedroom size
AND a.changed_by = r.changed_by)
)

[bhattnirav]

There are two ways to pass parameter.

1. You can create parameter form in report itself. Here you have to allow
users to enter parameter value.

2. You can create FORM. Run the report from FORM by passing the parameter to report

e.g.

A. Create one button.
B. Write following code in when-button-press trigger

declare
pl_id paramlist;
pl_name varchar(10) := 'rpt_param';

begin
pl_id := get_parameter_list(pl_name);
if not id_null(pl_id) then
destroy_parameter_list(pl_id);
end if;
pl_id := create_parameter_list(pl_name);

add_parameter(pl_id, 'P_REP_TITLE', text_parameter, 'Report Name');

Run_Product(REPORTS,'ABC', SYNCHRONOUS, RUNTIME,FILESYSTEM, pl_id, NULL);

end;


Here P_REP_TITLE is same parameter name must be exist in report.

[keyun21]

Thanks Nirav
but this is what i found out about creating a Button in Oracle 10g. when i try to find out how to create a button in Report Builder this is what i found out from Help Menu

Note: * The ability to create a button in the Paper Layout view is no longer supported in Oracle Reports 10g. * Use hyperlinks instead.

So can you give me direction how to put the button or hyperlinks in Paper Layout Form? I will be very thankful to you Nirav.

Thank You