HOW TO CALL REPORT FROM ORACLE DEVELOPER9

[lucky]

I WANT TO CALL A REPORT FROM ORACLE FORM BUILDER 9i.AS IN BUILDER 6i WE USE RUN_PRODUCT.WHAT WE USER IN 9I AND WHAT ARE THE PARAMETERS. IF ANY EXAMPLE I WILL BE VERY THANKFUL FOR THAT.

[ahmadbilal]

topic.asp?TOPIC_ID=109&SearchTerms=reports,in,forms,9i




[quote]I WANT TO CALL A REPORT FROM ORACLE FORM BUILDER 9i.AS IN BUILDER 6i WE USE RUN_PRODUCT.WHAT WE USER IN 9I AND WHAT ARE THE PARAMETERS. IF ANY EXAMPLE I WILL BE VERY THANKFUL FOR THAT.

<i><div align="right">Originally posted by lucky

[musman]

Thanks a lot

i also want to pass runtime query from DS9.
Please tell me if u have solution for that.I mean how to pass runtime query parameter from DS9.

[ahmadbilal]

You can use lexical parameter as use in oracle dev 6i


1. Setup the final where clause lexical parameter as a user parameter
a. In the Object Navigator, expand the Data Model
b. Click on User Parameters
c. Click on the add (+) on the tool bar to the left to add a parameter
d. Double Click on the 'XY' icon next to the new parameter to open the property palette
e. Change the name to FINALWHERE
f. Change the data type to Character
g. Change the width to 200 or more
h. Set the initial value to (1=1)
I. closes the property palette.

2. Add code to the After Parameter Form trigger to manipulate the
Final where clause
a. In the Object Navigator, expand the Report Triggers
B. Double Clock on the circle icon next to After Parameter Form to bring up the PL/SQL code block.
c. Add code similar to the following that address each type of data type:

Function AfterPForm return Boolean is
Begin
IF :p_dept_start is null then
:final where := 'deptno>=1';
ELSE
:finalwhere := 'deptno>='||to_char(:p_dept_start);
END IF;
IF :p_dept_end is null then
:finalwhere := :finalwhere||' AND deptno<=99';
ELSE
:finalwhere := :finalwhere||' AND deptno<='||to_char(:p_dept_end);
END IF;
IF :p_manager is null then
:finalwhere := :finalwhere||' AND (1=1)';
ELSE
:finalwhere := :finalwhere||' AND mgr='||to_char(:p_manager);
END IF;
IF :p_job is not null then
:p_job := UPPER(:p_job);
:finalwhere := :finalwhere||' AND job='||chr(39)||:p_job||chr(39);
END IF;
IF :p_begin_date is not null AND :p_end_date is not null then
:finalwhere := :finalwhere||'AND emp.hiredate between
to_date('||Chr(39)||:p_begin_date||Chr(39)||','||Chr(39)||'dd-mon-rr'||Chr(39)||') and to_date('||Chr(39)||:p_end_date||Chr(39)||','||Chr(39)||'dd-mon-rr'||Chr(39)||')';
END IF;
return (TRUE);
end;




<b>Metalink Note:227937.1</b>
[quote]Thanks a lot

i also want to pass runtime query from DS9.
Please tell me if u have solution for that.I mean how to pass runtime query parameter from DS9.

<i><div align="right">Originally posted by musman

[musman]

i defined six parameter at form level and pass through url.
now how to define these parameters in report builder after parameter form.if i use them the error is bad bind variable .
either i have to change query
how to write query in report builder
i am trying but getting nothing


thanks;

[ahmadbilal]

Please ask a specific question you are asking bulk of question in single post its confusion for all.

i would like to suggest you post complete case with quey



[quote]i defined six parameter at form level and pass through url.
now how to define these parameters in report builder after parameter form.if i use them the error is bad bind variable .
either i have to change query
how to write query in report builder
i am trying but getting nothing


thanks;

<i><div align="right">Originally posted by musman

[musman]

Sorry Sorry Sorry


Now please check my question

i want to pass a query that is built at form level to report builder.As in Forms6i we do like that

1) First built a record group at runtime at form builder level
2) Second populate that group
3) Third pass that group to query like that

add_parameter(pl_id,'Q_1',data_parameter,'emp_query');

Here Q_1 is name of query in Reports Builder and emp_query is record group that we generated at form builder level

4) Now query in Report Builder will be populated against emp_query that we pass through parameter from form builder.

Now i think that would be clear.

Now i want to do that using DS9i and Database 10G