Multi User Parameters to a Report using run_report_object

[Linda McBrien via 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