Installation Cookbooks: Deploying Reports on the Web

In a previous article, I
discussed a deployment framework for getting Forms on the Web. This article
discusses the steps to get your Reports on the Web. I’ll cover the steps to use
Reports-related built-in’s in your forms, how to "tell" your forms
the name of your report server, how to toss in some JavaScript so an
"Exit" button will exit a form and close the browser window, and how
to use and configure the in-process report server. As in the Forms on the Web
article, I will not discuss Reports development.

Establishing a baseline example

Because there are so many variations of how to configure
various components, I’m going to use the following assumptions for a Forms and
Reports development-to-deployment environment.

• Your 6i forms have been successfully migrated to a 9i/10g
  state (“10g” will refer to both versions)
• You are using Application Server 10g Forms and Reports
  Services (the steps here work for the full scale version as well) and
  everything will be on one server
• When Reports were used in your 6i client-server
  environment, the reports were called via a “Run Report” button on a form
  or via a report filter type of form (it also uses a “Run Report” button
  after the user inputs parameter type of information)
• You want Reports on the Web to appear in a new browser
  window, and that browser window has its “normal” buttons removed (when the
  user is finished viewing, saving, or printing the report, the user closes
  the browser window and returns to the report-calling browser window)
• If a user wants to save a report, he has to click on the
  Save button (reports appear as PDF documents), and reports cached or saved
  on the Application Server machine are not intended to be directly accessed
  by users

The above sounds like a lot of restrictions or assumptions,
but it really boils down to a fairly simple – and easy to install/configure –
environment. If your primary task is to get things working as soon as possible,
then you just bought into a happy meal. Later on, when you have time to sit
back and figure out how you are going to spend the raise you got for turning
your Oracle-based application into a Web product, you can experiment with
ordering the big chicken dinner (SSO, a separate report server machine, using
the “other” report server, and so on).

The code to make a report run on the web

Let’s clarify the difference between parameters and data.
Parameters tell the report how it is supposed to run or display. Data is what
the report shows. How you provide the data to make a report run is up to you.
How you provide the parameters falls into two categories: those required by
Oracle, and those you want or need. Further, several items that you used to
pass in as parameters under 6i are now required to be set using a built-in with
10g. The best way to describe the code is to show an example. Some of what is
shown below comes from an example Oracle has in a note on MetaLink. The code
below is what you can put behind a “Run Report” button. I formatted some comments with a different font to help point out what is
taking place. There’s nothing worse than reading dry code, so that’s
another reason for all of the comments.

PROCEDURE RUN_REPORT IS

— Some variable you use to populate a report table named REPORT,

— or call some procedure to generate data for a report
v_report_uui    REPORT.report_uui%TYPE;
— Parameter List used in call to Oracle Report “rpt_name”
p_rpt_param_list  paramlist;
  report_id       REPORT_OBJECT;

  ReportServerJob VARCHAR2(100);

  v_jobID         VARCHAR2(100);

  rep_status      VARCHAR2(20);

  reportserver    VARCHAR2(30);

  v_url           VARCHAR2(200);

  v_url2          VARCHAR2(200);
— However you keep track of reports; this is just one way
  usr_sessionid   NUMBER;
  report_not_generated EXCEPTION;
BEGIN
–Get the user session ID
  SELECT USERENV(‘sessionid’) into usr_sessionid FROM DUAL;
— Execute database stored package.procedure “some_package.some_procedure”

— that populates a REPORT table
some_package.some_procedure

  (

   to_char(:block.some_item),      — IN

   v_report_uui                    — OUT

   );
— Destroy the parameter list if it already exists
IF NOT Id_Null(p_rpt_param_list) then

      Destroy_Parameter_List(p_rpt_param_list);

END IF;
— Create a Parameter List
p_rpt_param_list := Create_Parameter_List (‘tmp’);
— Generate report – on the Web

— You can add an ELSE clause to this to re-use your 6i code
IF (get_application_property(user_interface)=’WEB’) THEN
— You need to provide the name of the report server

— The “rpt_name” is a report object you create in the Form Builder Object

— Navigator
   reportserver := :parameter.reportserver;

   report_id := find_report_object(‘rpt_name’);
— These five calls to the built-in cover what 10g requires
   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_COMM_MODE, SYNCHRONOUS);

   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_EXECUTION_MODE, BATCH);

   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESTYPE, FILE);

   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESFORMAT, ‘pdf’);

   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_SERVER, reportserver);
— Here is where you can add your own parameters to the report
   Add_Parameter(p_rpt_param_list,’paramform’,TEXT_PARAMETER,’no’);
   Add_Parameter(p_rpt_param_list,’p_report_uui’,TEXT_PARAMETER, to_char(v_report_uui));
   Add_Parameter(p_rpt_param_list,’PRINTJOB’,TEXT_PARAMETER,’NO’);
— A common error message has to do with not being able to find the report object.

— No report object means no report_id, and you’ll get lots of errors.
   ReportServerJob := run_report_object(report_id, p_rpt_param_list);

   v_jobID := substr(ReportServerJob,length(reportserver)+2,length(ReportServerJob));
   IF ReportServerJob is NOT NULL THEN

       rep_status := report_object_status(ReportServerJob);
       WHILE rep_status in (‘RUNNING’, ‘OPENING_REPORT’,’ENQUEUED’) LOOP

         rep_status := report_object_status(ReportServerJob);

       END LOOP;

       IF rep_status != ‘FINISHED’ THEN

          raise report_not_generated;

       END IF;
 — The JavaScript command/string can be used to show a basic browser window
   v_url := ‘/reports/rwservlet/getjobid’||v_jobID||’?server=’||reportserver;
— The following is one long string, carriage returns used for formatting
v_url2 := ‘javascript:window.open(“‘||v_url ||'”, “”, “fullscreen=no,

titlebar=no, location=no, toolbar=no, menubar=no, status=no, resizable=yes”);
— “self.close” shown below is continued from the line above, carriage return

— here for formatting, but it is really one long string
       self.close()’;
— This is the built-in that calls a new browser window
       Web.Show_Document(v_url2,’_blank’);

   ELSE

       raise report_not_generated;

   END IF;
END IF;
— Destroy the parameter list. Report has been generated – it’s no longer needed
Destroy_Parameter_List(p_rpt_param_list);
EXCEPTION

  WHEN report_not_generated THEN
— “am” is a shortcut to call an alert message. If you are raising a lot of alert messages in

— your forms, add a procedure in Program Units. It takes the string you pass in and will

— save you lots of time by not having to keep writing set_alert_property(…).
       am(‘There was an error in running the report.’||chr(10)||

          ‘Contact the Application Server administrator for assistance.’);
  WHEN OTHERS THEN

    user_show_error;
END;

The code above looks like a lot, but the good news is that
the procedure is virtually 100% reusable. If you standardize your Forms and
Reports development environment, all that needs to be changed here is the name
of the report, and even then, you can pass that in to the procedure via the
code behind when-button-clicked for the “Run Report” button. Your calling Form
needs to have a report object added to it (that’s covered in Oracle’s
documentation) in addition to having a “reportserver” parameter (the name of
the report server).