@XRANGE

The @XRANGE calculation function for Essbase returns the range of members between (and inclusive of) two specified single or cross-dimensional members at the same level.

For example, when you work with the Time and Scenario dimensions, you can use @XRANGE to return a member set combination of Time and Scenario instead of creating a dimension that combines the two (which creates many more individual members than necessary).

@XRANGE is a member set function. Member set functions return a list of members. @XRANGE can appear anywhere in a formula where a range can normally appear.

Syntax

@XRANGE (mbrName1, mbrName2)

Parameters

mbrName1

Any valid member name, member combination, or function that returns a single member.

mbrName2

Any valid member name, member combination, or function that returns a single member. If mbrName1 is a cross-dimensional member (such as Actual->Jan), then mbrName2 must be also, and the dimension order must match the order used in mbrName1.

Notes

  • The two arguments to @XRANGE can be either both single members or both cross-dimensional members. For example, @XRANGE(Actual->Jan, Budget) is invalid because a single member and a cross dimensional member are used together. Both @XRANGE(Actual->Jan, Budget->Feb) and @XRANGE(Jan, Mar) are valid.

  • The dimension order of members must match for both arguments. For example, @XRANGE(Actual->Jun, Jul->Budget) is invalid because the two member components are in different orders. @XRANGE(Actual->Jun, Budget->Jul) is valid.

  • The order of dimensions you specify for cross-dimensional mbrnames drives the sequence of the tuples in the resulting tuples list. The right-most dimension is the most frequently incremented dimension. The increment of members in a dimension goes in outline order, or in the order of the arguments.

  • Although the syntax is correct, a function such as @XRANGE (Dec, Mar) is meaningless because it results in an empty set.

  • The member components of each argument must be from the same level. For example, @XRANGE(Actual->Jun, Budget->Qtr1) is invalid because Jun and Qtr1 are not from the same level.

Example

The following examples are based on the Sample Basic database.

Example 1

Here is a very simple example using simple members to return the range between Jan and Mar.

@XRANGE(Jan, Mar)

This example returns the following members:

Jan
Feb
Mar

Example 2

Here is an example using cross dimensional members to return a range.

If your range calculation crosses two or more dimensions, the syntax order in which you specify the dimensions makes a difference in how many dynamic dependencies are calculated. The iteration proceeds from left to right.

The first dimension specified in the cross dimensional members defines the start and end of the range, while the last dimension specified is iterated over fully.

Consider the following expression:

@XRANGE (Actual->Jan, Budget->Mar)

The iteration of members starts with Actual and ends with Budget:

Actual, Jan
Actual, Feb
Actual, Mar
Actual, Apr
Actual, May
Actual, Jun
Actual, Jul
Actual, Aug
Actual, Sep
Actual, Oct
Actual, Nov
Actual, Dec
Budget, Jan
Budget, Feb
Budget, Mar

If you need a smaller range, you can change the dimensional order:

@XRANGE (Jan->Actual, Mar->Budget)

The iteration of members starts with Jan and ends with Mar:

Jan, Actual
Jan, Budget
Feb, Actual
Feb, Budget
Mar, Actual
Mar, Budget

Example 3

This example is not based on the Sample Basic database. It is based on database that contains a dimension called Year that contains members for each year, from 2001 to 2003.

The following formula computes the average sales for all months between Mar of 2000 and Jan of 2001.

SalesAvg= @MOVAVG(Sales, 3, @XRANGE("2000"->Mar, "2001"->Jan));

This example returns the following members:

          Colas   New York   Actual              
                     Sales       SalesAvg       
                     =====       ========       
2000  
        Mar            678        678   
        Apr            645        645   
        May            675        666   
        Jun            712        677.3 
        Jul            756        714.3 
        Aug            890        786   
        Sep            924        856.7
        Oct            914        909.3
        Nov            912        916.7
        Dec            723        849.7
2001
        Jan            647        760.7

See Also