This discussion is archived
13 Replies Latest reply: Feb 21, 2013 2:14 AM by HamidHelal RSS

how to run report with parameters from oracle forms 10g

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2 , xp
hi all ,
i can run report from forms builder with this code :
DECLARE
 
v_repid REPORT_OBJECT;
v_rep VARCHAR2(100);
v_rep_status VARCHAR2(100);
v_param VARCHAR2(200) := NULL;
v_valor VARCHAR2(200);
v_url VARCHAR2(2000);
v_repserver varchar2(20) := 'myserver';
 
v_report varchar2(100) := 'D:\REPORT_NAME.REP';
v_PARAMETRO varchar2(100) := '';
 
BEGIN
 
v_repid := FIND_REPORT_OBJECT('MYREPORT'); -- report is an element from object navigator report
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_FILENAME, v_report);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESTYPE, cache);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESFORMAT, 'pdf' );
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESNAME, v_report);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_SERVER, v_repserver);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
v_rep := RUN_REPORT_OBJECT(v_repid);
v_rep_status := REPORT_OBJECT_STATUS(v_rep);
WHILE v_rep_status IN ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP
v_rep_status := REPORT_OBJECT_STATUS(v_rep);
END LOOP;
IF v_rep_status = 'FINISHED' THEN
message(v_rep);
message(v_rep);
WEB.SHOW_DOCUMENT(v_url||'/reports/rwservlet/getjobid'||
SUBSTR(v_rep, INSTR(v_rep,'_', -1)+1)||'?'||'server='||v_repserver, '_blank');
END IF;
 
END;
but i want to run a report with parameter , that is my problem .

what i am thinking of is to create a parameter list "tmp" and add a parameter to it "p_ename" which holds the value of a text item ":t_ename" with which i want to run my report , then to add the parameter list parameter to the
run_report_object(myreport,"parameter list") ;
then
adds a user parameter with the name "p_ename" to my report in reports builder then test it , but i did that , and nothing happens , it runs the report with the whole data , not just with the text item value's data ?

