2 Mar 2017

Calling Reports in 11g

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
            || '&paramform=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.

No comments:

Post a Comment