If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

issue with #prompt

Started by khoinh, 15 Jul 2010 09:18:53 AM

Previous topic - Next topic

khoinh

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

CognosPaul

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]')#

khoinh

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?

CognosPaul

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

khoinh

Hi Paul,

   What is [Cube].[Dimension].[Hierarchy] ? I can not find this object in FrameWork Manager. Can you explain more detail?

Thanks
Khoi

CognosPaul

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.