Passing Data Item prompt value to Stored Procedure in RS

[buddtholomew]

I imported the Stored Procedure into FM, successfully supplied a value to the SystemRef parameter and validated the results. I then removed the value, saved and published the SP for Report Studio.

In RS:
1. Created Query1 with fields from the SP.
2. Created a Prompt Page and Query2 with an equivalent SystemRef field from another dimension in the model (relationship exists in FM).
3. Filtered Query1 based on Query2 prompt selection.

It does not appear that Cognos supports passing a data item value (selected in a value prompt for example) to a Stored Procedure when the Parameter is created in Report Studio. If a SP parameter value or macro is provided in FM, then the Prompt is auto-generated in the report and works as expected (however, the user has to manually enter a value and cannot select from a list of values). Also, if you went with this approach, I was unable to create additional prompts to coexist with the parameter created in FM.

I'm hoping others have encountered this issue and have a resolution in mind.

Best-
Budd

[CoolP]

Hi,
I think it should.
Try using the caption function on the 'equivalent SystemRef field from another dimension'.

Thanks
CP

[buddtholomew]

Hi,
I think it should.
Try using the caption function on the 'equivalent SystemRef field from another dimension'.

Thanks
CP

You think it would, but it doesn't. Also, I am not using a DMR, so the Caption function is not warranted. Does anyone else have a suggestion or is aware of this deficiency?

[MFGF]

I'm pretty sure I have done this before. You need to specify the parameter in Framework Manager (?YourParam?) to pass the argument to the SP first off. You should then be able to define whatever prompt you like based on this existing parameter in Report Studio.

I can't test this right now - I am on my iPhone about ten miles from my laptop...

Good luck!

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

[buddtholomew]

I'm pretty sure I have done this before. You need to specify the parameter in Framework Manager (?YourParam?) to pass the argument to the SP first off. You should then be able to define whatever prompt you like based on this existing parameter in Report Studio.

I can't test this right now - I am on my iPhone about ten miles from my laptop...

Good luck!

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

MF-

I am still struggling to achieve the desired outcome. Would you have an opportunity to confirm that Cognos supports this behavior?

Best-
Budd

[nishithshri]

Hello Budd and MF,

Just wanted to check if this is still possible? Passing data item value as input parameter in SP in FM?

Thanks,
Nishith