Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
12 replies Latest Post - ‏2013-03-01T17:17:33Z by DanielWagemann
Sruj
Sruj
25 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Passing parameters to the Stored Procedure

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

    Multiple values:
    #promptmany('Material_Name')#

    --
    Suraj Neupane
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Passing parameters to the Stored Procedure

    ‏2010-11-07T22:14:26Z  in response to Sruj
    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
      ACCEPTED ANSWER

      Re: Passing parameters to the Stored Procedure

      ‏2012-02-08T19:08:37Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Passing parameters to the Stored Procedure

        ‏2012-02-08T20:18:46Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Passing parameters to the Stored Procedure

          ‏2012-02-08T20:22:56Z  in response to SystemAdmin
          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
            ACCEPTED ANSWER

            Re: Passing parameters to the Stored Procedure

            ‏2012-02-08T20:31:50Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

              Re: Passing parameters to the Stored Procedure

              ‏2012-02-09T18:45:48Z  in response to SystemAdmin
              did u read above replies?

              Single value:
              #prompt('Material_Name')#

              Multiple values:
              #promptmany('Material_Name')#

              Winson.
  • Senators
    Senators
    37 Posts
    ACCEPTED ANSWER

    Re: Passing parameters to the Stored Procedure

    ‏2013-02-28T14:06:53Z  in response to Sruj
    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
      ACCEPTED ANSWER

      Re: Passing parameters to the Stored Procedure

      ‏2013-02-28T18:59:44Z  in response to Senators
      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
        ACCEPTED ANSWER

        Re: Passing parameters to the Stored Procedure

        ‏2013-03-01T15:48:55Z  in response to DanielWagemann
        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.
        • DanielWagemann
          DanielWagemann
          411 Posts
          ACCEPTED ANSWER

          Re: Passing parameters to the Stored Procedure

          ‏2013-03-01T17:17:33Z  in response to Senators
          Glad it worked out. Providing a value or calclation after the second comma sets a default value, which mimics the optional behaviour.
      • Senators
        Senators
        37 Posts
        ACCEPTED ANSWER

        Re: Passing parameters to the Stored Procedure

        ‏2013-03-01T15:58:03Z  in response to DanielWagemann
        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