how to resolve this?
thanks
  • 1. Re: how to run report with parameters from oracle forms 10g
    Christian Erlinger Guru
    Currently Being Moderated
    Did you take a look at the documentation; e.g. here: http://docs.oracle.com/cd/E15523_01/doc.1111/e10394/repgrph.htm#BHBBEECA
    where exactly the thing you want is done in the sample?

    cheers
  • 2. Re: how to run report with parameters from oracle forms 10g
    Michael Ferrante (Oracle) Guru Moderator
    Currently Being Moderated
    Also, be aware that we have updated the Forms/Reports Integration paper. Although it has been named with references to 11g, the concept for version 10 is the same. The paper can be found here:

    http://www.oracle.com/technetwork/developer-tools/forms/documentation/techlisting11gr1-086315.html
  • 3. Re: how to run report with parameters from oracle forms 10g
    HamidHelal Guru
    Currently Being Moderated
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
    for example
    declare
    QT char(1):='''';
    begin
    .........
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no UNIT='||QT||:TXT_CID_ID||QT||' VOUCHER_TYPE='||QT||:TXT_VOUCHER_TYPE||QT);
    ........     
    Hope this helps

    Hamid

    Edited by: HamidHelal on Feb 19, 2013 7:48 AM
  • 4. Re: how to run report with parameters from oracle forms 10g
    Amatu Allah Guru
    Currently Being Moderated
    Hi
    pls have a look at the white paper which offers a working way to accomplish calling a Report which uses a Reports parameter form. 

    http://www.oracle.com/technetwork/developer-tools/forms/frmrepparamform-128021.pdf

    Amatu Allah
  • 5. Re: how to run report with parameters from oracle forms 10g
    newbi_egy Explorer
    Currently Being Moderated
    hamidhelal
    for example
    declare
    QT char(1):='''';
    begin
    .........
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no UNIT='||QT||:TXT_CID_ID||QT||' >VOUCHER_TYPE='||QT||:TXT_VOUCHER_TYPE||QT);
    ........
    what is voucher_type and :txt_cid_id ? if you could modify my code please ??? , i am using scott/tiger schema .
    amatuallah
    http://www.oracle.com/technetwork/developer-tools/forms/frmrepparamform-128021.pdf
    this link provide the way to run the report without parameter , and this is what i posted from the beginning .

    thanks
  • 6. Re: how to run report with parameters from oracle forms 10g
    Christian Erlinger Guru
    Currently Being Moderated
    what is voucher_type and :txt_cid_id ? if you could modify my code please ??? , i am using scott/tiger schema .
    The sample in the documentation I gave you a link is using the scott/tiger schema.

    you have to pass the parameter list in the form
    'parameter_name_in_report=value'
    where you certainly have to enclose alphanumeric values with single quotes which you'd have to escape.

    cheers
  • 7. Re: how to run report with parameters from oracle forms 10g
    Amatu Allah Guru
    Currently Being Moderated
    sorry
     ============================================
    
    Begin
    
    PARAM_ID := GET_PARAMETER_LIST(PARAM_NAME);
    
    IF NOT ID_NULL(PARAM_ID) THEN
    DESTROY_PARAMETER_LIST('REPORT_PARAMETER');
    END IF;
    
    
    PARAM_ID := CREATE_PARAMETER_LIST(PARAM_NAME);
    ADD_PARAMETER(PARAM_ID,'PARAMFORM',TEXT_PARAMETER,'NO'); 
    
    ADD_PARAMETER(PARAM_ID,'COPIES',TEXT_PARAMETER,:COPIES);
    ADD_PARAMETER(PARAM_ID,'P_DISPLAY',TEXT_PARAMETER,:DESTYPE);
    ADD_PARAMETER(PARAM_ID,'DESFORMAT',TEXT_PARAMETER,'pdf'); 
    
    
    ADD_PARAMETER(PARAM_ID,'P_DATA_TYPE',TEXT_PARAMETER,:PERIOD_MONTH);
    ....
    ....
    u need to specify which parameter u Will pass cryeate them first in report with its corresponding datatype in forms u Will need after creating parameter list to add them one by one with the same name &datatype specified in report ..

    in forms they r just text item non db in the form's button pl/sql code is added as u did the piece of code u r missing is above customize it the way u want, forms input is

    occurred by user the
  • 8. Re: how to run report with parameters from oracle forms 10g
    newbi_egy Explorer
    Currently Being Moderated
    Amatu Allah      sorry
    i am so sorry amatu allah , but i feel so confused this way, i do not know what is :copies , :destype and :period_month , i think :destype is a property of my report , i setting it programmatically or in the property palette ,and there is a conflict in my head .

    please , i am using scott/tiger schema , with a text item and a button .............. , like i said before at the first post and mentioned their names too . and mentioned the parameter list too .
    - i modified my code now to this
    DECLARE
     
    v_repid REPORT_OBJECT;
    v_rep VARCHAR2(100);
    v_rep_status VARCHAR2(100);
    v_param VARCHAR2(200) := NULL;
    v_valor VARCHAR2(200);
    v_url VARCHAR2(2000);
    v_repserver varchar2(20) := 'step';
     
    v_report varchar2(100) := 'E:\MYREPORT.JSP';
    v_PARAMETRO varchar2(100) := '';
     pl paramlist ; 
    BEGIN
     pl := get_parameter_list('tmp') ; 
     if not id_null(pl) 
          then
          destroy_parameter_list('tmp') ; 
          else  
          pl := create_parameter_list('tmp') ; 
          end if ; 
          add_parameter('tmp','p_ename',text_parameter,:t_ename) ; 
          
    v_repid := FIND_REPORT_OBJECT('REPORT9'); -- report is an element from object navigator report
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_FILENAME, v_report);
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_EXECUTION_MODE, BATCH);
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_COMM_MODE, SYNCHRONOUS);
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESTYPE, cache);
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESFORMAT, 'pdf' );
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESNAME, v_report);
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_SERVER, v_repserver);
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
    v_rep := RUN_REPORT_OBJECT(v_repid,'tmp');
    v_rep_status := REPORT_OBJECT_STATUS(v_rep);
    WHILE v_rep_status IN ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP
    v_rep_status := REPORT_OBJECT_STATUS(v_rep);
    END LOOP;
    IF v_rep_status = 'FINISHED' THEN
    message(v_rep);
    message(v_rep);
    WEB.SHOW_DOCUMENT(v_url||'/reports/rwservlet/getjobid'||
    SUBSTR(v_rep, INSTR(v_rep,'_', -1)+1)||'?'||'server='||v_repserver, '_blank');
    END IF;
     
    END;
    then created the bind parameter ":p_ename" on the report like "select ename from emp where ename = :p_ename"
    and modified its data type to char .
    but when i press the button it show the whole data of the "ename" column not just the name i put into the :t_ename text item
    ?

    Edited by: semsem on Feb 20, 2013 3:03 AM
  • 9. Re: how to run report with parameters from oracle forms 10g
    newbi_egy Explorer
    Currently Being Moderated
    i've seen your example chris , and it's very good and simple but i can not test it ., i modified it to
    DECLARE
     v_report_id              Report_Object;
     vc_report_job_id                  VARCHAR2(100);  /* unique id for each Report      request */
     vc_rep_status                    VARCHAR2(100);           /* status of the Report job */
              
     BEGIN
            
            v_report_id:= FIND_REPORT_OBJECT('report7');
            SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_COMM_MODE, SYNCHRONOUS);
            SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESTYPE,CACHE);
     
     
            SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESFORMAT, 'PDF');
            
            SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_SERVER, 'stepdev');
            SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
            
            vc_report_job_id:=RUN_REPORT_OBJECT(v_report_id);
     
     
     
            vc_rep_status := REPORT_OBJECT_STATUS(vc_report_job_id);
            IF vc_rep_status='FINISHED' THEN
     
     web.show_document ('http://stepdev:8889/reports/rwservlet/getjobid='||
     vc_report_job_id ||'?server='|| 'stepdev','_blank');
            ELSE
            message ('Report failed with error message '||vc_rep_status);
            END IF;
    END;
    it ends with "unable to run report" message , and 'report failed with error message terminated with error'
    ??
    ?

    if any one could help with this example
  • 10. Re: how to run report with parameters from oracle forms 10g
    Michael Ferrante (Oracle) Guru Moderator
    Currently Being Moderated
    An unable to run report error means that there is a problem when RRO is called. It can be either on the Forms or Reports side. The easiest/fastest way to figure out which side the problem is occurring it to expose the currently recieved jobs sent to the Rep server. If your job was recieved but was unable to complete you will see a red X icon in the table. Clicking on will provide you with the details. For example, unable to connect to the db, invalid parameter/value, etc.

    However, I have these questions:
    <blockquote>1. What is the name of the host where Reports installation lives?
    2. What is the name of the Reports server? This is not and should not be the same as the host name.</blockquote>
    You should not have the host and rep server name the same
    <blockquote><li>SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_SERVER, +'stepdev'+ );
    <li>http:// stepdev* :8889/reports/rwservlet .... </blockquote>
    As for looking at the recent jobs, do this:

    http://stepdev:8889/reports/rwservlet/showjobs?server=<ENTER THE REPORTS SERVER NAME AND NOT THE HOST NAME>
  • 11. Re: how to run report with parameters from oracle forms 10g
    HamidHelal Guru
    Currently Being Moderated
    SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
    modify the above line with bellow one
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no p_deptno='||QT||:dept.deptno||QT); --- remember to add QT at your declaration
    QT char(1):=''''; -- only at declaration section if you want to add single quote(')
    and place it just place of
    SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
    at your first posted code

    Hope this works.. :)

    Hamid
  • 12. Re: how to run report with parameters from oracle forms 10g
    newbi_egy Explorer
    Currently Being Moderated
    thank to you all ,
    special thanks for HamidHelal ,
    but what is the need of "QT char(1):='''';"
    i've tried it without QT and it worked even when i changed the parameter to "ename" -- a character column not a number like deptno ?

    and if you could tell me how to work if i need to specify two or three parameters not one ?


    thanks a lot
  • 13. Re: how to run report with parameters from oracle forms 10g
    HamidHelal Guru
    Currently Being Moderated
    semsem wrote:
    thank to you all ,
    special thanks for HamidHelal ,
    but what is the need of "QT char(1):='''';"
    Hi semsem,
    instead of using single quote i use QT, it's easier to me if i forget to add single quote
    QT is just replace of '''' /'
    i've tried it without QT and it worked even when i changed the parameter to "ename" -- a character column not a number like deptno ?
    For number datatype, you didn't need of single quote or QT but for character and date you must need it. but in both data type you can use.


    >
    and if you could tell me how to work if i need to specify two or three parameters not one ?
    for multiple parameter my first example is right. Again i paste it
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no UNIT='||QT||:TXT_CID_ID||QT||' VOUCHER_TYPE='||QT||:TXT_VOUCHER_TYPE||QT);
    --UNIT AND VOUCHER_TYPE is parameter name. and can add more like this way.
    Hope this helps..

    Hamid

    Edited by: HamidHelal on Feb 21, 2013 4:14 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points