Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

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.

flowersr (MIS) (OP)
28 Feb 07 15:26
Hi,

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,
Rich
mfgf (Instructor)
1 Mar 07 6:08
Hi,

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,

MF.

flowersr (MIS) (OP)
1 Mar 07 10:29
Mfgf,

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{prompt('EnterStatus','token')}#

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:
ACTIVE

Key             Value
---------------------
ACTIVE          A
DISCHARGED      D        

Thanks,
Rich



mfgf (Instructor)
1 Mar 07 11:10
Hi,

'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,

MF.
flowersr (MIS) (OP)
1 Mar 07 12:46
MF,

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
mfgf (Instructor)
1 Mar 07 13:14
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,

MF.

flowersr (MIS) (OP)
1 Mar 07 13:30
MF,

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,
Rich
flowersr (MIS) (OP)
1 Mar 07 14:18
MF,

Never mind the last question.  I got it working.

Thanks again for all your help.

-Rich

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!

Back To Forum

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