14 Replies Latest reply: Mar 25, 2013 7:52 PM by salute-Salem RSS

    running report from forms 10g with parameters

    salute-Salem
      db and dev 10g ,
      hi all , my report displays "sal and ename" columns of scott/tiger schema .
      i've seen a lot of articles talking about this but i want you to help me to get it :

      1- i want to run report by assigning a value to the "report other" property of the report object .
      i've tried it like so , i wrote sal = 800 or ename = 'SCOTT' in that property "report other" . and it did not work .

      2- and i want to how to do the same with "set_report_object('myrep',report_other,..........) ;

      3- i did it before with a parameter list by creating a parameter in the report builder called "p_sal" and p_ename then
      created a parameter list with :sal and :ename text_items as parameters then added the parameter list to
      run_report_object('myreport',pl) and it did not work too .

      please clarify these to me

      thanks a lot
        • 1. Re: running report from forms 10g with parameters
          HamidHelal
          newbi_egy wrote:
          db and dev 10g ,
          hi all , my report displays "sal and ename" columns of scott/tiger schema .
          i've seen a lot of articles talking about this but i want you to help me to get it :

          1- i want to run report by assigning a value to the "report other" property of the report object .
          i've tried it like so , i wrote sal = 800 or ename = 'SCOTT' in that property "report other" . and it did not work .

          2- and i want to how to do the same with "set_report_object('myrep',report_other,..........) ;

          3- i did it before with a parameter list by creating a parameter in the report builder called "p_sal" and p_ename then
          created a parameter list with :sal and :ename text_items as parameters then added the parameter list to
          run_report_object('myreport',pl) and it did not work too .

          please clarify these to me
          Hello,
          try <a href="http://hamid-oracle.blogspot.com/2012/04/how-to-call-report-in-forms-10g.html">How to call a report in Forms 10g ? </a>

          Hope this works..
          • 2. Re: running report from forms 10g with parameters
            salute-Salem
            thanks hamid , but i think this article is talking about running a report without parameter , and it does not talk about the three ways i mentioned
            • 3. Re: running report from forms 10g with parameters
              HamidHelal
              newbi_egy wrote:
              thanks hamid , but i think this article is talking about running a report without parameter , and it does not talk about the three ways i mentioned
              No, there also have the paramete issues.
              Don't worry check {thread:id=2501702}

              Hope this helps
              • 4. Re: running report from forms 10g with parameters
                salute-Salem
                should i create parameters in the reports builder to hold the values passed ? in the three ways ?
                • 5. Re: running report from forms 10g with parameters
                  HamidHelal
                  newbi_egy wrote:
                  should i create parameters in the reports builder to hold the values passed ? in the three ways ?
                  Yes. Of course. also same data type.
                  • 6. Re: running report from forms 10g with parameters
                    GregorM
                    Hi.

                    My experience is to go with set_report_object('myrep',report_other,..........) option. The problem could be in parameters you are passing. It is always good practice to enclose parameter values in double quotes. For example :

                    l_other_params := l_other_params || 'PAR1="' || 'VAL1' || '" ';
                    l_other_params := l_other_params || 'PAR2="' || 'VAL2' || '" ';
                    .....
                    set_report_object('myrep',report_other,l_other_params);
                    .....

                    Sure you will have to define all of the passed parameter names in your report as user parameters.

                    Best regards.
                    • 7. Re: running report from forms 10g with parameters
                      salute-Salem
                      let us talk about my second requirement :
                      2- and i want to how to do the same with "set_report_object('myrep',report_other,..........) ;
                      -- my form has scott.dept block and a button with the code below to run the report with p_deptno parameter .
                      -- the report runs well without parameter .
                      -- in my report i created a bind parameter through my query which is
                      "select deptno , ename , sal from emp where deptno = :p_deptno" and it created successfully . with datatype "number"
                      {code}
                      declare
                           ro report_object ;
                           v varchar2(90) ;
                      begin
                           
                           ro := find_report_object('report2') ;
                           
                      SET_REPORT_OBJECT_PROPERTY(ro,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
                           
                           v := run_report_object(ro) ;
                           
                           
                           web.show_document('http://rocky:8889/reports/rwservlet/getjobid'||substr(v,instr(v,'_')+1)||'?server=rocky','_blank') ;
                           
                           end ;
                      {code}
                      when i press the button , FRM-41214 shows up in the form , and REP-52251 ,and i still can not run it .
                      do i miss something ?

                      by the way , when i typed this in the address bar :
                      http://rocky:8889/reports/rwservlet/showjobs?server=rocky , then clicked "x" button , then
                      i got this
                      <br>REP-300: invalid number where deptno = : ==> p_deptno finished with error
                      thanks

                      Edited by: newbi_egy on Mar 21, 2013 4:49 PM

                      Edited by: newbi_egy on Mar 21, 2013 5:15 PM
                      • 8. Re: running report from forms 10g with parameters
                        GregorM
                        Hi.

                        Try to put space before paramform other parameter.

                        Like this
                        SET_REPORT_OBJECT_PROPERTY(ro,REPORT_OTHER, 'p_deptno='||:dept.deptno|| ' paramform=no');
                        Best regards

                        Edited by: gregor13 on Mar 22, 2013 7:39 AM

                        Edited by: gregor13 on Mar 22, 2013 7:43 AM
                        • 9. Re: running report from forms 10g with parameters
                          HamidHelal
                          newbi_egy wrote:
                               
                          SET_REPORT_OBJECT_PROPERTY(ro,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
                               
                               v := run_report_object(ro) ;
                               
                               
                               web.show_document('http://rocky:8889/reports/rwservlet/getjobid'||substr(v,instr(v,'_')+1)||'?server=rocky','_blank') ;
                               
                               end ;
                          when i press the button , FRM-41214 shows up in the form , and REP-52251 ,and i still can not run it .
                          do i miss something ?

                          by the way , when i typed this in the address bar :
                          http://rocky:8889/reports/rwservlet/showjobs?server=rocky , then clicked "x" button , then
                          i got this
                          <br>REP-300: invalid number where deptno = : ==> p_deptno finished with error
                          thanks
                          Hi,
                          You can not check the second link i provided where parameter passing issue is clearly mentioned. you should give space before all parameter.
                          Check the second link, it will help you. And for your correction
                          SET_REPORT_OBJECT_PROPERTY(ro,REPORT_OTHER, 'paramform=no p_deptno='||:dept.deptno);
                          Hope you understand...

                          Hamid
                          • 10. Re: running report from forms 10g with parameters
                            salute-Salem
                            thanks to you all , and special thanks to hamid and george ,

                            i can now run report with one parameter with set_report_object built-in , but now i want to know how to do the task through "other report parameter" property of the report object .

                            how to do it with one parameter and two parameters ?

                            thanks
                            • 11. Re: running report from forms 10g with parameters
                              HamidHelal
                              newbi_egy wrote:
                              thanks to you all , and special thanks to hamid and george ,

                              i can now run report with one parameter with set_report_object built-in , but now i want to know how to do the task through "other report parameter" property of the report object .
                              It's not clear to me.
                              how to do it with one parameter and two parameters ?
                              Ahh! again i said to check my second link for your help. Here is an example
                              REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no one_para='||:btn.one_value||' second_para='||:btn.another_value);
                              If the parameter value is not number then you have to put single quote both side of the value. Always keep single space before parameter.

                              Hope this works...

                              Hamid
                              • 12. Re: running report from forms 10g with parameters
                                salute-Salem
                                this is not what i am talking about .

                                when you click the property palette of the report object on the object navigator of forms builder , you see a property called "other reports parameters" .
                                i think it is for doing the same task ?

                                thanks a lot
                                • 13. Re: running report from forms 10g with parameters
                                  HamidHelal
                                  newbi_egy wrote:
                                  this is not what i am talking about .

                                  when you click the property palette of the report object on the object navigator of forms builder , you see a property called "other reports parameters" .
                                  i think it is for doing the same task ?
                                  Hi
                                  Here i copied from Forms Online Help

                                  Other Reports Parameters Property

                                  Description
                                  A <keyword>=<value> list of parameters to include in the running of the report. For a list of valid parameters, see the keyword list in the Reports online help.
                                  Applies to Reports reports
                                  Set Oracle Forms
                                  Default blank
                                  Required/Optional optional
                                  Usage Notes: When passing multi-word parameter values in the where-clause, the entire where-clause should be enclosed in single quotes. When a name appears in such a multi-word parameter, then two single quotes should also be used to begin and to end that name. For example, in order to pass the parameter value where ename = ’MILLER’ it is necessary to code this as: ‘where ename = ‘‘MILLER’’’

                                  Hope it's clear..

                                  Hamid
                                  • 14. Re: running report from forms 10g with parameters
                                    salute-Salem
                                    Here i copied from Forms Online Help
                                    i looked at it before i posted my question , but could not do the task , i tried but vainly .

                                    could you provide me with an example ?

                                    thanks