[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

WebForms calling WebReports

Posted on 2004-10-27
9
Medium Priority
?
4,911 Views
Last Modified: 2007-12-19
Hello,

I have read that system parameters cannot be passed to a parameter list when Forms call Reports over the Web in Oracle 9i.
After using the FMA to migrate my code from ORacle 6i to 9i, I made the following modificaitons to allow me to print my reports over the WEb to my Network Printer installed on my Linux server.

HEre is the sample code:
-----
         ---repid := find_report_object('btprtinv');
            ---add_parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'YES');
            --System Parameters not part of Parameter List
            --add_parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'PRINTER');
            --add_parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'PRINTER');
            ---add_parameter(pl_id,'COPIES',TEXT_PARAMETER,'2');
            --add_parameter(pl_id,'DESNAME',TEXT_PARAMETER,'Lexmark Upper Tray');
            --add_parameter(pl_id,'DESNAME',TEXT_PARAMETER,'lxut');
         ---set_report_object_property(repid, report_destype, printer);
         ---set_report_object_property(repid, report_desname, 'lexmark lower tray');

        ---set_report_object_property(PARAMETER.rp2rrodestype,report_destype,printer);
        ---set_report_object_property(PARAMETER.rp2rrodesname,report_desname,'lexmark lower tray');

            ---add_parameter(pl_id,'P_PRINT_DATE',TEXT_PARAMETER,to_char(:variable.print_date));
            ---add_parameter(pl_id,'P_PROJECT',TEXT_PARAMETER,:variable.project);
            ---add_parameter(pl_id,'P_CO',TEXT_PARAMETER,:company.co);
            ---add_parameter(pl_id,'P_SURVEYOR',TEXT_PARAMETER,to_char(:variable.surveyor));
            
            ---rp2rro.rp2rro_run_product(REPORTS,'btprtinv',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id,null);
         
         repid := find_report_object('btprtinv');
         set_report_object_property(repid, report_destype, printer);
         set_report_object_property(repid, report_desname, 'lxut');
    set_report_object_property(repid, report_other, 'paramform=yes ' || 'copies=2' ||
                               'P_PRINT_DATE=' || to_char(:variable.print_date) ||
                               'P_PROJECT=' || :variable.project ||
                               'P_CO=' || :company.co ||
                               'P_SURVEYOR=' || to_char(:variable.surveyor));
            
    v_rep := run_report_object(repid);
    rep_status := report_object_status(v_rep);
         
     while rep_status in ('RUNNING', 'OPENING_REPORT', 'ENQUEUED') loop
        rep_status := report_object_status(v_rep);
     end loop;

     if rep_status = 'FINISHED' then
        -- display the report in the browser
        rep_url := 'http://Linux2005:7780/reports/rwservlet/getjobid' ||
                   substr(v_rep, instr(v_rep, '_', -1) +1) ||
                   '?'||'server=rep_Linux2005';
        web.show_document(rep_url, '_blank');
     else
        message('Error when running the report', 1);
     end if;
            
  end if;

  destroy_parameter_list(pl_id);
-----
I cannot compile the instructions report_object_status.
1) Any ideas why ?

This is how my variables are initialised:

    pl_id      PARAMLIST;
    repid report_object;
    v_rep VARCHAR2(100);
    rep_status VARCHAR2(100);
    rep_url varchar2(500);

2) I cannot invoke/run my report in my Oracle 9i iDS.
I had noticed that I was making referenc yo a reports server called RepSRV which I launched.
I check that my report called btprtinv.rep exists.

3) How can I go about resolving this issue ?

PT
0
Comment
Question by:ptreves
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12430573
I think that this line ought to be
v_rep := run_report_object(repid,pl_id);
instead of
v_rep := run_report_object(repid);

You can also increase v_rep and rep_status to VARCHAR2(200);
0
 

Author Comment

by:ptreves
ID: 12435706
Hello,

Could the problem be related to the fact that maybe v_rep is NULL and not referencing the proper Report node ?

PT
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12441472
Yes, of course.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ptreves
ID: 12452948
Hello,

I have been working on the Forms invoking Reports over the Web part of my application.
I set the system paramters DESTYPE and DESNAME with the set_system as follows before setting variables through the parameter list:
-----
/*******************************************
   Code modified by the Forms Migration Assistant
   12-Sep-2004 03:07 PM
 *******************************************/
