Hi,
I want to get value of specified node from tree prompt to pass into my store procedure. DataItem of tree prompt is "rootMembers([Business view].[DimMy].[My])". Parameter name of tree prompt is 'pmt_My' When I defined macro function in Framework Manager, which is #prompt('pmt_My')#, my tree on my report can not show its tree, only show top node (before I did macro function, it can show its tree). How can I resolve this issue? I'm working on Cognos 8.3 ???
Thanks
The parameters of a prompt macro are
#prompt/promptmany(
'ParameterName'
,'datatype'
,'Default'
,'PreText'
,'Source'
,'PostText'
)#
The generated prompt is based partly on the source. Try this:
#prompt('pmt_My','memberuniquename','','','[Cube].[Dimension].[Hierarchy]')#
Thank for your answer. I'm using MS SQL 2005 to be datasource of Cognos server. I followed your instructions but after I edited prompt, I tried with Test section and I received error message:
"
RQP-DEF-0354 The query contains one or more unresolved prompts
QE-DEF-0385 Invalid format for prompt "pmtMy". Expected format is unknown.
"
My variable in store procedure is varchar type. How can I resolve this issue?
Does the prompt generate the tree prompt?
The prompt macro will return a memberuniquename such as
[Cube].[Dimension].[Hierarchy]->[all].[3].[12].[73]
Since your sp is expecting a value you could try something like:
roleValue('_businessKey',#prompt('test','memberuniquename','','','[Cube].[Dimension].[Hierarchy]')#)
which would return: 73
Hi Paul,
What is [Cube].[Dimension].[Hierarchy] ? I can not find this object in FrameWork Manager. Can you explain more detail?
Thanks
Khoi
I meant [Business view].[DimMy].[My].
The reason that you were getting that error before is because the prompt was trying to return the memberUniqueName of the specific member, instead of the key. By wrapping the macro in the rolevalue function you can convert that member to the specified value that you need.