11 Replies Latest reply: Feb 22, 2013 12:19 AM by HamidHelal RSS

    Passing two parameters for reprot

    870213
      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
          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
            Dear HamidHelal Thank you for your reply

            I already tried this code. not working.
            • 3. Re: Passing two parameters for reprot
              HamidHelal
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.