1. Calculation and Query Reference for Oracle Essbase
  2. Calculation Functions
  3. Calculation Function List
  4. @RELXRANGE

@RELXRANGE

The @RELXRANGE calculation function for Essbase generates a cross dimensional list based on another cross dimensional list, combined with starting and ending positional offsets.

This function generates a cross-dimensional list for each cell in the predefined cross-dimensional list (XrangeList), based on the relative position of the cell that is currently being calculated and the offsets, using the predefined cross-dimensional list (XrangeList) as the limit.

Syntax

@RELXRANGE (startOffset, endOffset, XrangeList)

Parameters

startOffset

Defines the first tuple in the cross dimensional list to be returned.

  • An integer value returns a cross-dimensional member relative to the current cell being calculated, in the predefined cross-dimensional list (XrangeList).

  • A negative value specifies a prior cross-dimensional member to the current cell being calculated, in XrangeList.

  • A value of 0 returns the cross-dimensional member or cell currently being calculated.

  • A positive value specifies a subsequent cross-dimensional member to the current cell being calculated, in XrangeList.

endOffset

Defines the last tuple in the cross-dimensional list to be returned. The value types are the same as for startOffset

XrangeList

A cross-dimensional list to be used as the limit.

Can be a valid member name, a comma-delimited list of member names, cross-dimensional members, or a return value from a member set function or range function (including @XRANGE).

For more information about rangeList and XrangeList, see Range List Parameters.

Notes

  • startOffset must be equal to or lesser than endOffset.

  • The order of dimensions in XrangeList drives the sequence of the tuples in the resulting tuples list. The right-most dimension in an XrangeList is the most frequently incremented dimension. The increment of members in a dimension goes in outline order, or in the order of the XrangeList used as an argument.

  • If the cell that is currently being calculated is out of the bounds of XrangeList, this function returns an empty cross-dimensional list.

  • If startOffset is out of the bounds of XrangeList, this function returns a cross-dimensional list starting from the first member of XrangeList.

  • If endOffset is out of the bounds of XrangeList, this function returns a cross-dimensional list ending on the last member of XrangeList.

  • Within XrangeList, in the parameter list for @XRANGE, you cannot pass members from the anchor dimension, meaning the dimension of the member on which the formula is set. See the Example for a correct way to use members from the anchor dimension.

Example

In the parameter list for @XRANGE, you cannot pass members from the anchor dimension. This example demonstrates a correct and an incorrect usage of @XRANGE.

Correct

mbrCount=@COUNT(SKIPNONE,@RELXRANGE(-1,3,@XRANGE(Jan->Actual,May->Actual))->Sales);

Where mbrCount and Sales are both in the Measures dimension. Measures is the anchor dimension, meaning the dimension of the member on which the formula is set.

The XrangeList is represented by @XRANGE(Jan->Actual,May->Actual), and returns the following: 


Jan->Actual
Jan->Budget
Feb->Actual
Feb->Budget
Mar->Actual
Mar->Budget
Apr->Actual
Apr->Budget
May->Actual

@RELXRANGE operates on the XrangeList, returning lists of cross dimensional members within the defined offsets of -1 and 3.

If the current member being calculated is Jan->Actual, the count returned is 4 (offset of -1 is empty):


Jan->Actual (offset 0)
Jan->Budget (offset 1)
Feb->Actual (offset 2)
Feb->Budget (offset 3)

If the current member being calculated is Jan->Budget, the count returned is 5:


Jan->Actual (offset -1)
Jan->Budget (offset 0)
Feb->Actual (offset 1)
Feb->Budget (offset 2)
Mar->Actual (offset 3)

If the current member being calculated is Apr->Budget, the count returned is 3 (offsets of 2 and 3 are empty):


Apr->Actual (offset -1)
Apr->Budget (offset 0)
May->Actual (offset 1)

Incorrect

mbrCount=@COUNT(SKIPNONE,@RELXRANGE(0,0,@XRANGE(Sales->Jan->Actual,Sales->May->Actual)));

You cannot use Sales in the arguments for @XRANGE, because it is from the anchor dimension for mbrCount. Instead, reference a cross dimensional member with Sales and the @XRANGE function call, as shown in the correct example.