This discussion is archived
14 Replies Latest reply: Mar 25, 2013 5:52 PM by newbi_egy RSS

running report from forms 10g with parameters

newbi_egy Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    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

Legend

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