21 June, 2012

Run Oracle Reports from Oracle Forms

I will explain how to run and display Oracle Reports within Oracle Forms 10g/11g

1- In Oracle Forms add new Report
At your form add new report from object navigator and change following properties
Name                                      :  ID for report in Oracle Forms
Filename                                 :  Physical file name path of report at application server
Report Destination Type       :  You can choose (File, Preview, Printer, Cache, Mail, Screen)
Report Destination Format   :  Output format of report (PDF, spreadsheet)


2- Now You can use the below code for running the report in your form

 DECLARE  
   pl_id         paramlist;  
   x           VARCHAR2 (150);  
   y           NUMBER;  
   report_service_name  VARCHAR2 (200);  
 BEGIN  
   -- Create parameter List to pass report parameters through it.  
   pl_id := GET_PARAMETER_LIST ('tmpdata');  
   
   IF NOT ID_NULL (pl_id)  
   THEN  
    DESTROY_PARAMETER_LIST (pl_id);  
   END IF;  
   
   pl_id := CREATE_PARAMETER_LIST ('tmpdata');  
   
   -- Adding Parameters to parameter List  
   ADD_PARAMETER (pl_id,  
          'P_EMP_NO',  
          text_parameter,  
          '100');  
   ADD_PARAMETER (pl_id,  
          'P_DEPTNO',  
          text_parameter,  
          '10');  
   
   -- Set Report server name which uses to run report programatically  
   report_service_name := 'FRHOME1_REPORT_SERVER';  
   SET_REPORT_OBJECT_PROPERTY ('REPORT_NAME',  
                 report_server,  
                 report_service_name);  
   
   -- Set report output format programatically  
   -- Set spreadsheet output  
   SET_REPORT_OBJECT_PROPERTY ('REPORT_NAME',  
                 report_desformat,  
                 'spreadsheet');  
   
   --set pdf output  
   --SET_REPORT_OBJECT_PROPERTY ('REPORT_NAME', report_desformat, 'pdf');  
   
   --- Execute report ar Report Service  
   y := LENGTH (report_service_name) + 2;  
   x := RUN_REPORT_OBJECT ('REPORT_NAME', pl_id);  
   
   --Display a report in URL  
   web.show_document (  
      '/reports/rwservlet/getjobid'  
    || SUBSTR (x, y)  
    || '?server='  
    || report_service_name);  
 END;  

I use SET_REPORT_OBJECT_PROPERTY procedure to change report properties programatically and Parameter List to pass parameters to report programatically( "on-the-fly" ).

Thanks
Mahmoud A. El-Sayed
Recommended Post Slide Out For Blogger