IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 12 replies
  • Latest Post - ‏2013-03-01T17:17:33Z by DanielWagemann
Sruj
Sruj
25 Posts

Pinned topic Passing parameters to the Stored Procedure

‏2010-11-04T15:19:27Z |
Hi ,

My Requirement is to call a SQL Stored Procedure from the Report and fetch the data.

It has got 4 input parameters and 8 output values.

I have imported the Stored procedure to the FM and dragged all the output values on to the report in Report Studio.

Now, how do i associate or assign the Input Values in the Stored Procedure with the Prompts/Parameters in the Report.

Thanks,
Sruj
Updated on 2013-03-01T17:17:33Z at 2013-03-01T17:17:33Z by DanielWagemann
  • Neupane
    Neupane
    99 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2010-11-05T17:14:31Z  
    Single value:
    #prompt('Material_Name')#

    Multiple values:
    #promptmany('Material_Name')#

    --
    Suraj Neupane
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2010-11-07T22:14:26Z  
    The input arguments need to be defined in Framework Manager. When you edit the stored procedure definition in the model you will have the option to define the input values. Instead of using a constant you can use a parameter reference such as ?Parameter name? and this will then become a prompted parameter whenever you use the stored procedure. You can also use the macro expressions as Neupane mentioned but there is a restriction that you can only use the single-select version of the prompt macros (because you can only supply one input value per procedure argument) so promptmany may not work for you here.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2012-02-08T19:08:37Z  
    The input arguments need to be defined in Framework Manager. When you edit the stored procedure definition in the model you will have the option to define the input values. Instead of using a constant you can use a parameter reference such as ?Parameter name? and this will then become a prompted parameter whenever you use the stored procedure. You can also use the macro expressions as Neupane mentioned but there is a restriction that you can only use the single-select version of the prompt macros (because you can only supply one input value per procedure argument) so promptmany may not work for you here.
    Hi Phil,

    I have a prompt page and i am selecting the values to pass to the stored procedure.
    I already created the parameters (?parammetername?) in FM. I want to know how to pass the value from the prompt page to the stored procedure.

    thanks
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2012-02-08T20:18:46Z  
    Hi Phil,

    I have a prompt page and i am selecting the values to pass to the stored procedure.
    I already created the parameters (?parammetername?) in FM. I want to know how to pass the value from the prompt page to the stored procedure.

    thanks
    Hi All,

    I am able to do this. When we are creating the Prompt, instead of crating New parameter, select the "Existing Parameter" option. Then choose the parameter you specified in the FM model for the storedprocedure.

    I have one question. I have created a value prompt (Mulitselect, List box). But the thing is I can't select multiple values. It is allowing me to select only one value. I changed the properties, but not able to select multiple values in the value prompt.

    any idea, pls share.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2012-02-08T20:22:56Z  
    Hi All,

    I am able to do this. When we are creating the Prompt, instead of crating New parameter, select the "Existing Parameter" option. Then choose the parameter you specified in the FM model for the storedprocedure.

    I have one question. I have created a value prompt (Mulitselect, List box). But the thing is I can't select multiple values. It is allowing me to select only one value. I changed the properties, but not able to select multiple values in the value prompt.

    any idea, pls share.
    have u change the operator to "IN" instead of "=" go to the main query u will see the parameter in that query and change the operator = to IN

    Winson.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2012-02-08T20:31:50Z  
    have u change the operator to "IN" instead of "=" go to the main query u will see the parameter in that query and change the operator = to IN

    Winson.
    Hi winson,

    I am using a stored procedure, so from the prompt page i am passing the values to the stored procedure. There is no filter in the main page.
    When i am selecting only one value it will return the value. But I have to pass mulitple values from the value prompt.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2012-02-09T18:45:48Z  
    Hi winson,

    I am using a stored procedure, so from the prompt page i am passing the values to the stored procedure. There is no filter in the main page.
    When i am selecting only one value it will return the value. But I have to pass mulitple values from the value prompt.
    did u read above replies?

    Single value:
    #prompt('Material_Name')#

    Multiple values:
    #promptmany('Material_Name')#

    Winson.
  • Senators
    Senators
    37 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2013-02-28T14:06:53Z  
    Hi All

    I am running into a similar situation. I was hoping that someone can clarify a best practise.

    • We have a Stored Procedure that works great. It prompts for a Date Parameter and works like a charm. I can map a prompt page - Value prompt to that parameter and it works

    • My requirement is to also automate this report for tomrorow's date (add_days (current_date). +1)

    I'm thinking I need too:

    • Have to 2 date parameters in the Stored Proc
    • In the FM Model have one that prompts and have another that is the calc mentioned above

    Does this make sense? When we go to automate the report we can't have an parameters appear for entry. It should be seemless

    This is Oracle 11g
    Cognos 8.4.1

    Thanks in advance!
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2013-02-28T18:59:44Z  
    • Senators
    • ‏2013-02-28T14:06:53Z
    Hi All

    I am running into a similar situation. I was hoping that someone can clarify a best practise.

    • We have a Stored Procedure that works great. It prompts for a Date Parameter and works like a charm. I can map a prompt page - Value prompt to that parameter and it works

    • My requirement is to also automate this report for tomrorow's date (add_days (current_date). +1)

    I'm thinking I need too:

    • Have to 2 date parameters in the Stored Proc
    • In the FM Model have one that prompts and have another that is the calc mentioned above

    Does this make sense? When we go to automate the report we can't have an parameters appear for entry. It should be seemless

    This is Oracle 11g
    Cognos 8.4.1

    Thanks in advance!
    Within FM you could replace your current value expression in your screen caputre with the following:

    #prompt('MyPrompt','date',  _add_days($current_timestamp ,1))      #
    


    If not value is entered for the MyPrompt the report should run with 2morrows date, if a value is entered it will use that.

    See if this works for you.
    Updated on 2014-03-25T07:42:31Z at 2014-03-25T07:42:31Z by iron-man
  • Senators
    Senators
    37 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2013-03-01T15:48:55Z  
    Within FM you could replace your current value expression in your screen caputre with the following:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">#prompt('MyPrompt','date', _add_days($current_timestamp ,1)) # </pre>

    If not value is entered for the MyPrompt the report should run with 2morrows date, if a value is entered it will use that.

    See if this works for you.
    Hi Dan

    Thanks for the email. This appears to work as when I execute the report off the Stored Procedure with that prompt macro, the 'MyPrompt' is optional which is great. Now when I automate this report in the Portal I can "set prompt values" and uncheck the 'MyPrompt' option, which is what I was looking to do.

    I'm curious, which part of the code makes 'MyPrompt' optional. Is it just that there is a calculation after the comma?

    Just good to know for future reference.
  • Senators
    Senators
    37 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2013-03-01T15:58:03Z  
    Within FM you could replace your current value expression in your screen caputre with the following:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">#prompt('MyPrompt','date', _add_days($current_timestamp ,1)) # </pre>

    If not value is entered for the MyPrompt the report should run with 2morrows date, if a value is entered it will use that.

    See if this works for you.
    Sorry one more quick question Dan. I read in the documentation that output parameters are not supported for Stored Procs. I'm trying to test the data (dummy data right now, not real) to ensure that it shows data for the current date +1 when the prompt is not selected.

    (Doc where I found this http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_fm.8.4.1.doc/ug_fm_i_stored_procedure_query_subjects.html)

    In the report:

    -I have created a Layout calculation and exposted 'MyPrompt'
    -When the Prompt is selected during execution, it shows the correct date which tells me this will pass correctly to the stored Proc.
    -When the Prompt is not selected, nothing appears.

    I would like to test to ensure that the 'currentdate +1' is functioning properly. Do you have any suggestions in how I could expose this in the report at all? Normally I'd have proper data, but the database guys have not finished loading it yet.

    Thank you very much
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Passing parameters to the Stored Procedure

    ‏2013-03-01T17:17:33Z  
    • Senators
    • ‏2013-03-01T15:48:55Z
    Hi Dan

    Thanks for the email. This appears to work as when I execute the report off the Stored Procedure with that prompt macro, the 'MyPrompt' is optional which is great. Now when I automate this report in the Portal I can "set prompt values" and uncheck the 'MyPrompt' option, which is what I was looking to do.

    I'm curious, which part of the code makes 'MyPrompt' optional. Is it just that there is a calculation after the comma?

    Just good to know for future reference.
    Glad it worked out. Providing a value or calclation after the second comma sets a default value, which mimics the optional behaviour.