passing parameters between stored procs and Report Studio

[flowersr]

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]

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]

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]

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]

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]

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]

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]

MF,

Never mind the last question. I got it working.

Thanks again for all your help.

-Rich