Multi User Parameters to a Report using run_report_object

From: Linda McBrien via OracleMonster.com <forum_at_OracleMonster.com>
Date: Tue, 25 Jan 2005 19:04:19 GMT
Message-ID: <0b074953113b4c8d89fe3a69373daa1b_at_OracleMonster.com>

I am in the process of upgrading our Oracle 6i application to 10g. I keep running into problems with each report I try to convert. It seems that each one has a unique way of working. I am stuck on a particular report where I have tried the following setups:

METHOD 1: DECLARE
repid REPORT_OBJECT;
report_prop VARCHAR2(20);
v_rep VARCHAR2(100);
BEGIN
repid := find_report_object('rep1');
set_report_object_property(repid,report_other,'p1=15 p2=19 paramform=yes'); v_rep := RUN_REPORT_OBJECT(repid);
END; Additional Information:

---

 

If you are referencing bind variables or Forms parameters in the string argument for the report_other property and thus concatenating it from several parts, be careful to leave a space between the single parameters contained in the string.
E.g.:
 set_report_object_property(repid, report_other,

     'p_1='||:employee.name||' '||'p_2='||:employee.hire_date);

METHOD 2: p_text1=' ' p_text2=' PART NUMBER12345678901354' p_text3=' REV1234567 12/31/2005' p_text4=' DRAWING12354 SHOP ORDER12' p_text5=' LOT12345678912 1234567890' p_text6=' COMMENTS1234567891324657981354' p_text7='' p_text8='' p_text9='' p_text10='' p_text11='' p_text12='' p_text13='' p_text14='' p_length=1 p_copies=1 p_width=1.25 v_report='TAG_251' copies='1' p_rpt_key='79' p_database='DHASS' envid='HASS' paramform='NO'

This is a sample of the code forming it: v_rpt_params := v_rpt_params||' p_text1='''||:p_text1||'''';

METHOD 3: Add_Parameter(pl_id,'p_where',text_PARAMETER,'lvl2_work_area in (select lvl2_work_area from epms_rollup where lvl5_work_area = :p_work_area)');

METHOD 4: ws_report_where := ws_report_where||' and lvl3_work_area = '''||:global.lvl3_work_area||''''; ws_report_where := 'transaction_mday >= '||:from_date|| ' and transaction_mday <= '||:to_date||'';

METHOD 5:         pl_id := Create_Parameter_List('tmpdata');

  Add_Parameter(pl_id,'PARAMFORM',text_PARAMETER,'NO'); 
 	Add_Parameter(pl_id,'P_LVL2_WORK_AREA',text_PARAMETER,:lvl2_work_area);
 	Add_Parameter(pl_id,'P_LVL3_WORK_AREA',text_PARAMETER,:lvl3_work_area);
 	Add_Parameter(pl_id,'P_LVL4_WORK_AREA',text_PARAMETER,:lvl4_work_area);
 	Add_Parameter(pl_id,'P_LVL5_WORK_AREA',text_PARAMETER,:lvl5_work_area);
 	Add_Parameter(pl_id,'P_WORK_AREA',text_PARAMETER,:global.work_area);
 	Add_Parameter(pl_id,'P_PROGRAM_ID',text_PARAMETER,:program_id);
 	Add_Parameter(pl_id,'P_FROM_DATE',text_PARAMETER,:from_date);
 	Add_Parameter(pl_id,'P_THRU_DATE',text_PARAMETER,:to_date);
 	Add_Parameter(pl_id,'P_WHERE',text_PARAMETER,ws_report_where);

	rep_server := find_report_server('EPMS');
	rep_id := Find_Report_Object('epms_l2delay');
  set_report_object_property(rep_id, report_server, rep_server);

  set_report_object_property(rep_id, report_other, 'ENVID=EPMS');   v_rep := Run_Report_Object(rep_id, pl_id);

        rep_status := Report_Object_Status(v_rep);   while rep_status in ('RUNNING', 'OPEN_REPORT', 'ENQUEUED') loop

		rep_status := report_object_status(v_rep);
	end loop;
	if (rep_server = 'SERVER_NOT_FOUND') then
	  	set_application_property(cursor_style, 'DEFAULT');
	    message('The report server is not on-line. Contact your EPMS service representative.', acknowledge);
 	    message(' ', no_acknowledge);
	else 
		if (rep_status = 'FINISHED') then
			web.show_document('/reports/rwservlet/getjobid'||substr(v_rep, length (rep_server)+ 2)||
		  '?server='||rep_server||'&destype=screen'||'&pagestream=YES', '_BLANK');
		end if;
	end if;

NOTE: All examples use the parameters and also the standard "set_report" commands.

None of these methods works for the report I am working on now. Does anyone out there have a "fool proof" method that always works?

Linda
linda.j.mcbrien[AT]boeing.com

-- 
Message posted via http://www.oraclemonster.com

Received on Tue Jan 25 2005 - 20:04:19 CET