This discussion is archived
11 Replies Latest reply: Feb 21, 2013 10:19 PM by HamidHelal RSS

Passing two parameters for reprot

870213 Newbie
Currently Being Moderated
oracle 10g developer suite

I am running report through 10g form with one parameter it is working fine.
but I want to pass two parameters . I tried but not succeeded.

1. p_date=' || :btn.at_date
2. p_date1=' || :btn.at_date1
------- With One Parameter ------------
DECLARE 
repid REPORT_OBJECT;
v_rep VARCHAR2(100);
rep_status VARCHAR2(20);
 
BEGIN
 
repid := FIND_REPORT_OBJECT('REPORT1');
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESTYPE,cache);
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESFORMAT,'PDF');
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER, 'paramform=no p_date=' || :btn.at_date);
 
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
  WEB.SHOW_DOCUMENT('/reports/rwservlet/getjobid'||substr(v_rep,instr(v_rep,'_',-1)+1) ||
  '?server=rserver' ); 
else
  message ( 'error while running reports-object '  || error_text );
  message ( ' ' );
  clear_message;
end if;
end;
I tried this but blank report.
QT char(1):='''';
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no P_date='||QT||:btn.at_date||QT||' p_date1='||QT||:btn.at_date1||QT); 
Edited by: 867210 on Feb 21, 2013 3:24 AM
  • 1. Re: Passing two parameters for reprot
    HamidHelal Guru
    Currently Being Moderated
    try this
    DECLARE 
    QT char(1):='''';
    repid REPORT_OBJECT;
    v_rep VARCHAR2(100);
    rep_status VARCHAR2(20);
     
    BEGIN
     
    repid := FIND_REPORT_OBJECT('REPORT1');
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_COMM_MODE,SYNCHRONOUS);
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESTYPE,cache);
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESFORMAT,'PDF');
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no P_date='||QT||:btn.at_date||QT||' p_date1='||QT||:btn.at_date1||QT);
     
    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
      WEB.SHOW_DOCUMENT('/reports/rwservlet/getjobid'||substr(v_rep,instr(v_rep,'_',-1)+1) ||
      '?server=rserver' ); 
    else
      message ( 'error while running reports-object '  || error_text );
      message ( ' ' );
      clear_message;
    end if;
    end;
    Hope this works.. :)

    Edited by: HamidHelal on Feb 21, 2013 5:42 PM
  • 2. Re: Passing two parameters for reprot
    870213 Newbie
    Currently Being Moderated
    Dear HamidHelal Thank you for your reply

    I already tried this code. not working.
  • 3. Re: Passing two parameters for reprot
    HamidHelal Guru
    Currently Being Moderated
    867210 wrote:
    Dear HamidHelal Thank you for your reply

    I already tried this code. not working.
    what the error you get ?

    with you parameter value, does the reports works at report builder ?
  • 4. Re: Passing two parameters for reprot
    870213 Newbie
    Currently Being Moderated
    There is no error. showing report blank no rows.
    in report builder report working .

    I already checked with one parameter report working but when I use two parameters than blank report.
  • 5. Re: Passing two parameters for reprot
    HamidHelal Guru
    Currently Being Moderated
    867210 wrote:
    There is no error. showing report blank no rows.
    There is NO issue with parameter passing.
    in report builder report working .
    Did you checked with two parameter ? and how you use it in the query ?
    I already checked with one parameter report working but when I use two parameters than blank report.
    what is the parameter data type value filed data type ?

    1. p_date=' || :btn.at_date --same data type ?
    2. p_date1=' || :btn.at_date -- same data type ?
    and how you using the parameter ?

    Hope this helps

    Hamid
  • 6. Re: Passing two parameters for reprot
    870213 Newbie
    Currently Being Moderated
    in Report P_date and p_date1 data type is date and i set format mask DD-MM-RRRR

    and in form at_date and at_date1 data type is date format mask DD-MM-RRRR

    I have checked . do not know what is the prob. :S
  • 7. Re: Passing two parameters for reprot
    HamidHelal Guru
    Currently Being Moderated
    867210 wrote:
    in Report P_date and p_date1 data type is date and i set format mask DD-MM-RRRR

    and in form at_date and at_date1 data type is date format mask DD-MM-RRRR

    I have checked . do not know what is the prob. :S
    You cannot answer, with two date value in reports builder, does it works ?

    Can you contact with me with my profile mail address ?

    Hamid
  • 8. Re: Passing two parameters for reprot
    870213 Newbie
    Currently Being Moderated
    HamidHelal wrote:
    867210 wrote:
    in Report P_date and p_date1 data type is date and i set format mask DD-MM-RRRR

    and in form at_date and at_date1 data type is date format mask DD-MM-RRRR

    I have checked . do not know what is the prob. :S
    You cannot answer, with two date value in reports builder, does it works ?

    Can you contact with me with my profile mail address ?

    Hamid
    yes Report builder is working fine with two date value.
  • 10. Re: Passing two parameters for reprot
    870213 Newbie
    Currently Being Moderated
    Solved this issue
      SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no sdate='||:btn.sdate|| ' ' || 'edate='||:btn.edate);  
    Edited by: 867210 on Feb 21, 2013 9:51 PM
  • 11. Re: Passing two parameters for reprot
    HamidHelal Guru
    Currently Being Moderated
    867210 wrote:
    Solved this issue
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no sdate='||:btn.sdate|| ' ' || 'edate='||:btn.edate);  
    Edited by: 867210 on Feb 21, 2013 9:51 PM
    I always add space on the side of parameter.. Like my example also work..
    SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no sdate='||:btn.sdate||' edate='||:btn.edate);  
    Mark as answered.

Legend

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