@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. -
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("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