PROCEDURE Print_transaction IS
   pl_id PARAMLIST;
   repid report_object;
    v_rep VARCHAR2(200);
    rep_status VARCHAR2(200);
    rep_url varchar2(500);
BEGIN
 pl_id := Get_parameter_list('TRANSACTION');
 if not id_null(pl_id)
 then destroy_parameter_list(pl_id);
 end if;
 ---'repid := find_report_object('btreppro');
 repid := find_report_object('BTPRTINV');
 pl_id := Create_parameter_list('TRANSACTION');
    set_report_object_property(repid, report_comm_mode, synchronous);
    set_report_object_property(repid, report_execution_mode, batch);
    set_report_object_property(repid, report_destype, cache);
    set_report_object_property(repid, report_desname, 'PDF');
    --set_report_object_property(repid, report_desname, 'Lexmark Upper Tray');
    set_report_object_property(repid, report_server, 'RepSRV');
    ---set_report_object_property(repid, report_other, 'paramform=yes ' || 'copies=2' ||
    ---                           'P_PRINT_DATE=' || to_char(:variable.print_date) ||
    ---                           'P_PROJECT=' || :variable.project ||
    ---                           'P_CO=' || :company.co ||
    ---                           'P_SURVEYOR=' || to_char(:variable.surveyor));
 add_parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');
 --add_parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'printer');
 --add_parameter(pl_id,'DESNAME',TEXT_PARAMETER,'Lexmark Upper Tray');
 add_parameter(pl_id,'COPIES',TEXT_PARAMETER,'1');
 ----add_parameter(pl_id,'P_PRINT_DATE',TEXT_PARAMETER,to_char(:variable.print_date));
 ----add_parameter(pl_id,'P_PROJECT',TEXT_PARAMETER,:variable.project);
 ----add_parameter(pl_id,'P_CO',TEXT_PARAMETER,:company.co);
-- add_parameter(pl_id,'P_SURVEYOR',TEXT_PARAMETER,nvl(to_char(:variable.surveyor),'0'));
 --- rp2rro.rp2rro_run_product(REPORTS,'btreppro.rep',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id,null);
 
    message('Ok, Parameter List + Report Object Set', 1);
    v_rep := run_report_object(repid, pl_id);
    if v_rep is not null then
     rep_status := report_object_status(v_rep);
         
      message('Report Status Loop....Wait....', 1);
      while rep_status in ('RUNNING', 'OPENING_REPORT', 'ENQUEUED') loop
        rep_status := report_object_status(v_rep);
      end loop;
      message('Ok, Trying to run over the Web', 1);
      if rep_status = 'FINISHED' then
        -- display the report in the browser
        rep_url := 'http://terrain2004:8888/reports/rwservlet/getjobid' ||
                   substr(v_rep, instr(v_rep, '_', -1) +1) ||
                   '?'||'server=RepSRV';
        web.show_document(rep_url, '_blank');
      elsif rep_status = 'TERMINATED_WITH_ERROR' then
        message('TERMINATED_WITH_ERROR, Error when running the report', 1);
      else
        message('Some other Error when running the report', 1);
      end if;
    else
      message('Report object not valid', 1);
    end if;
 destroy_parameter_list(pl_id);
END;

=====

When I run this code, for some reason the run_report_object() and the report_object_status() function do not return proper values.

I checked under the report node, I have 2 nodes: RP2RRO and BTPRTINV.

1) From those 2 nodes, can I call 10 different reports ?
(Shouldn't each different report have a seperate node to be referenced ?)

Of the 10 reports to be called, 2 make reference/invoke a parameter form.
2) Are there additional settings to be made for the parameter form ?

Also, when attempting to debug my code, i noticed that the v_rep variable took the value of RepSRV_0 when I had explicitly set it to RepSRV in the source code.
3) Could this be a source of problem ?

PT

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12470371
You can use only one report object for each report. Here is my code (it is OUR_DUMMY_REPORT):

