How to call reports using oracle forms 11g developer (Oracle Fusion) or 10g Developer with paramform=yes
Well I am sure this example will also help you to create your production level forms and reports.
This is the simple example using the demo user of oracle “HR” having table“employee”.
*************
Coding for report calling using paramform=yes or paramform=no is different.
So given below example will help you to call the report using paramform=yes.
It will create a new web page with the parameters defined on the report.
*************
Create a simple report based on table EMPLOYEE with a user parameter:
SELECT ALL EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME,
EMPLOYEES.HIRE_DATE, EMPLOYEES.SALARY, EMPLOYEES.DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=:DEPARTMENT;
Now Compile and save the report C:\TEST\FORMS11G\EMP.RDF. Please check your report is working fine and also taking the report input from your parameter. You can view the output in run preview.
Then create a simple form and drag a button to call report using the procedure or create a menu or Htree based menu to call that procedure.
Now add a new object for "Reports" in the object navigator of Forms.
A dialog box will appear and select the option "Use Existing Report File" and browse the file. An object with the default name will appear under Reports Object.
Now Open Property Palette for Reports objects in the Forms Object Navigator and see the following values:
Name: EMPLOYEE
Filename: C:\TEST\FORMS11G\EMP.RDF
Now Check your Weblogic server is in running mode or
Start Your Weblogic Server (Whatever configuration you had set)
Now check your 11g report server is running or
Note: Please note that for 10g you must specify full path to your RDF file or add this path into REPORTS_PATH environment variable.
Report Destination Type: CACHE
Report Destination Format: HTML
Report Server: rep_adminserver_my-pc_asinst_2
(This is the same server name as defined in the file D:\Oracle\Middleware\asinst_2\reports\reports_install.properties)
On button create a WHEN-BUTTON-PRESSED trigger with the following code:
Test_report;
Now create a procedure.
Name = test_report
Now write the code in the area of code:
***********************
PROCEDURE TEST_REPORT_PARAMFORMYES IS
vc_urlvarchar2(1000);
vc_user_nameVARCHAR2(100);
vc_user_passwordVARCHAR2(100);
vc_user_connectVARCHAR2(100);
vc_connectVARCHAR2(300);
vc_reportvarchar2(100) := 'C:\TEST\FORMS11G\emp.rdf';
vc_servervarchar2(50):= 'rep_adminserver_my-pc_asinst_2';
BEGIN
vc_user_name:=get_application_property(username);
vc_user_password:=get_application_property(password);
vc_user_connect:=get_application_property(connect_string);
vc_connect:=vc_user_name||'/'||vc_user_password||'@'||vc_user_connect;
vc_url:='http://my-pc:7001/reports/rwservlet?server='||vc_server
|| '&report='||vc_report||'&desformat=pdf&destype=cache'
|| '&userid='||vc_connect
|| '&P_REP_NM='||vc_report
|| '&P_ACTION='||'http://my-pc:7001/reports/rwservlet?'
|| '&P_SERVERNAME='||vc_server
|| '&P_USER_CONNECT='||vc_connect
|| '¶mform=yes';
WEB.SHOW_DOCUMENT(vc_url,'_blank');
END;
***********************
Now simply compile the form.
And Run the form then click the button. A new web page will appear with the parameters of department. Now pass the parameters on the web based parameters and Report will be displayed in a new browser window if you have mention the html format or else an acrobat reader will run and will show the report.
If Report is not working smoothly
Open the report_install file
Located at D:\Oracle\Middleware\asinst_1\reports
Locate the Parameter
REPORTS_APP_CONFIGURATION_DIR=D\:\\Oracle\\Middleware\\user_projects\\domains\\FormsClassicDomain\\config\\fmwconfig\\servers\\WLS_REPORTS\\applications\\reports_11.1.2\\configuration
Edit the file rwservlet.properties located on
<DomainHome>\config\fmwconfig\servers\WLS_REPORTS\applications\reports_11.1.2.1.0\configuration
Add the following parameter below the <inprocess> parameter:
<webcommandaccess>L2</webcommandaccess>
Re-start the WLS_REPORTS Server.
Well I am sure this example will also help you to create your production level forms and reports.
This is the simple example using the demo user of oracle “HR” having table“employee”.
*************
Coding for report calling using paramform=yes or paramform=no is different.
So given below example will help you to call the report using paramform=yes.
It will create a new web page with the parameters defined on the report.
*************
Create a simple report based on table EMPLOYEE with a user parameter:
SELECT ALL EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME,
EMPLOYEES.HIRE_DATE, EMPLOYEES.SALARY, EMPLOYEES.DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=:DEPARTMENT;
Now Compile and save the report C:\TEST\FORMS11G\EMP.RDF. Please check your report is working fine and also taking the report input from your parameter. You can view the output in run preview.
Then create a simple form and drag a button to call report using the procedure or create a menu or Htree based menu to call that procedure.
Now add a new object for "Reports" in the object navigator of Forms.
A dialog box will appear and select the option "Use Existing Report File" and browse the file. An object with the default name will appear under Reports Object.
Now Open Property Palette for Reports objects in the Forms Object Navigator and see the following values:
Name: EMPLOYEE
Filename: C:\TEST\FORMS11G\EMP.RDF
Now Check your Weblogic server is in running mode or
Start Your Weblogic Server (Whatever configuration you had set)
Now check your 11g report server is running or
Note: Please note that for 10g you must specify full path to your RDF file or add this path into REPORTS_PATH environment variable.
Report Destination Type: CACHE
Report Destination Format: HTML
Report Server: rep_adminserver_my-pc_asinst_2
(This is the same server name as defined in the file D:\Oracle\Middleware\asinst_2\reports\reports_install.properties)
On button create a WHEN-BUTTON-PRESSED trigger with the following code:
Test_report;
Now create a procedure.
Name = test_report
Now write the code in the area of code:
***********************
PROCEDURE TEST_REPORT_PARAMFORMYES IS
vc_urlvarchar2(1000);
vc_user_nameVARCHAR2(100);
vc_user_passwordVARCHAR2(100);
vc_user_connectVARCHAR2(100);
vc_connectVARCHAR2(300);
vc_reportvarchar2(100) := 'C:\TEST\FORMS11G\emp.rdf';
vc_servervarchar2(50):= 'rep_adminserver_my-pc_asinst_2';
BEGIN
vc_user_name:=get_application_property(username);
vc_user_password:=get_application_property(password);
vc_user_connect:=get_application_property(connect_string);
vc_connect:=vc_user_name||'/'||vc_user_password||'@'||vc_user_connect;
vc_url:='http://my-pc:7001/reports/rwservlet?server='||vc_server
|| '&report='||vc_report||'&desformat=pdf&destype=cache'
|| '&userid='||vc_connect
|| '&P_REP_NM='||vc_report
|| '&P_ACTION='||'http://my-pc:7001/reports/rwservlet?'
|| '&P_SERVERNAME='||vc_server
|| '&P_USER_CONNECT='||vc_connect
|| '¶mform=yes';
WEB.SHOW_DOCUMENT(vc_url,'_blank');
END;
***********************
Now simply compile the form.
And Run the form then click the button. A new web page will appear with the parameters of department. Now pass the parameters on the web based parameters and Report will be displayed in a new browser window if you have mention the html format or else an acrobat reader will run and will show the report.
If Report is not working smoothly
Open the report_install file
Located at D:\Oracle\Middleware\asinst_1\reports
Locate the Parameter
REPORTS_APP_CONFIGURATION_DIR=D\:\\Oracle\\Middleware\\user_projects\\domains\\FormsClassicDomain\\config\\fmwconfig\\servers\\WLS_REPORTS\\applications\\reports_11.1.2\\configuration
Edit the file rwservlet.properties located on
<DomainHome>\config\fmwconfig\servers\WLS_REPORTS\applications\reports_11.1.2.1.0\configuration
Add the following parameter below the <inprocess> parameter:
<webcommandaccess>L2</webcommandaccess>
Re-start the WLS_REPORTS Server.
