Returns a member list that crosses the specified member from one dimension (mbrName) with the specified member range from another dimension (rangeList). @RANGE can be combined with non-range functions, such as @AVG, which replaces an existing range function, such as @AVGRANGE.
@RANGE (mbrName [, rangeList])
Any valid single member name or member combination, or a function that returns a single member or member combination.
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 dimension tagged as Time.
Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX/ENDFIX statement.
The following example is based on the Sample Basic database. The @RANGE function is used with the @AVG function to determine the average sales for Colas in the West.
FIX(Sales) West=@AVG(SKIPBOTH,@RANGE(Sales,@CHILDREN(West))); ENDFIX
Since 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
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Prod Count. The @RANGE function is used with the @COUNT function 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