Essbase @XRANGE

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)
Parameter Description
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

  • @XRANGE can be used only in these functions: @AVGRANGE, @SUMRANGE,
    @MINRANGE, @MINSRANGE, @MAXRANGE, @MAXSRANGE, @STDDEVRANGE, @MOVSUM, @MOVAVG,
    @MOVMIN, @MOVMAX, @MOVMED, @SPLINE.
  • 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.
  • 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 a very simple example using cross dimensional members to return
the range between Actual, Jan and Budget, Mar:

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

This example returns the following members:

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

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("2001"->Mar, "2003"->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

  • @AVGRANGE
  • @SUMRANGE
  • @MINRANGE
  • @MINSRANGE
  • @MAXRANGE
  • @MAXSRANGE
  • @STDEVRANGE
  • @MOVSUM
  • @MOVAVG
  • @MOVMIN
  • @MOVMAX
  • @MOVMED
  • @SPLINE