How to return the level number of a member?

[suhas_bhat]

Hi all,
   I am facing an issue which goes like this.
I a have a value prompt that displays members of a hierarchy viz. a,b,c,d where b i s a child of a and c is a child of b and so on, the source for the value prompt is the hierarchy itself.
Now when i select the member i want to use the level number that the member belongs to. Which function can i use to access the level number of the member? What would the code look like?
Please help. ???

[CognosPaul]

Two ways

Code Select Expand

ordinal(#prompt('member','memberuniquename','','','[ns].[dim].[hierarchy]')#)
or

Code Select Expand

roleValue('_levelNumber',#prompt('member','memberuniquename','','','[ns].[dim].[hierarchy]')#)

The ordinal will work with all cubes. The _levelNumber may not work with some dimensional sources.

A small trick: in the insertable objects pane, open any hierarchy to a member. Right click on that member and click Properties. You can reference any of the attributes there with the roleValue function:
First word is always preceded by an underscore _ and first word is always lowercase, the first character of all subsequent words are capitalized
rolevalue('_firstSecond',[member])
rolevalue('_path',currentMember([ns].[dim].[hier]))

[suhas_bhat]

This is fantastic stuff Paul, Thanks a ton!

Since i am using a value prompt i don't want to hard code the member name, i tried using it like this
roleValue('_levelNumber',?parameter_name?) - this throws invalid prompt use errors.

What would be the right way to use this?

[CognosPaul]

Well, it depends on what's being passed to the prompt.

A few possible scenarios that would get you that error:

• You're relying on Cognos to generate the prompt. That won't work since there isn't any context for it.
• You have the prompt on the page, but for whatever reason it's not returning the MUN.
• You're using a cube that doesn't have the _levelNumber attribute.

Try this:

roleValue('_levelNumber',#prompt('parameter_name','memberuniquename','','','[ns].[dim].[hierarchy]')#)

replace the [ns].[dim].[hierarchy] with the hierarchy you're using.