Dimension rule functions
This topic describes the available dimension rule functions.
CHILD
Returns the nth child of a specified consolidated element.
Syntax
CHILD(Dimension,Element,Index)
Where Dimension is the name of the dimension which contains the element in question, Element is the element, the child of which to determine and Index is the number of the child to return.
Example
If the element Central Europe, of the Regions dimension has three children, France, Germany and Belgium, in that order, then this formula will return Germany:
CHILD('Regions','Central Europe',2)
But this formula returns Belgium:
CHILD('Regions','Central Europe',3)
CLOSINGPERIOD
Syntax
ClosingPeriod(Dimension,Element,DistanceFromRoot)
Closingperiod returns the last element with these characteristics:
- It is at the same MDX level in the hierarchy as the element you specify in the Element parameter.
- It shares at least one ancestor with the specified element.
Examples
In the examples provided, a hierarchy of all years, years, quarters and months is used. In this hierarchy, the element ’All Years’ is the root element (Level 1).
The years 2002, 2003, 2004 and 2005 are at Level 2.
The quarters of each year, 2002_Q1/Q2/Q3/Q4, 2003_Q1/Q2/Q3/Q4 etc. are at Level 3.
The months within the quarters, 2002_Q1_01/02/03, 2002_Q2_04/05/06 etc. are at Level 4.
Example 1
ClosingPeriod(’Time’,’2002_Q4_12’,0) returns 2005_Q4_12.
The element returned by ClosingPeriod is calculated from the root element (All Years - Level 1).
The last element in the hierarchy that is at the same level as 2002_Q4_12 is 2005_Q4_12. It has the same ancestor (All Years).
Example 2
ClosingPeriod(’Time’,’2002_Q4_12’,1) returns 2002_Q4_12.
The element returned by ClosingPeriod will be calculated from Level 2 (one below the root level). This is 2002.
There is no other element at the same level as 2002_Q4_12 which has 2002 as its ancestor.
Example 3
ClosingPeriod(’Time’,’2005_Q1_01’,2) returns 2005_Q1_03.
The shared parent is 2005_Q1. The last element at the same level as 2005_Q1_01 is 2005_Q1_03. It is the same distance (2 levels down) from the root.
DE.CHILDNAME
Returns the child of a specified consolidated element.
Syntax
DE.CHILDNAME(Dimension,Element,Index)
Dimension is the name of the dimension which contains the element in question, Element is the element whose child you want to determine and Index is the number of the child to display.
Example
If the element Central Europe of the dimension regions has three children, France, Germany and Belgium in that order, then this formula will return Germany:
DE.CHILDNAME('Regions','Central Europe',2)
And this will return Belgium:
DE.CHILDNAME('Regions','Central Europe',3)
DE.CHILDRENCOUNT
Returns the number of children of a specified consolidated element.
Syntax
DECHILDRENCOUNT(Dimension, Element)
Example
If the element Central Europe of the dimension regions has three children, France, Germany and Belgium in that order, then this formula will return "3".
DE.CHILDRENCOUNT('Regions','Central Europe')
DE.COUNT
Returns the number of elements of a specified dimension.
Syntax
DE.COUNT(Dimension)
Example
If the dimension REGIONS has 46 elements (including consolidated ones), this formula will return "46".
DE.COUNT('Regions')
DE.DESCENDANTSCOUNT
Returns the recursive number of children of a specified consolidated element.
Syntax
DE.DESCENDANTSCOUNT(DIMENSION,ELEMENT)
Where Dimension is the name of the dimension which contains the consolidated element and Element is the element of which you want to determine the number of children or granchildren.
Example
This example returns 16 (12 months + 4 quarters):
DE.DESCENDANTSCOUNT(’Month’,’Year’)
The Year element contains four quarters, each containing three months. The quarters and months are all descendants of the Year element.
DE.FIRST
Syntax
DE.FIRST(Dimension)
Example
If the first element in the regions dimension is USA then this function returns USA.
DE.FIRST('Regions')
DE.HSIBLING
Returns the sibling of an element on the same MDX level as a specified element and a specified number of elements away from it. You specify the MDX level as a value from -1 to 127. An element with an MDX level of 0 is a root element. Its children are at MDX level 1, 2 etc.
The special MDX level -1 is used if to display descendants of other root elements at the same MDX level.
Syntax
DE.HSIBLING(Dimension, Element, MDX Level, Offset)
DE.INDEX
Returns the index number of a specified element within a specified dimension.
DE.INDEX is one-based. It returns 1 for the first element in the dimension. If the element is not found, the function returns 0.
Syntax
DE.INDEX(Dimension,Element)
Example
If the fourth element in the dimension Regions is Brazil, then this function returns 4.
DE.INDEX('Regions','Brazil')
DE.ISCHILD
Returns TRUE if the second specified element is a child of the first.
Syntax
DE.ISCHILD(Dimension,Parent,Child)
Example
If the element Central Europe of the dimension regions, has three children, France, Germany and Belgium in that order, then this formula will return TRUE.
DE.ISCHILD('Regions','Central Europe','Germany')
And this will return FALSE:
DE.ISCHILD('Regions','Central Europe','Spain')
DE.ISDESCENDANT
Returns TRUE if the second specified element is a descendant of the first.
Syntax
DE.ISDESCENDANT(Dimension,RootElement,TestedElement)
Where Dimension is the name of the dimension which contains the specified elements, RootElement is the parent or grand parent element and TestedElement is the child or grand child element.
Examples
The first two examples return TRUE:
DE.ISDESCENDANT(’Month’,’1st Quarter’,’January’)
DE.ISDESCENDANT(’Month’,’Year’,’January’)
But this example returns FALSE:
DE.ISDESCENDANT(’Month’,’2nd Quarter’,’January’)
DE.LEAFCHILDRENCOUNT
Returns the number of child base elements of a specified consolidated element.
Syntax
DE.LEAFCHILDRENCOUNT(Dimension,Element)
Where Dimension is the name of the dimension which contains the consolidated element and Element is the element of which you want to determine the number of base elements.
Examples
This example returns No Data:
DE.LEAFCHILDRENCOUNT(’Month’,’Year’)
The four quarters are children. But none is a base element because each contains three months.
This example returns 3:
DE.LEAFCHILDRENCOUNT(’Month’,’1st Quarter’)
The three months of the first quarter are all base elements.
DE.LEAFDESCENDANTSCOUNT
Returns the recursive number of base child elements of a specified consolidated element.
Syntax
DE.LEAFDESCENDANTSCOUNT(Dimension,Element)
Where Dimension is the name of the dimension which contains the consolidated element and Element is the element of which you want to determine the number of base children or grand children.
Example
This example returns 12.
DE.LEAFDESCENDANTSCOUNT(’Month’,’Year’)
The Year element contains four quarters, each containing three months. All are descendants of the Year element. But only the months are base elements so the quarters are not included.
DE.LEVEL
Returns the level in the dimension hierarchy of a specified element. In a dimension hierarchy, Base Elements are at level 0. All further levels are calculated from this level upwards i.e. the first level of consolidated elements is level 1, the second is level 2, etc.
Syntax
DE.LEVEL(Dimension,Element)
Examples
In the regions dimension, Germany and France consolidate into Central Europe and Central Europe in turn consolidates into Europe. This formula will return "0".
DE.LEVEL('Regions','Germany')
And this formula will return "2":
DE.LEVEL('Regions','Europe')
DE.NAME
Returns the name of the nth element within a dimension.
Syntax
DE.NAME(Dimension,Index)
Example
If the fourth element in the regions dimension is Brazil then this function returns Brazil.
DE.NAME('Regions',4)
DE.NEXT
Syntax
DE.NEXT(Dimension,Element)
Example
If the first three elements in the dimension regions are USA, Canada and Mexico, then this function returns Mexico.
DE.NEXT('Regions','Canada')
DE.PARENTNAME
Returns the parent of a specified element.
Syntax
DE.PARENTNAME(Dimension,Element,Index)
Example
The element Austria in the dimension regions rolls up into three different parents World, Europe (East/West) and Western Europe. This formula will return Europe (East/West).
DE.PARENTNAME('Regions','Austria',2)
DE.PARENTSCOUNT
Returns the number of parents of a specified.
Syntax
The element Austria in the dimension regions rolls up into three different parents: Benelux, West-Europe and European Union. This function returns "3".
DE.PARENTSCOUNT('Regions','Austria')
DE.PREV
Syntax
DE.PREV(Dimension, Element)
Example
If the first three elements in the dimension regions are USA, Canada and Mexico, then this formula will return USA.
DE.PREV('Regions','Canada')
If the element has no predecessor (that is, it is the first element in a dimension) the function returns "0".
DE.SIBLING
Returns a sibling of a specified element. The sibling returned is specified by an offset number.
Syntax
DE.SIBLING(Dimension,Element,Offset)
Example
The Europe element in the dimension regions has these five children: Northern Europe, North-West Europe, Central Europe, Southern Europe and South-East Europe defined in that order.
This formula will return North-West Europe:
DE.SIBLING('Regions','Southern Europe',2)
This formula will return Central Europe:
DE.SIBLING('Regions','Northern Europe',-2)
DE.TYPE
Returns the type (for example, base element).
Syntax
DE.TYPE(Dimension, Element)
The function returns these codes:
- N: For base elements
- C: For consolidated elements
- S: For string elements
- R: For rules elements.
Example
The element Benelux in the dimension regions is a consolidated element. This function returns "C".
DE.TYPE('Regions','Benelux')
DE.WEIGHT
When you define a consolidated element, you can assign a weight to each of its components. DE.WEIGHT returns the weight of a specified component of an element.
Syntax
DE.WEIGHT(Dimension,Parent,Child)
Example
If the element Germany in the dimension regions is weighted by 2 when consolidated into Central Europe, then this function returns "2".
DE.WEIGHT('Regions','Central Europe','Germany')
DIM.TOPLEVEL
In a dimension hierarchy, Base Elements are considered level 0. All further levels are calculated from this level upwards. So, the first level of consolidated elements is level 1, the second level 2 and so on. DIM.TOPLEVEL returns the level number of the highest element in the consolidation hierarchy of a dimension.
Syntax
DIM.TOPLEVEL(Dimension)
Where Dimension is the name of the dimension whose highest hierarchy level you want to determine.
Example
In the Regions dimension, World is the highest level element at level 3. This formula returns "3".
DIM.TOPLEVEL('Regions')
DMIX
Returns the index number for a specified element within a dimension.
Syntax
DIMIX(Dimension, Element)
Where Dimension is the name of the dimension containing the element whose index number you want to determine, and Element is the element.
Example
If the fourth element in the dimension regions is Brazil, thisfunction will return "4":
DIMIX('Regions','Brazil')
DIMNM
Returns the name of the nth element within a dimension.
Example
DIMNM(Dimension, Index)
Where Dimension is the name of the dimension from which you want to return an element and Index is the number of the element within the dimension sequence.
Example
If the fourth element in the Regions dimension is Brazil, then this function will return Brazil.
DIMNM('Regions',4)
HCLOSINGPERIOD
HCLOSINGPERIOD works in a similar way to CLOSINGPERIOD but can be used with parallel hierarchies. It has the additional parameter ’TopNode’. ’TopNode’ indicates the hierarchy to be used.
Parallel hierarchies are those in which an element has multiple parents. This is common in time-based dimensions.
For example, a Time dimension may contain a hierarchy of years, quarters and months. January, February and March are within Quarter 1 and Quarter 1 is within a year.
But the dimension may also contain a year-to-date hierarchy containing a cumulative year parent, cumulative months, and months. So, Cumulative Month 1 contains January, Cumulative Month 2 contains January and February and Cumulative Month 3 contains January, February and March.
In this example, ClosingPeriod would return an empty string because it would be unable to determine which hierarchy (Year or Cumulative Year) is intended.
Syntax
HClosingPeriod(Dimension, Element, DistanceFromTopNode, TopNode)
Example
HClosingPeriod(’Time’, ’March’, 0, ’CumulativeMonth 11’)
This would return ’November’.
HOPENINGPERIOD
HOPENINGPERIOD works in a similar way to OPENINGPERIOD but can be used with parallel hierarchies. It has the additional parameter ’TopNode’. ’TopNode’ indicates the hierarchy to be used.
Parallel hierarchies are those in which an element has multiple parents. This is common in time-based dimensions.
For example, a Time dimension may contain a hierarchy of years, quarters and months. January, February and March are within Quarter 1 and Quarter 1 is within a year.
But the dimension may also contain a year-to-date hierarchy containing a cumulative year parent, cumulative months, and months. So, Cumulative Month 1 contains January, Cumulative Month 2 contains January and February and Cumulative Month 3 contains January, February and March.
In this example, OpeningPeriod would return an empty string because it would be unable to determine which hierarchy (Year or Cumulative Year) is intended.
Syntax
HOpeningPeriod(Dimension, Element, DistanceFromTopNode, TopNode)
Example
HOpeningPeriod(’Time’, ’March’, 1, ’Cumulative_Year’)
This would return ’January’.
OPENINGPERIOD
Returns the first element which has these characteristics:
- It is at the same MDX level in the hierarchy as the element you specify in the Element parameter of the function.
- It shares at least one ancestor with the specified element.
The function returns and empty string if the parameters are incorrect or there is no element with the required characteristics.
Syntax
Examples
In the examples provided, a hierarchy of all years, years, quarters and months is used. In this hierarchy, the element ’All Years’ is the root element (Level 1).
The years 2002, 2003, 2004 and 2005 are at Level 2.
The quarters of each year, 2002_Q1/Q2/Q3/Q4, 2003_Q1/Q2/Q3/Q4 etc. are at Level 3.
The months within the quarters, 2002_Q1_01/02/03, 2002_Q2_04/05/06 etc. are at Level 4.
Example 1
OpeningPeriod(’Time’,’2005_Q4_12’,0) returns 2002_Q1_01.
The element returned by OpeningPeriod is calculated from the root element (All Years - Level 1).
The first element in the hierarchy that is at the same level as 2005_Q4_12 is 2002_Q1_01. It has the same ancestor (All Years).
Example 2
OpeningPeriod(’Time’,’2005_Q4_12’,1) returns 2005_Q1_01.
The element returned by OpeningPeriod will be calculated from Level 2 (one level below the root level). This is 2005.
The common ancestor is 2005. 2005_Q1_01 is at the same level of the hierarchy as 2005_Q4_12.
Example 3
OpeningPeriod(’Time’,’2005_Q4_12’,2) returns 2005_Q4_10.
The element returned by OpeningPeriod will be calculated from Level 3 (two levels below the root level).
The common ancestor is 2005_Q4. The first element at the same level as 2005_Q4_12 is 2005_Q4_10.
PARENT
Returns the parent of a specified element.
Syntax
PARENT(Dimension, Element, Index)
If the element Austria in the Regions dimension rolls up into 3 different parents: Other Countries, EFTA Countries and European Union, this formula will return EFTA Countries.
DE.PARENT('Regions','Austria',2)