Sometimes it’s necessary to know the number of a level in a hierarchy, for example if you want to display or hide a column depending on the current level in a drillable hierarchy. Cognos Report Studio provides two ways to implement that behavior:
- roleValue() function
- with a combination of several functions
The roleValue(<role string>; <Hierarchy>) function in Cognos returns the values of defined roles in the data model. When you are creating a meta data model in framework manager you can define so called roles. These roles can be connected to different attributes on different levels. These different attributes then can be accessed by the using the same role on all levels.
There are some standard roles defined in Cognos. Two very common examples are the Business Key and Member Caption, which have to be defined for each level. Further standard roles are available as well as it is possible to define your own roles. For example you could create a “SortValue” role. Then you can access the different sorting attributes for each level easily by using one role and the roleValue() function. This can be pretty useful when developing reports with drill features.
Another standard role exists to receive the level no. This role is the _levelNumber role. The use is pretty simple: roleValue (‘_levelNumber’; <Level of a hierarchy>)
The second method is the combination of several functions:
Ordinal(level(item(<member of a hierarchy>;0)))
Item(<Level of a hierarchy>;0) returns the first item of the level of a hierarchy. Level () returns the level of the given member and ordinal() then returns a corresponding number. In case of levels this is the level number.
With these both methods one can receive the level number and based on this implement level dependent behavior.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!