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.
This topic has been locked.
12 replies Latest Post - 2013-03-01T17:17:33Z by DanielWagemann
Pinned topic Passing parameters to the Stored Procedure
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-03-01T17:17:33Z at 2013-03-01T17:17:33Z by DanielWagemann
Re: Passing parameters to the Stored Procedure2010-11-07T22:14:26Z in response to SrujThe 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.
Re: Passing parameters to the Stored Procedure2012-02-08T19:08:37Z in response to SystemAdminHi 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.
Re: Passing parameters to the Stored Procedure2012-02-08T20:18:46Z in response to SystemAdminHi 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.
Re: Passing parameters to the Stored Procedure2012-02-08T20:31:50Z in response to SystemAdminHi 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.
Re: Passing parameters to the Stored Procedure2013-02-28T14:06:53Z in response to SrujHi 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
Thanks in advance!
DanielWagemann 2700058MAJ411 PostsACCEPTED ANSWER
Re: Passing parameters to the Stored Procedure2013-02-28T18:59:44Z in response to SenatorsWithin 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.
Re: Passing parameters to the Stored Procedure2013-03-01T15:48:55Z in response to DanielWagemannHi 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.
Re: Passing parameters to the Stored Procedure2013-03-01T15:58:03Z in response to DanielWagemannSorry 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