@RANGE

The @RANGE calculation function returns a member list that crosses a member from one Essbase dimension with a member range from another dimension.

This function can be combined with non-range functions, such as @AVG, which replaces an existing range function, such as @AVGRANGE.

Syntax

@RANGE (mbrName [, rangeList])

Parameters

mbrName: Any valid single member name, or a function that returns a single member.
rangeList: Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions. If rangeList is not specified, Essbase uses the level 0 members from the Time dimension.

Notes

This function combined with the cross-dimensional operator (->) cannot be used inside a FIX statement.

Example 1

The following example is based on Sample Basic. @RANGE is used with @AVG to determine the average sales for Colas in the West:

FIX(Sales)
West=@AVG(SKIPBOTH,@RANGE(Sales,@CHILDREN(West)));
ENDFIX

As the calculation script fixes on Sales, only the Sales value for West are the average of the values for western states; COGS values for West are the sum of the western states. This example produces the following report:

                         Colas  
                Sales            COGS   
                Actual           Actual 

             Qtr3    Qtr4     Qtr3    Qtr4
             ====    ====     ====    ====                       
California   3401    2767     2070    1701
Oregon        932    1051      382     434
Washington   1426    1203      590     498
Utah         1168    1294      520     575
Nevada        496     440      222     197
  West       1484.6  1351     3784    3405

Example 2

For the following example, assume that the Measures dimension of Sample Basic contains an additional member, Prod Count. @RANGE is used with @COUNT to calculate the count of all products for which a data value exists:

"Prod Count" = @COUNT(SKIPMISSING,@RANGE(Sales,@CHILDREN(Product)));

This example produces the following report. Since SKIPMISSING is specified in the formula, the #MI value for Sales->Diet Drinks is not counted as a data value:

              Jan     New York     Actual
                 Sales      Prod Count  
                 =====      ==========                           
Colas             678          #MI
Root Beer         551          #MI
Cream Soda        663          #MI
Fruit Soda        587          #MI
Diet Drinks       #MI          #MI
  Product        2479           4