Hire me on UpWork

Friday, April 27, 2012

How to call a report in Forms 10g ?

Calling reports from forms isn't like forms version 6i, where you can call a report via RUN_PRODUCT.
I will talk another day if i think it's needed. Because Forms version 6i isn't supported by Oracle.com and it's uses is going to decreases.

Lets talk about version 10g Developer Suite. In development environment you need to run report server manually but at Application Server it's not needed.

How can we run report server manually ?
Just go to start menu >> run and type    
rwserver SERVER=myserver
Where myserver is the server name.
Now reports server runs.
 
In your form under Reports Node create an report object and give name MYREPORT
(Select Reports Node click on Create Icon(+), select Use Existing Report File then give
 a name in the box and click OK.
Re-name it to MYREPORT go to property and delete FILENAME property value.)  
  
Make changes(Report Name) on following code and  try this in a Button trigger 
 
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;
 
Updated: 20/January/2013
  
For more Click Here
 
Hope this helps you all. 

13 comments:

  1. Salam, to call report in 10g, do we need to configure any file?

    ReplyDelete
  2. Walikum as salam o rahmatillah.

    Sorry for delay as for Eid Festival. No need to configure any other file. But at testing PC, you need to give a report server name and run the server.

    Thanks

    ReplyDelete
  3. thanks for reply = ) can you elaborate more?

    ReplyDelete
  4. @ oracleuser. Specifically what you want to know ?

    ReplyDelete
  5. Iam using forms 6i,reports 6i,but my question is I am calling the report from form(custom form). Report is having parameters also. its calling the reprot When I click on the button ,but i am unable to see the parameter data in report.
    my code is
    DECLARE
    pl_id100 ParamList;
    the_param varchar2(15):='CLIENT';
    BEGIN

    if :PROVISIONAL.BRAND='SREEVARI' THEN
    IF :PROVISIONAL.final_inv_num IS NOT NULL THEN
    -- Destroy_Parameter_List(pl_id);
    PL_ID100:=GET_PARAMETER_LIST(THE_PARAM);
    IF NOT ID_NULL(PL_ID100) THEN
    DESTROY_PARAMETER_LIST(PL_ID100);
    END IF;
    PL_ID100:=CREATE_PARAMETER_LIST(THE_PARAM);--'CLIENT');
    ADD_PARAMETER(PL_ID100,'DNO',TEXT_PARAMETER,:PROVISIONAL.final_inv_num);
    MESSAGE('INVOICE NUMBER--'||:PROVISIONAL.final_inv_num);
    Run_Product(REPORTS, 'D:\svct backup\sct\INVOICE-SVCT-PROV',SYNCHRONOUS,RUNTIME,FILESYSTEM,PL_ID100 , NULL);
    Destroy_Parameter_List(pl_id100);
    ELSE
    MESSAGE(' PLEASE ENTER INVOICE NUMBER.....');
    END IF;
    END IF;



    END;

    ReplyDelete
  6. as salam alakum Md. Hamidur Rahman Siddique, thank you to put this procedure on the web .. is very useful for me ,,,

    ReplyDelete
  7. AOA,

    as you wrote the procedure to call report from Form, what about KILLJOBID ? is not required in 10g or something else.

    regards.

    ReplyDelete
  8. Hello Md. Hamidur Rahman Siddique.

    Is there a way to pass other different user from Forms 10g to Reports 10g? I mean, if I've connected to my app with X user, how I pass a different user of X to Reports?

    I've tried using:
    SET_REPORT_OBJECT_PROPERTY ('ny_report', REPORT_OTHER, 'paramform=no user_id=other_user/pwd@database');

    But it doesn't work.

    Could you help me please?

    ReplyDelete
  9. Not sure, didn't try. @ amlangle aml

    ReplyDelete
  10. salam alikum bro!!
    i did all the above but its bot runnig and giving the following msg:
    REP-51002: Bind to Reports Server obs failed


    how to solve it bro...

    ReplyDelete
  11. I want to call report from menu.(Oracle 10g). Can u show how?

    ReplyDelete