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. ???
Two ways
ordinal(#prompt('member','memberuniquename','','','[ns].[dim].[hierarchy]')#)
or
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]))
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?
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.