PROCEDURE SPUSTI_REPORT IS
 pl_id           ParamList;
 pl_name         VARCHAR2(10) := 'tempdata';
 report_id       Report_Object;
 report_job_id   VARCHAR2(200);
 rep_status      VARCHAR2(200);
 alert_button    PLS_INTEGER;
 al_id               Alert:= Find_Alert('chyba');
 vc_dummy_report Varchar2(20):='OUR_DUMMY_REPORT';
 L_REPORT                ZOSTAVY.REPORT%TYPE;
 L_REPORT_DESFORMAT      ZOSTAVY.REPORT_DESFORMAT%TYPE;
 L_REPORT_SERVER         ZOSTAVY.REPORT_SERVER%TYPE;
 L_REPORT_SERVER_PATH    ZOSTAVY.REPORT_SERVER_PATH%TYPE;

BEGIN
 pl_id := Get_Parameter_List(pl_name);
 IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List( pl_id );
 END IF;
 pl_id := Create_Parameter_List(pl_name);
 IF Id_Null(pl_id) THEN
    Set_Alert_Property(al_id,alert_message_text,'Nepodarilo sa vytvoriť súbor parametrov potrebný pre spustenie zostavy.Končím spúšťanie zostavy.' );
    alert_button := Show_Alert(al_id);
    RAISE Form_Trigger_Failure;
 END IF;

....
  Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');

  report_id:= FIND_REPORT_OBJECT(vc_dummy_report);

  BEGIN
   Select z.REPORT,z.REPORT_DESFORMAT,z.REPORT_SERVER,z.REPORT_SERVER_PATH
   INTO L_REPORT,L_REPORT_DESFORMAT,L_REPORT_SERVER,L_REPORT_SERVER_PATH FROM ZOSTAVY z WHERE z.id=:Z3_ZOSTAVY_UKAZ.ID;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
     Set_Alert_Property(al_id,alert_message_text,'ZOSTAVU sa nepodarilo vytvoriť.');
     alert_button := Show_Alert(al_id);
     return;
  END;

  SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,L_REPORT); -- report ZOZNAM_ZAKAZNIKOV
  SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);

  IF Get_Application_Property(USER_INTERFACE)='WEB' THEN
     SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,BATCH);
     SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,CACHE);
     SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,L_REPORT_DESFORMAT);  --'html'
     SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,L_REPORT_SERVER);     --'RepBA'

     report_job_id := RUN_REPORT_OBJECT(report_id,pl_id);

     rep_status:=REPORT_OBJECT_STATUS(report_job_id);

     WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
     LOOP
         rep_status := report_object_status(report_job_id);
     END LOOP;
     IF rep_status='FINISHED' THEN
        Web.Show_Document(L_REPORT_SERVER_PATH||'/dev60cgi/rwcgi60.exe/getjobid='||report_job_id||'?server='||L_REPORT_SERVER);
     ELSE
        Set_Alert_Property(al_id,alert_message_text,'ZOSTAVU sa nepodarilo vytvoriť.');
        alert_button := Show_Alert(al_id);
     END IF;
   ELSE
     SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
     SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,PREVIEW);
     Add_Parameter(pl_id,'BLANKPAGES', TEXT_PARAMETER, 'NO');
     Add_Parameter(pl_id,'MAXIMIZE', TEXT_PARAMETER, 'YES');

     report_job_id := RUN_REPORT_OBJECT(report_id,pl_id);
   END IF;
END SPUSTI_REPORT;

Maybe the problem is in your report objects RP2RRO and BTPRTINV. What is the definition of these objects ?

The properties of my OUR_DUMMY_REPORT are:
Name = OUR_DUMMY_REPORT
Execution Mode = Batch
Communication Mode = Synchronous
Report Destination Type = File
0
 

Author Comment

by:ptreves
ID: 12478512
Hello,

In my case, I have a Parameter Form to generate with my Report. Before I generate my report I have to display a Parameter form. This involves defining 3 extra user parameters in my Report: P_ACTION, P_USER_CONNECT and P_SERVERNAME.

With each report I also have about 4 or 5 user parameters to pass.

1) Do you have a good example of this ?

I understand from the oracle documentation that I have to modify my code on the Forms side and then on the Reports side. On the report side, I have to enter some code to generate the HTML ACTION attibute in the BEFORE PARAMETER FORM trigger.

2) Your toughts ....

PT
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12480507
I have no experiences with generating the HTML ACTION attibute, so I cannot advice.
0
 

Author Comment

by:ptreves
ID: 12482592
Hello,

Any idea where I could get some more information about this ?

PT


0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 1500 total points
ID: 12482629
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question