Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

passing parameters between stored procs and Report Studio

passing parameters between stored procs and Report Studio


I have "imported" a stored procedure into Framework Manager that has 4 arguments, 2 which are dates (a range) plus 2 that are of character type.  I can see them when I right click on the query subject and select "Edit Definition".   

Next, I created a report in Report Studio (Cognos 8) that accesses this stored procedure but it does not prompt me for values when I run the report?  

I am looking for direction on how to:

1) make the report prompt me for those 4 parameters
2) restrict 1 parameter to 2 static values only
3) add some user instructions to each parameter

Thanks for reading,

RE: passing parameters between stored procs and Report Studio


When you edit the definition of the stored procedure in Framework Manager, you can set a default value for each argument.  The trick here is to use a parameter as the value for each (?paramname?), which will then result in a prompt being generated for each argument whenever data is retrieved from the SP.  If you get creative with the parameter names, you can also provide instructions to the users, eg ?Please select the relevant product code?

For any prompts that require the list restricting to 2 values, you will probably need to construct your own prompt within the report to achieve this.

Best regards,


RE: passing parameters between stored procs and Report Studio


Thanks.  I kind of got it to work but barely.  

I created parameter maps and while editing the definition and clicking on '...' in the VALUE column I entered


Parm_PatientStatus is the name of the parameter map but when I run the report it does not show a dropdown box only an single entry box to type in a value.  I realize that 'EnterStatus' shows as prompt text but what does 'token' do?  

Parm_PatientStatus is defined as:

Default value:

Key             Value
ACTIVE          A
DISCHARGED      D        


RE: passing parameters between stored procs and Report Studio


'token' is the datatype parameter value you are using for the prompt macro.  The default value if you do not specify one is 'string'.  You would specify 'token' as the datatype if you want the macro to accept the Use value passed from the prompt without enclosing it in quotes in the generated query.

The problem with this approach is that you have to maintain the parameter maps and code the prompt macros.  If you simply use parameters for the argument values (?param?) this will allow prompted values to be passed into the SP, and you can code the relevant prompts in your report (value prompts in your case, with static values).  This would also mean you would have control over how the prompts appear to the users running the reports.

Best regards,


RE: passing parameters between stored procs and Report Studio


This is only my 2nd report and I took the classes close to a year ago so I'm not sure I am following you regarding "?param?".

Are you saying that I should not define ANYTHING in Framework regarding the arguments but rather create value prompts in Report Studio?   If so, I am guessing the names would have to match between the two so an 'under the sheets' connection is made so that Report Studio will pass it to the stored procedure correctly.

Thanks, Rich

RE: passing parameters between stored procs and Report Studio

Hi Rich,

In Framework Manager, you need to edit the definition of your SP query subject, and for each argument, set the value to be a parameter (any text you like, as long as it begins and ends with a ? character).  Each value does not need to be a prompt macro - just a parameter name enclosed in question marks.

When you test the query subject, you will find that Framework Manager has to prompt you for a value to use for each parameter you coded - you can test the mechanism of calling the SP by entering some values here (and save them for future testing in Framework Manager if you wish).

Next you need to publish your package (including the SP), then launch Report Studio, using the package.

Bring in the relevant items from the SP query subject into your report, then add a prompt page to your report, and drag in your first prompt from the toolbox (most likely a value prompt).  When the prompt wizard asks you if you want to create a new parameter or use an existing parameter, choose the existing option, and you should then be able to pick the relevant parameter name for the prompt to pass a value to.  If you want to use hard-coded values to display in your prompt, finish the wizard at this point, select the prompt, and use the 'Static Choices' property to specify the values to use and display in your prompt.  Repeat this process to add prompts for the other parameters.

Best regards,


RE: passing parameters between stored procs and Report Studio


OK, that makes sense.  

Last question, I hope...  How do I create a prompt that basically shows all values in a reference table, like, say an office table that contains a code & description?  I have already defiend the office table in Framework.

By the way, the reason I am using stored procs is that the database is NOT in Cognos and I was told by a Framework teacher (a former DBA) that stored procs are faster than redefining the relationships within Framework when the database is NOT in Cognos.

Thanks again,

RE: passing parameters between stored procs and Report Studio


Never mind the last question.  I got it working.

Thanks again for all your help.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close