Essbase Functions


@ABS
@ACCUM
@ALLANCESTORS
@ALIAS
@ALLOCATE
@ANCEST
@ANCESTORS
@ANCESTVAL
@ATTRIBUTE
@ATTRIBUTEBVAL
@ATTRIBUTESVAL
@ATTRIBUTEVAL
@AVG
@AVGRANGE
@CALCMODE
@CHILDREN
@COMPOUND
@COMPOUNDGROWTH
@CONCATENATE
@CORRELATION
@COUNT
@CURGEN
@CURLEV
@CURRMBR
@CURRMBRRANGE
@DECLINE
@DESCENDANTS
@DISCOUNT
@EXP
@FACTORIAL
@GEN
@GENMBRS
@GROWTH
@IALLANCESTORS
@IANCESTORS
@ICHILDREN
@IDESCENDANTS
@ILSIBLINGS
@INT
@INTEREST
@IRDESCENDANTS
@IRR
@IRSIBLINGS
@ISACCTYPE
@ISANCEST
@ISCHILD
@ISDESC
@ISGEN
@ISIANCEST
@ISIBLINGS
@ISICHILD
@ISIDESC
@ISIPARENT
@ISISIBLING
@ISLEV
@ISMBR
@ISPARENT
@ISSAMEGEN
@ISSAMELEV
@ISSIBLING
@ISUDA
@LEV
@LEVMBRS
@LIST
@LN
@LOG
@LOG10
@LSIBLINGS
@MATCH
@MAX
@MAXRANGE
@MAXS
@MAXSRANGE
@MDALLOCATE
@MDANCESTVAL
@MDPARENTVAL
@MDSHIFT
@MEDIAN
@MEMBER
@MERGE
@MIN
@MINRANGE
@MINS
@MINSRANGE
@MOD
@MODE
@MOVAVG
@MOVMAX
@MOVMED
@MOVMIN
@NAME
@NEXT
@NEXTS
@NPV
@PARENT
@PARENTVAL
@POWER
@PRIOR
@PRIORS
@PTD
@RANGE
@RANK
@RDESCENDANTS
@RELATIVE
@REMAINDER
@REMOVE
@ROUND
@RSIBLINGS
@SANCESTVAL
@SHIFT
@SHIFTMINUS
@SHIFTPLUS
@SIBLINGS
@SLN
@SPARENTVAL
@SPLINE
@STDEV
@STDEVP
@STDEVRANGE
@SUBSTRING
@SUM
@SUMRANGE
@SYD
@TODATE
@TREND
@TRUNCATE
@UDA
@VAR
@VARPER
@VARIANCE
@VARIANCEP
@WITHATTR
@XREF

New Release 6 Functions

@ALLOCATE
@ANCEST
@ATTRIBUTE
@ATTRIBUTEVAL
@CORRELATION
@COUNT
@CURRMBR
@LIST
@MDALLOCATE
@MEDIAN
@MERGE
@MODE
@MOVAVG
@MOVMAX
@MOVMED
@MOVMIN
@PARENT
@RANGE
@RANK
@REMOVE
@SPLINE
@STDEV
@STDEVP
@STDEVRANGE
@TODATE
@TREND
@VARIANCE
@VARIANCEP
@WITHATTR
@XREF

New Release 6.1 Functions

@ATTRIBUTEBVAL
@ATTRIBUTESVAL
@CALCMODE
@CONCATENATE
@EXP
@IRDESCENDANTS
@LN
@LOG
@LOG10
@MAXS
@MAXSRANGE
@MEMBER
@MINS
@MINSRANGE
@RDESCENDANTS
@SUBSTRING

New Release 6.2 Functions

@ALIAS
@NAME
@NEXTS
@PRIORS
@SHIFTMINUS
@SHIFTPLUS

Member set functions can now be used within a FIX statement and in partition area definitions. Member set functions also support the cross-dimensional operator. See the List of Member Set Functions.


@ABS

The @ABS() function returns the absolute value of expression. The absolute value of a number is that number less its sign. A negative number becomes positive, while a positive number remains positive.

Syntax

@ABS(expression)

expression Member name or mathematical expression that generates a numeric value.

Example

The following example is based on the Demo Basic database. In this example, Variance needs to be presented as a positive number. The @ABS function is used because otherwise some combinations of Actual - Budget would return negative values.

Variance=@ABS(Actual-Budget); 

This example produces the following report:


Sales                 VCR      San_Francisco 
                      Jan      Feb      Mar 
                      ===      ===      ===					  
Actual              1,323    1,290    1,234 
Budget              1,200    1,100    1,100 
Variance              123      190      134

See Also

@INT
@REMAINDER
@ROUND
@TRUNCATE


@ALIAS

@ACCUM

The @ACCUM() function accumulates the values of mbrName within rangeList, up to the current member in the dimension of which rangeList is a part.

Syntax

@ACCUM (mbrName [, rangeList])

mbrName Any valid single member name or member combination (or a function that returns a single member or member combination) whose value is to be accumulated.
rangeList Optional comma-delimited list of members, member set functions, or range functions from the same dimension, across which the accumulation occurs. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

In this example, Accum_Asset is calculated using the following formula:

"Accum Asset" = @ACCUM(Asset, FY1992:FY1995);

This example produces the following report. This report shows that the values for Asset are accumulated starting with FY1992 and the accumulated values are placed in Accum_Asset for FY1992 through FY1995:


             FY1990   FY1991   FY1992   FY1993   FY1994   FY1995
            =======  =======  =======  =======  =======  =======
Asset         9,000        0    1,000        0   2 ,500    1,500
Residual        750        0        0        0  #MISSING #MISSING
Life              5        0        3        0  #MISSING #MISSING
Accum_Asset #MISSING #MISSING   1,000    1,000    3,500    5,000

@ALIAS

The @ALIAS() function takes a string as an argument and returns an alias name to the function that calls @ALIAS.

Syntax

@ALIAS (function_name)

Because functions that take strings as arguments may not function correctly if the string matches a member alias, use function @ALIAS to pass member alias names as strings, for example when passing alias names as strings to functions such as @ISUDA, @UDA, @CONCATENATE, @SUBSTRING, @MATCH, or @NAME.

Example

For example, if the value "US$" is both an alias and a user-defined attribute, pass the string using @ALIAS:

IF(@ISUDA(@ALIAS("US$")))
...
ENDIF


@ALLANCESTORS

The @ALLANCESTORS() function returns all ancestors of the specified member, including ancestors of any occurrences of the specified member as a shared member. This function excludes the specified member.

You can use @ALLANCESTORS as a parameter of another function, where that parameter is a list of members.

Syntax

@ALLANCESTORS(mbrName)

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

Notes

Example

The following example is based on the Sample Basic database. Sample Basic has a shared level of diet drinks, which includes 100-20 (Diet Cola). So 100-20 (Diet Cola) is a descendant of 100 (Colas) and is a shared member descendant of Diet:

100
      100-10
      100-20
        …
Diet
      100-20 (Shared Member)
        …

The following calculation script increases by 5% the Budget->Sales values of all ancestors of 100-20, including Diet.

FIX(Budget,@ALLANCESTORS("100-20"))
Sales = Sales * 1.05;
ENDFIX

This example produces the following report. This report shows that the Budget->Sales values for 100, Diet, and Product (the ancestors of 100-20) have been increased by 5%. The original values were 8980, 8260, and 28480, respectively.
 


                            Jan	
                     Actual      Budget
                     Sales       Sales
                     =====       =====
Market  100-10       4860        5200
        100-20       2372        2610
        100-30       1082        1170
         100         8314        9429  *
        100-20       2372        2610
        200-20       3122        3090
        300-30       2960        2560
         Diet        8454        8673  *
          Product   31538       30954  *		  


See Also

@IALLANCESTORS, which includes the specified member.


@ALLOCATE

The @ALLOCATE() function allocates values from a member, from a cross-dimensional member, or from a value across a member list within the same dimension. The allocation is based on a variety of criteria.

The @ALLOCATE function allocates values that are input at an upper level to lower-level members. The allocation is based upon a specified share or spread of another variable. For example, you can allocate values loaded to a parent member to all of that member's children. You can specify a rounding parameter for allocated values and account for rounding errors.

Syntax

@ALLOCATE (amount, allocationRange, basisMbr, [roundMbr],
method [, methodParams] [, round [, numDigits][, roundErr]])

amount A value, member, or cross-dimensional member that contains the value to be allocated into allocationRange. The value may also be a constant.
  • If amount is a member, the member must be from the dimension to which allocationRange belongs.
  • If amount is a cross-dimensional member, at least one of its members must be from the dimension to which allocationRange belongs.
  • If no member or cross-dimensional member is from the dimension to which allocationRange belongs, Essbase displays a warning message.
If the amount parameter is a loaded value, it cannot be a Dynamic Calc member.
allocationRange A comma-delimited list of members, member set functions, or range functions from the same dimension, into which value(s) from amount are allocated. allocationRange should be from only one level (for example, @CHILDREN(Total Expenses) rather than from multiple levels (for example, @DESCENDANTS(Product)).
basisMbr A value, member, or cross-dimensional member that contains the values that provide the basis for the allocation. The method you specify determines how the basis data is used.
roundMbr Optional. The member or cross-dimensional member to which rounding errors are added. The member (or at least one member of a cross-dimensional member) must be included in allocationRange.
method

The expression that determines how values are allocated. One of the following:

  • share:
    Uses basisMbr to calculate a percentage share. The percentage share is calculated by dividing the value in basisMbr for the current member in allocationRange by the sum across the allocationRange for that basis member:

    amount *   (@CURRMBR()->basisMbr/@SUM(allocationRange-> basisMbr)

  • spread:
    Spreads amount across allocationRange:

      amount * (1/@COUNT(SKIP, allocationRange))

    SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH: Values to be ignored during calculation of the spread. You must specify a SKIP parameter only for spread.
    • SKIPNONE: Includes all cells.
    • SKIPMISSING: Excludes all #MISSING values in basisMbr, and stores #MISSING for values in allocationRange for which the basisMbr is missing.
    • SKIPZERO: Excludes all zero (0) values in basisMbr, and stores #MISSING for values in allocationRange for which the basisMbr is zero.
    • SKIPBOTH: Excludes all zero (0) values and all #MISSING values, and stores #MISSING for values in allocationRange for which the basisMbr is zero (0) or #MISSING.

  • percent: Takes a percentage value from basisMbr for each member in allocationRange and applies the percentage value to amount:

      amount * (@CURRMBR()->basisMbr * .01)

  • add: Takes the value from basisMbr for each member of allocationRange and adds the value to amount:

      amount + @CURRMBR()->basisMbr

  • subtract: Takes the value from basisMbr for each member of allocationRange and subtracts the value from amount:

      amount - @CURRMBR()->basisMbr

  • multiply: Takes the value from basisMbr for each member of allocationRange and multiplies the value by amount:

      amount * @CURRMBR()->basisMbr

  • divide: Takes the value from basisMbr for each member of allocationRange and divides the value by amount:

      amount/@CURRMBR()->basisMbr
round Optional. One of the following:
  • noRound: No rounding. noRound is the default.
  • roundAmt: Indicates that you want to round the allocated values. If you specify roundAmt, you also must specify numDigits to indicate the number of decimal places to round to.
numDigits An integer that represents the number of decimal places to round to. You must specify numDigits if you specify roundAmt.
  • If numDigits is 0, the allocated values are rounded to the nearest integer. The default value for numDigits is 0.
  • If numDigits is greater than 0, the allocated values are rounded to the specified number of decimal places.
  • If numDigits is a negative value, the allocated values are rounded to a power of 10.
If you specify roundAmt, you also can specify a roundErr parameter.
roundErr Optional. An expression that specifies where rounding errors should be placed. You must specify roundAmt in order to specify roundErr. If you do not specify roundErr, Essbase discards rounding errors.

To specify roundErr, choose from one of the following:

  • errorsToHigh: Adds rounding errors to the member with the highest allocated value. If allocated values are identical, adds rounding errors to the first value in allocationRange.
  • errorsToLow: Adds rounding errors to the member with the lowest allocated value. If allocated values are identical, adds rounding errors to the first value in allocationRange. #MISSING is treated as the lowest value in a list; if multiple values are #MISSING, rounding errors are added to the first #MISSING value in the list.
  • errorsToMbr: Adds rounding errors to the specified roundMbr, which must be included in allocationRange.

Notes

Example

Consider the following example from the Sample Basic database. The example assumes that the Scenario dimension contains an additional member, PY Actual, for the prior year's actual expenses. Data values of 7000 and 8000 are loaded into Budget->Total Expenses for Jan and Feb, respectively. (For this example, assume that Total Expenses is not a Dynamic Calc member.)

You need to allocate values to each expense category (to each child of Total Expenses). The allocation for each of child of Total Expenses is based on the child's share of actual expenses for the prior year (PY Actual).:

FIX("Total Expenses")
Budget = @ALLOCATE(Budget->"Total Expenses",@CHILDREN("Total Expenses"),
"PY Actual",,share);
ENDFIX

This example produces the following report:


                          Product   Market		
                     PY Actual           Budget	
                    Jan     Feb        Jan     Feb
                    ===     ===        ===     ===				
Marketing          5223     5289     3908.60  4493.63
Payroll            4056     4056     3035.28  3446.05
Misc                 75       71       56.13    60.32
  Total Expenses   9354     9416     7000     8000

See Also

@MDALLOCATE


@ANCEST

The @ANCEST() function returns the ancestor at the specified generation or level of the current member being calculated in the specified dimension. If you specify the optional mbrName, that ancestor is combined with the specified member.

This member set function can be used as a parameter of another function, where that parameter is a member or list of members.

Syntax

@ANCEST(dimName, genLevNum [, mbrName])

dimName Single dimension name specification.
genLevNum An integer value that defines the generation or level number from which the ancestor value is returned. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, that is crossed with the ancestor returned.

Notes

Example

In the Sample Basic database:

Function                        Generated List

@ANCEST(Product,2,Sales)        Colas->Sales, if the current member of
                                Product being calculated is Diet Cola.

@ANCEST(Measures,3,East)        Total Expenses->East, if the current member of
                                Measures being calculated is Payroll.

See Also

@PARENT
@CHILDREN
@ANCESTORS
@DESCENDANTS
@SIBLINGS


@ANCESTORS

The @ANCESTORS() function returns all ancestors of the specified member (mbrName) or those up to a specified generation or level. You can use this member set function as a parameter of another function, where that parameter is a list of members.

Syntax

@ANCESTORS (mbrName [, genLevNum | genLevName])

mbrName Any valid single member name or member combination (or a function that returns a single member or member combination).
genLevNum Optional. An integer value that defines the absolute generation or level number up to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Optional. Level name or generation name up to which to select the members.

Notes

Example

In the Sample Basic database:

@ANCESTORS("New York")
returns East, Market (in that order).

@ANCESTORS(Qtr4)
returns Year.

@ANCESTORS("100-10",1)
returns 100, Product (in that order).

@ANCESTORS(Sales,-2)
returns Margin, Profit (in that order).

See Also

@IANCESTORS, which includes the specified member.
@ISANCEST
@CHILDREN
@DESCENDANTS
@SIBLINGS


@ANCESTVAL

The @ANCESTVAL() function returns the ancestor values of a specified member combination.

Syntax

@ANCESTVAL (dimName, genLevNum [, mbrName])

dimName A single dimension name that defines the focus dimension of ancestor values.
genLevNum Integer value that defines the generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference.
mbrName Optional. Any valid single member name or member combination (or a function that returns a single member or member combination).

Example

In this example, SKU Share is derived by taking Sales in each SKU as a percentage of its product family. Families are at generation 2; therefore, each descendant of family is calculated as a percentage its respective ancestor. Consolidated results must be calculated for Sales by Product before the SKU Share calculation occurs.

"SKU Share" = Sales % @ANCESTVAL(Product,2,Sales);

This example produces the following report:


              Sales   SKU Share
              =====   =========  
SKU101          510        26.0
SKU102          520        26.5
  Group01      1030        52.5
SKU120          430        21.9
SKU123          500        25.5
  Group02       930        47.4
    Family1    1960      100.00

See Also

@MDANCESTVAL
@SANCESTVAL
@PARENTVAL


@ATTRIBUTE

The @ATTRIBUTE() function generates a list of all base members that are associated with the specified attribute member (mbrName). This member set function can be used as a parameter of another function, where that parameter is a member or list of members.

Syntax

@ATTRIBUTE (attMbrName)

attMbrName Single attribute member name or member combination.

Notes

Examples

In the Sample Basic database,

@ATTRIBUTE(Can);
returns all base members with the Can attribute: Cola, Diet Cola, and Diet Cream.

Consider the following two calculation scripts, which are based on the Sample Basic database:



/* To increase the marketing budget for markets with large populations */
FIX (@ATTRIBUTE(Large))
Marketing = Marketing * 1.1;
ENDFIX


/* To calculate the average sales of bottled products */
"Bottle Sales" = @AVG(SKIPBOTH,@ATTRIBUTE(Bottle));

See Also

@WITHATTR
@ATTRIBUTEVAL


@ATTRIBUTEBVAL

For the current member being calculated, the @ATTRIBUTEBVAL() function returns the associated attribute value from the specified Boolean attribute dimension.

Syntax

@ATTRIBUTEBVAL(attDimName)

attDimName The name of a Boolean attribute dimension.

Notes

Example

This example is based on the Sample Basic database.

The Product dimension is associated with the Caffeinated Boolean attribute dimension, as shown in the following example:

Product {Caffeinated}
    100
       100-10 {Caffeinated:True}
       100-20 {Caffeinated:True}
       100-30 {Caffeinated:False}
    200
       200-10 {Caffeinated:True}
       200-20 {Caffeinated:True}
       200-30 {Caffeinated:False}
       200-40 {Caffeinated:False}
Caffeinated Attribute {Type: Boolean}
    True
    False

For the current member of the base dimension Product, the function

@ATTRIBUTEBVAL(Caffeinated)

returns the associated attribute value from the Boolean attribute dimension, Caffeinated. The following table shows the value that would be returned:

Current Member Return Value
100-10 True
100-20 True
100-30 False
100 #MISSING
200-10 True
200-20 True
200-30 False
200-40 False
200 #MISSING
Product #MISSING

For any any member that does not have an associated attribute, Essbase returns #MISSING. Only one value is returned at a time.

See Also

@ATTRIBUTEVAL
@ATTRIBUTESVAL


@ATTRIBUTESVAL

For the current member being calculated, the @ATTRIBUTESVAL() function returns the associated attribute value from the specified text attribute dimension.

Syntax

@ATTRIBUTESVAL(attDimName)

attDimName The name of a text attribute dimension.

Notes

Example

This example is based on the Sample Basic database.

The Product dimension is associated with the Pkg Type text attribute dimension, as shown in the following example:

Product {Pkg Type}
    100
       100-10 {Pkg Type:Can}
       100-20 {Pkg Type:Can}
       100-30 {Pkg Type:Bottle}
    200
       200-10 {Pkg Type:Bottle}
       200-20 {Pkg Type:Bottle}
       200-30 {Pkg Type:Bottle}
       200-40 {Pkg Type:Bottle}
Pkg Type Attribute {Type: Text}
    Bottle
    Can

For the current member of the base dimension, Product, the function

@ATTRIBUTESVAL("Pkg Type")

returns the associated attribute value from the text attribute dimension, Pkg Type. The following table shows the value that would be returned:

Current Member Return Value
100-10 Can
100-20 Can
100-30 Bottle
100 (empty string)
200-10 Bottle
200-20 Bottle
200-30 Bottle
200-40 Bottle
200 (empty string)
Product (empty string)

For any any member that does not have an associated attribute, Essbase returns an empty string.

See Also

@ATTRIBUTEVAL
@ATTRIBUTEBVAL


@ATTRIBUTEVAL

For the current member being calculated, the @ATTRIBUTEVAL() function returns the associated attribute value from the specified numeric or date attribute dimension.

Syntax

@ATTRIBUTEVAL(attDimName)

attDimNameSingle dimension specification for a numeric or date attribute dimension.

Notes

Examples

The following example is based on the Sample Basic database:

"Profit Per Ounce" = Profit/@ATTRIBUTEVAL(Ounces);

In this formula, for the current member being calculated, @ATTRIBUTEVAL returns the associated attribute from the Ounces numeric attribute dimension. For example, if the member being calculated is Cola and if the Ounces attribute value associated with Cola is 12, @ATTRIBUTEVAL returns 12. The value returned is then divided into Profit to yield Profit Per Ounce.

This example produces the following report:


              Actual      Year      West	   
              Profit        Profit Per Ounce
             ========       ================
Cola          4593            382.75

The following example applies a specific formula to members with a particular attribute:

Product
(IF (@ATTRIBUTEVAL(Caffeinated_True))
  Price = Price - Discount;
ENDIF;);

If the Product-dimension member being calculated has the Caffeinated_True attribute value associated with it, @ATTRIBUTEVAL returns 1 (True) and applies the Price formula to the member. If the current Product member does not have the Caffeinated_True attribute associated with it, Essbase does not apply the formula and returns #MISSING.

See Also

@ATTRIBUTEBVAL
@ATTRIBUTESVAL
@TODATE


@AVG

The @AVG() function returns the average of all values in expList.

Syntax

@AVG (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in the average operation regardless of their content.
SKIPMISSING Excludes all values that are #MISSING in the average operation.
SKIPZERO Excludes values of zero from the average calculation.
SKIPBOTH Excludes all values of zero or #MISSING from the average calculation.
expList Comma-delimited list of member names, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the average is calculated.

Example

The following example is based on the Sample Basic database. The calculation averages the values for the individual states making up the western region and places the results in West:

FIX(Sales)
West=@AVG(SKIPNONE,California:Nevada);
ENDFIX

This example produces the following report:


                     Sales      Jan       Actual	
                Cola    Diet Cola    Caffeine Free Cola
                ====    =========    ==================				
California       678       118             145
Oregon           160       140             150
Washington       130       190             #MI
Utah             130       190             170
Nevada            76        62             #MI
  West           234.8     140             155

See Also

@AVGRANGE


@AVGRANGE

The @AVGRANGE() function returns the average value of the specified member (mbrName) across the specified range (rangeList).

Syntax

@AVGRANGE( SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [, rangeList])

SKIPNONEIncludes all cells specified in the average operation regardless of their content.
SKIPMISSINGExcludes all values that are #MISSING in the average operation.
SKIPZEROExcludes values of zero from the average calculation.
SKIPBOTHExcludes all values of zero or #MISSING from the average calculation.
mbrNameAny valid single member or member combination.
rangeListOptional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example is based on the Sample Basic database. The calculation script determines the average sales of Colas in the West.

FIX(Sales)
West=@AVGRANGE(SKIPNONE,Sales,@CHILDREN(West));
ENDFIX

This example produces the following report:


              Sales   Colas   Actual
                Jan    Feb    Mar
                ===    ===    ===			
California      941    899    927
Oregon          450    412    395
Washington      320    362    377
Utah            490    488    476
Nevada          138    137    138
  West         467.8   459.6  462.6

See Also

@AVG


@CALCMODE

The @CALCMODE function enables the choice of an execution mode of a formula. @CALCMODE can control two types of modes:

Syntax

@CALCMODE (CELL|BLOCK|TOPDOWN|BOTTOMUP)

CELL Turns on the cell calculation mode
BLOCK Turns on the block calculation mode
TOPDOWN Turns on the top-down calculation mode
BOTTOMUP Turns on the bottom-up calculation mode

Cell and block modes are mutually exclusive. Top-down and bottom-up modes are mutually exclusive. Within one @CALCMODE specification, you can specify only one option. To specify both types of modes, perform the instruction twice; for example:

@CALCMODE (CELL)
@CALCMODE (TOPDOWN)

Description

Understanding Block Calculation and Cell Calculation Modes

Using block calculation mode, Essbase groups the cells within a block and simultaneously calculates the cells in each group. Block calculation mode is fast, but you must carefully consider data dependencies within the block to ensure that the resulting data is accurate.

Using cell calculation mode, Essbase calculates each cell sequentially, following the calculation order, which is based on the order of the dense dimensions in the outline. For more information on calculation order, see the Database Administrator's Guide.

For more information on which mode Essbase uses and on data dependency issues, see Notes.

Understanding Bottom-Up and Top-Down Calculation Modes

Essbase uses one of two calculation methods to do a full calculation of a database outline: bottom-up calculation (the default) or top-down calculation. If the database outline contains a complex member formula, Essbase performs a top-down calculation for that member. When a formula is compiled, if the formula is to be calculated top-down, Essbase logs a message in the application log file.

For a bottom-up calculation, Essbase determines which existing data blocks need to be calculated before it calculates the database. Essbase then calculates only the blocks that need to be calculated during the full database calculation. The calculation begins with the lowest existing block number and works up through each subsequent block until the last existing block is reached.

In contrast, a top-down calculation calculates the formula on all potential datablocks with the member. A top-down calculation may be less efficient than a bottom-up calculation because more blocks may be calculated than is necessary. Although a top-down calculation is less efficient than a bottom-up calculation, in some cases top-down calculations are necessary to ensure that calculation results are correct. See Example 4.

For more information about bottom-up and top-down calculation modes, see the Database Administrator's Guide.

Notes

Knowing When Essbase uses Cell or Block Mode and Top-down or Bottom-up Mode

Understanding Data Dependency Issues With Block Calculation Mode

Data dependency occurs if the accurate calculation of one or more members depends on another member or other on members being calculated previously. Most data dependency issues with block calculation mode occur when a formula contains IF ELSE or IF ELSEIF conditions. However, data dependencies can occur in other formulas; for example, when using the @PRIOR function.

Data Dependency Issues With IF ELSE and IF ELSEIF

When Essbase uses block calculation mode to calculate a formula that contains IF ELSE or IF ELSEIF conditions, it separates the members being calculated into two groups. The first group contains the members that satisfy the IF condition. The second group contains the members that satisfy the ELSE or ELSEIF conditions.

Essbase simultaneously calculates the members in the first group before simultaneously calculating the members in the second group. See Example 1.

If a formula contains data dependencies, ensure that the following conditions are met:

If an IF condition has multiple ELSEIF conditions, Essbase evaluates each ELSEIF condition, placing the members that satisfy the ELSEIF condition in the first group and the members that satisfy subsequent ELSEIF or ELSE conditions in the second group. See Example 2.

Understanding Other Data Dependency Issues

Data dependencies can occur in formulas that do not contain IF ELSE conditions. See Example 3 for an example of data dependency in a formula containing the @PRIOR function.

You can also set CALCMODE BLOCK or CALCMODE BOTTOMUP at the server, application, or database level using the configuration setting CALCMODE.

Examples

Example 1, Example 2, and Example 3 illustrate use of the BLOCK and CELL options of the @CALCMODE function. Example 4 illustrates use of the BOTTOMUP and TOPDOWN options.

Example 1

Consider a database with two dense dimensions, Time and Accounts. The following formula is placed on the Budget Sales member of the Accounts dimension. Because this is a formula containing @ISMBR applied to a dense member (Budget Sales), by default Essbase uses cell calculation mode. Use the @CALCMODE(BLOCK) function to specify block calculation mode for this formula.

@CALCMODE(BLOCK);
IF(@ISMBR(Feb))
   "Budget Sales"=100;
ELSE
   "Budget Sales"=Feb+10;

According to the above formula, we expect that if the member being calculated is Feb, the Budget Sales value is 100. If the member being calculated is not Feb, the Budget Sales value is 100+10 (the value for Feb + 10).

Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb and Mar, as follows:

Jan Feb Mar
Budget Sales 10 20 30

Using block calculation mode, Essbase calculates the members satisfying the IF condition first. In this example, Feb is the only member that satisfies the IF condition. After calculating Feb, Essbase calculates the members Jan and Mar. In this example, the results are as expected:

Jan Feb Mar
Budget Sales 110 100 110

Example 2

Now consider the same database as in Example 1, but we place the following formula on the Budget Sales member of the Accounts dimension. As in Example 1, because this is a formula containing @ISMBR applied to a dense dimension member (Budget Sales), by default Essbase uses cell calculation mode. However, we use the @CALCMODE(BLOCK) function to specify the block calculation mode for this formula.

@CALCMODE(BLOCK);
IF(@ISMBR(Mar))
   "Budget Sales"=Feb+20;
ELSEIF(@ISMBR(Jan))
   "Budget Sales"=Feb+10;
ELSE
   "Budget Sales"=100;

According to this formula, we want the Jan and Mar Budget Sales values to be calculated based on the Feb Budget Sales value, which is 100. We want to see the following results:

Jan Feb Mar
Budget Sales 110 100 120

Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb, and Mar, as follows:

Jan Feb Mar
Budget Sales 10 20 30

Using block calculation mode, Essbase calculates the members satisfying the IF condition first, followed by the members satisfying the ELSEIF condition, followed by the members satisfying the ELSE condition. In this example, Hyperion Essbase calculates the members in the following order: Mar, Jan, Feb. The results are not what we want, because the calculation of Jan and Mar is dependent on the calculation of Feb and Feb is calculated after Jan and Mar. The inaccurate results are as follows:

Jan Feb Mar
Budget Sales 30 100 40

To achieve the desired results, use the @CALCMODE(CELL) function.

Example 3

The following formula calculates the members Opening Inventory and Ending Inventory using the @PRIOR function. There is a data dependency between Opening Inventory and Ending Inventory. The formula is placed on the Opening Inventory member. The example shows the results for January, February, and March.

@CALCMODE(BLOCK)
"Opening Inventory"=@PRIOR("Ending Inventory")+10;
"Ending Inventory"="Opening Inventory";

Before the calculation, there is no data for these members (the data is #MISSING or #MI):

Jan Feb Mar
Opening Inventory #MI #MI #MI
Ending Inventory #MI #MI #MI

Using block calculation mode, Essbase calculates the members simultaneously, taking the previous month's Ending Inventory #MISSING value as 0 for all member combinations and adding 10. This is not the desired result.

Jan Feb Mar
Opening Inventory 10 10 10
Ending Inventory 10 10 10

The following formula on the Opening Inventory member causes Essbase to use cell calculation mode (the default for formulas containing the @PRIOR function):


"Opening Inventory"=@PRIOR("Ending Inventory")+10;

"Ending Inventory"="Opening Inventory";

The results are as follows:

Jan Feb Mar
Opening Inventory 10 20 30
Ending Inventory 10 20 30

Example 4

Depending on the formula and the structure of the data, calculating a formula top-down versus bottom-up may involve two issues: performance (reflecting the number of calculations that must be made) and accuracy. This example compares calculation results to illustrate both of these issues.

Before the calculation, assume that Actual and Budget are members of a sparse dimension and they contain the following data:

Cola New York Sales
Actual Budget  
Jan #MISSING 50
Feb 200 #MISSING
Mar 400 450

The following formula is calculated bottom-up.

@CALCMODE(BOTTOMUP);

Budget=Actual*1.10;

This bottom-up calculation calculates two values, based on existing combinations of Budget, with the following results:

Cola New York Sales (Comment)
Actual Budget  
Jan #MISSING #MISSING (#MISSING*1.10)
Feb 200 #MISSING (No calculation is performed)
Mar 400 440 (400*1.10)

The following formula is calculated top-down.

@CALCMODE(TOPDOWN); 

Budget=Actual*1.10;

This top-down calculation calculates three values, considering all potential combinations of Budget, with the following results:

Cola New York Sales (Comment)
Actual Budget  
Jan #MISSING #MISSING (#MISSING*1.10)
Feb 200 220 (200*1.10)
Mar 400 440 (400*1.10

See Also

@WITHATTR
CALCMODE configuration setting


@CHILDREN

The @CHILDREN () function returns all children of the specified member, excluding the specified member. This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@CHILDREN(mbrName)

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

Notes

Example

In the Sample Basic database:

@CHILDREN(Market)  

returns East, West, South, and Central (in that order).

@CHILDREN(Margin)

returns Sales and COGS (in that order).

See Also

@ICHILDREN, which includes the specified member.
@ISCHILD
@ANCESTORS
@DESCENDANTS
@SIBLINGS


@COMPOUND

The @COMPOUND() function compiles the proceeds of a compound interest calculation. The calculation is based on the balances of the specified member at the specified rate across the specified range.

Syntax

@COMPOUND(balanceMbr, rateMbrConst [, rangeList])

balanceMbr Single member specification representing the beginning balance across a range of periods. The input can be either one deposit or a series of deposits. If balanceMbr is a constant, then Essbase assumes balanceMbr to be a single deposit in the first member of rangeList. This is equivalent to entering the constant value in the first member in the rangeList followed by zeros. The function keeps track of each deposit separately, but returns a composite value. If balanceMbr is a member, or a range, then it is assumed to be a series of deposits.
rateMbrConst Single member specification, variable name, or numeric expression in decimal form. This represents the interest rate per time period specified in the rangeList. If your interest is compounded monthly, this value would be the annual interest rate divided by 12.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. rangeList represents the range over which the interest is compounded. The last value in the range is the total compounded interest for that range.

Notes

Example

The following example determines the compound interest of a series of deposits, based on a credit rate of 0.0525, across a series of fiscal years:

"Compound Interest"=@COMPOUND(Deposit,"Credit Rate",FY1991:FY1994,FY1995);

This example produces the following report:


               FY1991   FY1992   FY1993   FY1994   FY1995
               ======   ======   ======   ======   ======
Credit Rate    0.0525  0.0525    0.0525   0.0525    0.0525
Compound Interest   0     105  110.5125 273.8144  288.1897
Deposit             0   2,000         0    3,000         0

See Also

@INTEREST


@COMPOUNDGROWTH

The @COMPOUNDGROWTH() function calculates a series of values that represent a compound growth of the first nonzero value in the specified member across the specified range of members. The growth factor is calculated by multiplying the growth rate in the current time period by the previous period's result, yielding a compounded value. You can change the growth rate from period to period by placing a nonzero value in the current period's rateMbrConst cell.

Syntax

@COMPOUNDGROWTH(principalMbr, rateMbrConst [, rangeList])
principalMbr Member specification representing the initial value to be compounded. The input line must be a single deposit.
rateMbrConst Single member specification, variable name, or expression which provides a constant value. This value can change across rangeList, making the new value be the new compound rate. If the value in the current period is zero, the compound rate is equal to zero, and the principal does not change.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example determines the compound growth of Principal Amount based on Growth Rate across a series of fiscal years.

"Compound Growth"=@COMPOUNDGROWTH("Principal Amount",
"Growth Rate",FY1991:FY1996);

This example produces the following report:


                 FY1991 FY1992 FY1993 FY1994  FY1995  FY1996
                 ====== ====== ====== ======  ======  ======			 
Principal Amount  2,000  2,000  2,000  3,000  2,500    -500
Growth Rate      0.0525      0      0      0      0       0
Compound Growth   2,105  2,105  2,105  2,105  2,105   2,105

See Also

@GROWTH


@CONCATENATE

The @CONCATENATE function returns a character string that is the result of appending one character string to another character string.

Syntax

@CONCATENATE (String1, String2)

String1 A string or member name, or a function that returns a string or single member name
String2 A string or member name, or a function that returns a string or single member name

Description

This function creates a single character string by attaching one character string (String2) to the end of another character string (String1). The resulting string contains the characters from both source strings. The @CONCATENATE function can be nested to concatenate more than two strings (See Example 2).

Examples

The following examples are based on the Sample Basic database:

Example 1

The following function statement puts the string Product in front of the name of the member currently being processed in the Measures dimension; for example, if the current member being calculated is 100-10, the result is Product100-10:

@CONCATENATE("Product",@CURRMBR(Product))

Example 2

To concatenate more than two strings, you can nest multiple instances of the @CONCATENATE function. The following function statement returns string values starting with the current member of the Year dimension, followed by an underscore, followed by the current member of the Measures dimension; for example, if the current members being calculated are Qtr1 and Sales, the result is Qtr1_Sales:

@CONCATENATE(@CURRMBR(Year),@CONCATENATE("_",@CURRMBR(Measures)))

See Also

@SUBSTRING


@CORRELATION

The @CORRELATION() function returns the correlation coefficient between two parallel data sets (expList1 and expList2). The correlation coefficient determines the relationship between two data sets.

Syntax

@CORRELATION (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH,
expList1, expList2)

SKIPNONE Includes all cells specified in expList1 and expList2, regardless of their content, during calculation of the correlation coefficient.
SKIPMISSING Excludes all #MISSING values from expList1 and expList2 during calculation of the correlation coefficient.
SKIPZERO Excludes all zero (0) values from expList1 and expList2 during calculation of the correlation coefficient.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList1 and expList2 during calculation of the correlation coefficient.
expList1 The first list of member specifications, variable names, functions, or other numeric expressions.
expList2 The second list of member specifications, variable names, functions, or other numeric expressions.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Correl. The calculation script calculates the correlation coefficient for a set of members (Sales for the children of Qtr1 and Qtr2). Because the calculation script fixes on Jun, the results are placed in Sales Correl->Jun.

This example uses the @RANGE function to generate expList1 and expList2:

FIX(June)
"Sales Correl"=@CORRELATION(SKIPNONE,
  @RANGE(Sales,@CHILDREN(Qtr1)),@RANGE(Sales,@CHILDREN(Qtr2)));
ENDFIX

This example produces the following report:


             Colas     Actual    New York			  
               Sales       Sales Correl
               =====       ============
Jan             678             #MI
Feb             645             #MI
Mar             675             #MI
Apr             712             #MI
May             756             #MI
Jun             890        0.200368468

See Also

@RANGE


@COUNT

The @COUNT() function returns the number of data values in the specified data set (expList).

Syntax

@COUNT (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the count.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the count.
SKIPZERO Excludes all zero (0) values from expList during calculation of the count.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the count.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Prod Count. This example calculates the count of all products for which a data value exists and uses the @RANGE function to generate expList:

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

This example produces the following report. Since SKIPMISSING is specified in the calculation script, the #MI values for Diet Drinks are skipped during the product count.


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

See Also

@RANGE


@CURGEN

The @CURGEN() function returns the generation number of the current member combination for the specified dimension. This number represents the number of members separating the current member from the top-most member of the dimension.

Syntax

@CURGEN(dimName)

dimName Single dimension name specification. dimName must be the name of the top-most member of the dimension. It cannot be another member name from within the dimension.

Notes

Example

Given the following database structure:

     Year
       Qtr1
          Jan, Feb, Mar
       Qtr2
          Apr, May, Jun
       Qtr3
          Jul, Aug, Sep
       Qtr4
          Oct, Nov, Dec

@CURGEN provides the following results for the members shown:

Formula                      Current Member   Value
Position = @CURGEN(Year);    Year             1
Position = @CURGEN(Year);    Qtr2             2
Position = @CURGEN(Year);    Oct              3

See Also

@CURLEV
@GEN


@CURLEV

The @CURLEV() function returns the level number of the current member combination for the specified dimension. This number represents the number of members that separates the current member from its bottom-most descendant.

Syntax

@CURLEV(dimName)

dimName Single dimension name specification. dimName must be the name of the top-most member of the dimension. It cannot be another member name from within the dimension.

Notes

Example

Given the following database structure:


    Year
      Qtr1
          Jan, Feb, Mar
      Qtr2
          Apr, May, Jun
      Qtr3
          Jul, Aug, Sep
      Qtr4
          Oct, Nov, Dec

@CURLEV provides the following results for the members shown:

Formula                     Current Member   Value
Position = @CURLEV(Year);   Year             2
Position = @CURLEV(Year);   Qtr3             1
Position = @CURLEV(Year);   Aug              0

See Also

@CURGEN
@LEV


@CURRMBR

The @CURRMBR() function returns the member that is currently being calculated in the specified dimension (dimName). This function can be used as a parameter of another function, where that parameter is a single member or a list of members.

Syntax

@CURRMBR(dimName)

dimName A single dimension name specification.

Notes

Examples

In the Sample Basic database,

@CURRMBR(Year);

returns Jan if the current member of Year being calculated is Jan.


As a more complex example, consider the following formula in the context of the Sample Basic database. Assume that the Measures dimension contains an additional member, Average Sales.

"Average Sales"
(IF(@ISLEV(Product,0))
     Sales;
ELSE
     @AVGRANGE(SKIPNONE,Sales,@CHILDREN(@CURRMBR(Product)));
ENDIF;);

This formula populates each upper-level member of the Product dimension (100, 200) at Average Sales. To calculate Average Sales, the Sales values for the level 0 members of Product are averaged and placed in their respective parent members. The Average Sales values for the level 0 Product members are the same as the Sales values, as specified by the IF statement in the calculation script.

This example produces the following report:


          Jan        New York       Actual		  
              Sales        Average Sales	
              =====        =============	  
100-10          5               5	
100-20         10              10	
100-30         15              15	
  100          30              10	
200-10         20              20	
200-20         25              25	
200-30         30              30	
200-40         35              35	
  200         110              27.5	
  300         #MI             #MI
  400         #MI             #MI	
  Diet         35              11.67	
Product       140              35	

See Also

@CURRMBRRANGE


@CURRMBRRANGE

The @CURRMBRRANGE() function generates a member list that is based on the relative position of the current member being calculated.

Syntax

@CURRMBRRANGE(dimName, {GEN|LEV}, genLevNum, [startOffset], [endOffset])

dimName Name of the dimension for which you want to return the range list.
GEN|LEV Defines whether the range list to be returned is based on a generation or a level within the dimension.
genLevNum Integer value that defines the absolute generation or level number of the range list to be returned.
startOffset Defines the first member in the range to be returned.
  • A null value returns the first member of the specified genLevNum.
  • An integer value returns the member name relative to the current member being calculated.
  • A negative value specifies a member prior to the current member being calculated in the dimension.
  • A value of 0 returns the name of the member currently being calculated.
  • A positive value specifies a member after the current member being calculated in the dimension.
endOffset Defines the last member in the range to be returned.
  • A null value returns the last member of the specified genLevNum.
  • An integer value returns the member name relative to the current member being calculated.
  • A negative value specifies a member prior to the current member being calculated in the dimension.
  • A value of 0 returns the name of the member currently being calculated.
  • A positive value specifies a member after the current member being calculated in the dimension.

Notes

Examples

Example 1

Average Inventory is calculated by summing opening inventories from the first month of the year to the current period plus one period, and dividing the result by the number of periods to date plus one period. This calculation is accomplished by defining the @CURRMBRRANGE function within the rangeList parameter of the @AVGRANGE function.

"Average Inventory" = @AVGRANGE(SKIPNONE,"Opening Inventory",
@CURRMBRRANGE(Year, LEV, 0, , 1));

This example produces the following result:


                    Jan   Feb   Mar   Apr       Nov   Dec
Opening Inventory   100   110   120   130 . . . 200   210
Average Inventory   105   110   115   120 . . ..155   155 

Since a null value is specified for startOffset, the average operations always begin at the first member of the range list, Jan. The endOffset parameter, 1, specifies that the member after the current member being calculated is included in each average operation. So, for Average Inventory->Jan, the values for Jan and Feb are averaged; for <Average Inventory->Feb, the values for Jan, Feb, and Mar are averaged; and so on. The values for Nov and Dec are the same since there is no member after Dec in the range list.


Example 2

Inventory Turnover is calculated by summing period-to-date Sales and dividing the result by the Average Inventory.

Turnover = @SUMRANGE(Sales,@CURRMBRRANGE(Year, LEV, 0, , 0))/"Average Inventory"

which produces the following result:


                    Jan    Feb     Mar     Apr
Average Inventory   110    116.7   122.5   126
Sales                40     44      48      52
Turnover              0.36   0.72    1.08    1.46


@DECLINE

The @DECLINE() function calculates the depreciation of an asset for the specified period using the declining balance method. The factor by which the declining balance depreciates the assets is specified using factorMbrConst. For example, to calculate a double declining balance, set factorMbrConst to 2.

Syntax

@DECLINE (costMbr, salvageMbrConst, lifeMbrConst,
factorMbrConst [, rangeList])

costMbr Single member specification representing the starting values of the assets. More than one asset can be input and depreciated across the specified range. The function calculates each asset separately.
salvageMbrConst Single member specification, variable name, or numeric expression that provides a constant value. This value represents the value of the asset at the end of the depreciation.
lifeMbrConst Single member specification, variable name, or numeric expression that provides a constant value. The value represents the number of periods over which the asset is depreciated.
factorMbrConst Single member specification, variable name, or numeric expression that provides a constant value. The value represents the factor by which the asset is depreciated.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. The range represents the periods over which the function is calculated. More than one asset can be depreciated.

Notes

Example

The following example calculates the depreciation of Asset for the specified series of fiscal years.


"Decline Dep" = @DECLINE(Asset,Residual,Life,2,FY1991:FY1992,FY1993,FY1994);

This example produces the following report:


            FY1991    FY1992    FY1993    FY1994
            ======    ======    ======    ======		
Asset        9,000         0         0         0
Residual       750         0         0         0
Life             5         0         0         0
Decline Dep  3,600     2,160     1,296       778

See Also

@SLN
@GROWTH


@DESCENDANTS

The @DESCENDANTS function returns all descendants of the specified member, or those down to the specified generation or level. This function excludes the specified member. You can use this member set function as a parameter of another function, where that parameter is a list of members.

Syntax

@DESCENDANTS(mbrName [, genLevNum| genLevName])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
genLevNum Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Optional. Level name or generation name down to which to select the members.

Notes

Example

In the Sample Basic database:

@DESCENDANTS(East)

returns New York, Massachusetts, Florida, Connecticut, and New Hampshire (in that order).

@DESCENDANTS(Profit)

returns Margin, Sales, COGS, Total Expenses, Marketing, Payroll, and Misc (in that order).

@DESCENDANTS(Market,2)

returns East, West, South, and Central (in that order).

@DESCENDANTS(Diet,0)

returns 100-20, 200-20, and 300-30 (in that order).

See Also

@IDESCENDANTS
@RDESCENDANTS
@IRDESCENDANTS
@ISDESC
@ANCESTORS
@CHILDREN
@SIBLINGS


@DISCOUNT

The @DISCOUNT() function calculates a value discounted by the specified rate, from the first period of the range to the period in which the amount to discount is found. The answer is returned in the same period. More than one value can be discounted simultaneously in this manner.

Syntax

@DISCOUNT (cashMbr, rateMbrConst [, rangeList])

cashMbr Member specification representing the value you want to discount from the last period in rangeList to the current period.
rateMbrConst Member specification, variable name, or numeric expression which provides a constant value. The value represents the rate per period which cashMbr is discounted. It is a decimal value, not a percent.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example discounts the values in Cash by the rates in Credit Rate and places the results in Discount Amount for each fiscal year.

"Discount Amount" = @DISCOUNT(Cash,"Credit Rate",FY1991:FY1994,FY1995);

This example produces the following report:


                    FY1991  FY1992  FY1993  FY1994  FY1995
                    ======  ======  ======  ======  ======			  
Cash                  0.00    0.00 1000.00 1000.00    0.00
Credit Rate           0.00    0.00    0.05    0.05    0.00
Discount Amount        #MI     #MI  863.84  822.70    #MI	 

@EXP

The @EXP() function returns the exponent of a specified expression; that is, the value of e (the base of natural logarithms) raised to the power of the specified expression.

Syntax

@EXP (expression)

expression Single member specification, variable name, function, or other numeric expression. If less than -700 or greater than 700, Essbase returns #MISSING.

Example

The following example is based on a variation of Sample Basic:

Index = @EXP("Variance %"/100);

This example produces the following result:


                 East      West     South   Central  
Variance %       10.7      10.9       3.6       3.6    
Index         1.11293   1.11516   1.03666   1.03666  

See Also

@LN


@FACTORIAL

The @FACTORIAL() function returns the factorial of expression. The factorial of a number is equal to 1*2*3*...* number.

Syntax

@FACTORIAL(expression)

expression Single member specification or numeric expression.

Notes

Example

@FACTORIAL(1)     1
@FACTORIAL(5)     120

See Also

@POWER


@GEN

The @GEN() function returns the generation number of the specified member.

Syntax

@GEN(mbrName)

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

Example

In the Sample Basic database:

@GEN(Year)              1
@GEN(Qtr3)              2

See Also

@CURGEN
@LEV


@GENMBRS

The @GENMBRS() function returns all members with the specified generation number or generation name in the specified dimension.

Syntax

@GENMBRS(dimName,genName|genNum)

dimName A single dimension name specification.
genName|genNum Generation name or generation number from dimName. A positive integer defines a generation number.

Notes

Example

In the Sample Basic database:

@GENMBRS(Year,Month)
@GENMBRS(Year,3)

both return the following members since generation 3 of the Year dimension is named Month:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec (in that order).

The following example restricts the calculation to members with the combination Budget and one of the members of the Market dimension with a generation name of State.

FIX(Budget,@GENMBRS(Market,State))
   CALC DIM (Year,Measures);
ENDFIX

See Also

@LEVMBRS


@GROWTH

The @GROWTH() function calculates a series of values that represent a linear growth of the first nonzero value encountered in principalMbr across the specified rangeList. Growth is calculated by multiplying the growth rate in rateMbrConst by the original principalMbr. This value is then added to the previous time period's result, yielding the new value.

Syntax

@GROWTH(principalMbr, rateMbrConst [, rangeList])

principalMbr Single member specification that represents the initial value of the value to grow. The first nonzero value encountered is the initial value. Other principalMbr values after the first are ignored.
rateMbrConst Single member specification, variable name, or numeric expression providing a constant value that represents the decimal growth rate to be applied (for example, 10% = .1).
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example calculates the growth of Principal Amount, using the rate found in Growth Rate for each fiscal year. The results are placed in Growth Amount.

"Growth Amount"=@GROWTH("Principal Amount","Growth Rate",FY1991:FY1996);

This example produces the following report:


                   FY1991 FY1992 FY1993 FY1994 FY1995 FY1996
                   ====== ====== ====== ====== ====== ======			 
Principal Amount    1,000      0  2,000      0      0      0
Growth Amount       1,050  1,120  1,200  1,280  1,380  1,480
Growth Rate          0.05   0.07   0.08   0.08    0.1    0.1

See Also

@COMPOUNDGROWTH
@DECLINE


@IALLANCESTORS

The @IALLANCESTORS() function returns the specified member and all the ancestors of that member, including ancestors of any occurrences of the specified member as a shared member. You can use this member set function as a parameter of another function, where that parameter is a list of members.

Syntax

@IALLANCESTORS(mbrName)

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

Notes

Examples

The following example is based on the Sample Basic database. Sample Basic has a shared level of diet drinks, which includes 100-20 (Diet Cola). So 100-20 (Diet Cola) is a descendant of 100 (Colas) and is a shared member descendant of >Diet:

100
      100-10
      100-20
       …
Diet
      100-20 (Shared Member)
      …

The following calculation script increases by 5% the Budget Sales values of 100-20 and all its ancestors, including Diet:

FIX(Budget,@IALLANCESTORS("100-20"))
Sales = Sales * 1.05;
ENDFIX

This example produces the following report. This report shows that the Budget->Sales values for 100-20, 100, Diet, and Product (100-20 and its ancestors) have been increased by 5%. The original values were 2610, 8980, 8260, and 28480, respectively.


                            Jan	
                     Actual      Budget
                     Sales       Sales
                     =====       =====
Market  100-10       4860        5200
        100-20       2372        2740.5 *
        100-30       1082        1170
         100         8314        9429   *
        100-20       2372        2610
        200-20       3122        3090
        300-30       2960        2560
         Diet        8454        8673   *
          Product   31538       30954   * 

See Also

@ALLANCESTORS, which excludes the specified member.
@IANCESTORS


@IANCESTORS

The @IANCESTORS() function returns the specified member and either (1) all ancestors of the specified member or (2) all ancestors up to the specified generation or level. You can use this member set function as a parameter of another function, where that parameter is a list of members.

Syntax

@IANCESTORS (mbrName [, genLevNum | genLevName])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
genLevNum Optional. An integer value that defines the absolute generation or level number up to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Optional. Level name or generation name up to which to select the members.

Notes

Examples

In the Sample Basic database:

@IANCESTORS("New York")

returns New York, East, Market (in that order).

@IANCESTORS(Qtr4)

returns Qtr4, Year (in that order).

@IANCESTORS(Sales,-2)

returns Sales, Margin, Profit (in that order). Members higher than level 2 are not returned, as specified in the formula.

@IANCESTORS("100-10",1)

returns 100-10, 100, Product (in that order). All ancestors are returned (up to generation 1), as specified in the formula.

See Also

@ANCESTORS, which excludes the specified member.
@IALLANCESTORS


@ICHILDREN

The @ICHILDREN() function returns the specified member and all of its children. This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@ICHILDREN(mbrName)

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

Notes

Examples

In the Sample Basic database:

@ICHILDREN(Market)  

returns Market, East, West, South, and Central (in that order).

@ICHILDREN(Margin)

returns Margin, Sales, and COGS (in that order).

See Also

@CHILDREN, which excludes the specified member.


@IDESCENDANTS

The @IDESCENDANTS function returns the specified member and either (1) all descendants of the specified member or (2) all descendants down to a specified generation or level. You can use this member set function as a parameter of another function, where that parameter is a list of members.

Syntax

@IDESCENDANTS(mbrName[, genLevNum | genLevName])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
genLevNum Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Optional. Level name or generation name down to which to select the members.

Notes

Example

In the Sample Basic database:

@IDESCENDANTS(East)

returns East, New York, Massachusetts, Florida, Connecticut, and New Hampshire (in that order).

@IDESCENDANTS(Profit)

returns Profit, Margin, Sales, COGS, Total Expenses, Marketing, Payroll, and Misc (in that order).

@IDESCENDANTS(Market,2)

returns Market, East, West, South, and Central (in that order).

@IDESCENDANTS(South,-1) returns South.

See Also

@DESCENDANTS
@IRDESCENDANTS
@RDESCENDANTS
@ISDESC
@ANCESTORS
@CHILDREN
@SIBLINGS


@ILSIBLINGS

The @ILSIBLINGS() function returns the specified member and all of the left siblings of the member. Left siblings are children that share the same parent as the member and that precede the member in the database outline.

This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@ILSIBLINGS(mbrName)

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

Notes

Example

In the Sample Basic database:

@ILSIBLINGS(Florida)

returns New York, Massachusetts, and Florida (in that order). New York and Massachusetts appear above Florida in the Sample Basic outline.

@ILSIBLINGS(Qtr3)

returns Qtr1, Qtr2, and Qtr3 (in that order). Qtr1 and Qtr2 appear above Qtr3 in the Sample Basic outline.

See Also

@LSIBLINGS, which excludes the specified member.


@INT

The @INT() function returns the next lowest integer value of expression.

Syntax

@INT (expression)

expression Member specification or mathematical expression that generates a numeric value.

Example

The following example is based on the Sample Basic database. Assume that the Profit % member is not tagged as Dynamic Calc.

The following formula rounds the values for West down to the nearest integer.

West=@INT(@SUM(@CHILDREN(West)));

This example produces the following report:


                    Profit %
                Cola      Actual	
               Jan    Feb    Mar
               ===    ===    ===
California    38.64  37.98  38.37
Oregon        17.50  16.13  16.11
Washington    29.23  30.90  32.00
Utah          23.08  23.08  20.97
Nevada        -3.95  -6.76  -5.33
  West       104     101   102

See Also

@ABS
@REMAINDER
@ROUND
@TRUNCATE


@INTEREST

The @INTEREST() function calculates the simple interest in balanceMbr at the rate specified by creditrateMbrConst if the value specified by balanceMbr is positive, or at the rate specified by borrowrateMbrConst if balanceMbr is negative. The interest is calculated for each time period specified by rangeList.

Syntax

@INTEREST(balanceMbr, creditrateMbrConst, borrowrateMbrConst
[, rangeList])

balanceMbr Single member specification representing the balance at the time the interest is calculated.
creditrateMbrConst Single member specification, variable name, or numeric expression providing a constant value. The value must be a decimal number that corresponds to a percentage. The value represents the per-period interest rate.
borrowrateMbrConst Single member specification, variable name, or numeric expression providing a constant value. The value must be a decimal number corresponding to a percentage value. The value represents the per-period interest rate.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

This example calculates the interest for Balance, using Credit Rate for positive balances and using Borrow Rate for negative balances. The results are placed in Interest Amount for each fiscal year.

"Interest Amount" = @INTEREST(Balance,"Credit Rate","Borrow Rate",
FY1991:FY1994,FY1995,FY1996);

This example produces the following report:


                FY1991  FY1992   FY1993  FY1994   FY1995   FY1996
                ======  ======   ======  ======   ======   ======				
Balance        2000.00 3000.00 -1000.00 3000.00  9000.00 -6000.00
Credit Rate      0.065   0.065    0.065   0.065    0.065    0.065
Borrow Rate     0.1125  0.1125   0.1125  0.1125   0.1125   0.1125
Interest Amount 130.00  195.00  -112.50  195.00   585.00  -675.00


See Also

@COMPOUND


@IRDESCENDANTS

The @IRDESCENDANTS function returns the specified member and either (1) all descendants of the specified member or (2) all descendants down to an optionally specified generation or level. If one or more of the descendants are shared, the result also includes either all descendants of each member being shared or descendants down to the specified generation or level.

You can use this member set function as a parameter of another function, where that parameter is a list of members. In the absence of shared members, @IRDESCENDANTS and @IDESCENDANTS have identical behavior.

Syntax

@IRDESCENDANTS(mbrName[, genLevNum | genLevName])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination
genLevNum Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Optional. Level name or generation name down to which to select the members.

Notes

Example

Example 1

Assume a variation of the Sample Basic database such that the Product dimension includes the following members:

Product
   100
     100-10
     100-20
     100-30
   200
     200-10
     200-20
     200-30
     200-40
   Diet
     100 (Shared Member)
     200 (Shared Member)

Diet has two children "100" and "200" instead of "100-10", "200-20" and "300-30". The members "100" and "200" are shared members.

@IRDESCENDANTS(Diet)

returns the members: Diet, 100, 100-10, 100-20, 100-30, 200, 200-10, 200-20, 200-30, 200-40 (in that order).

Example 2

@IRDESCENDANTS(East)

returns East, New York, Massachusetts, Florida, Connecticut, and New Hampshire (in that order) and is exactly the same as @IDESCENDANTS(East).

See Also

@RDESCENDANTS
@IDESCENDANTS
@DESCENDANTS
@ISDESC
@ICHILDREN
@ISIBLINGS
@IANCESTORS


@IRR

The @IRR() function calculates the Internal Rate of Return on a cash flow that must contain at least one investment (negative) and one income (positive) value.

Syntax

@IRR(cashflowMbr, discountFlag[, rangeList])

cashflowMbr Single member specification.
discountFlag Member specification, variable name, or numeric expression providing a constant value of either 1 or 0. discountFlag indicates whether the function should discount from the first period. 1 means do not discount from the first period.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

This example calculates the Internal Rate of Return (Return) on a cash flow (Cash).

Return = @IRR(Cash,0,FY1990:FY1994,FY1995:FY2000);

This example produces the following report:


         FY1990  FY1991  FY1992  FY1993  FY1994    FY1995
         ======  ======  ======  ======  ======    ======		 
Cash    (1,000)    500    600    500   #MISSING   #MISSING
Rate         0       0      0      0   #MISSING   #MISSING
Return       0       0      0      0          0          0

@IRSIBLINGS

The @IRSIBLINGS() function returns the specified member and all of the right siblings of the specified member. Right siblings are children that share the same parent as the member and that follow the member in the database outline.

This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@IRSIBLINGS(mbrName)

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

Notes

Example

In the Sample Basic database:

@IRSIBLINGS(Florida)    

returns Florida, Connecticut, and New Hampshire (in that order). Connecticut and New Hampshire appear below Florida in the Sample Basic outline.

@IRSIBLINGS(Qtr3)

returns Qtr3 and Qtr4 (in that order). Qtr4 appears below Qtr3 in the Sample Basic outline.

See Also

@RSIBLINGS, which excludes mbrName from the list.


ISACCTYPE

The @ISACCTYPE() function returns TRUE if the current member has the associated accounts tag.

Syntax

@ISACCTYPE(tag)

tag Valid account tag defined in the current database. Any of the following tags may be used: First, Last, Average, Expense, and Two-Pass.

Example

The following example is based on the Sample Basic database. For members with the Expense accounts tag, the formula uses the @ABS function to calculate Budget as the absolute value of Budget.

IF (@ISACCTYPE(Expense))
   Budget = @ABS(Budget);
ENDIF;

@ISANCEST

The @ISANCEST() function returns TRUE if the current member is an ancestor of the specified member. This function excludes the specified member.

Syntax

@ISANCEST(mbrName)

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

Example

In the Sample Basic database:

@ISANCEST(California)   TRUE    for Market, West

@ISANCEST(West)         FALSE   for California, West, East

See Also

@ISIANCEST, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.


@ISCHILD

The @ISCHILD() function returns TRUE if the current member is a child of the specified member. This function excludes the specified member.

Syntax

@ISCHILD(mbrName)

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

Example

In the Sample Basic database:

@ISCHILD(East)    TRUE   for New York, Florida, Connecticut

@ISCHILD(Margin)  FALSE  for Measures, Profit, Margin

See Also

@ISICHILD, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.


@ISDESC

The @ISDESC() function returns TRUE if the current member is a descendant of the specified member. This function excludes the specified member.

Syntax

@ISDESC(mbrName)

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

Example

In the Sample Basic database:

@ISDESC(Market)   TRUE    for West, California, Oregon, Washington,
                            Utah, Nevada

@ISDESC(Profit)   FALSE   for Measures, Profit, Profit %

See Also

@ISIDESC, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.


@ISGEN

The @ISGEN() function returns TRUE if the current member of the specified dimension is in the specified generation.

Syntax

@ISGEN(dimName, genName | genNum)

dimName The name of a dimension.
genName | genNum Generation name specification, or a non-negative number that defines the number of a generation.

Example

In the Sample Basic database:

@ISGEN(Measures,3)  

returns TRUE if the current member is Margin, Total Inventory, or Margin %, because these members are all in generation 3 of the Measures dimension.

@ISGEN(Market,2)

returns FALSE if the current member is New York or Market, because these members are not in generation 2 of the Market dimension.

See Also

@ISSAMEGEN
@ISLEV


@ISIANCEST

The @ISIANCEST() function returns TRUE if the current member is the specified member or an ancestor of the specified member. This function includes the specified member.

Syntax

@ISIANCEST(mbrName)

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

Example

In the Sample Basic database:

@ISIANCEST(California)

returns TRUE for Market, West, and California. California is the specified member, and West and Market are ancestors of California.

@ISIANCEST(Qtr1)

returns FALSE for Jan, Feb, Mar, Qtr2. None of these members is the specified member (Qtr1) or an ancestor of Qtr1.

See Also

@ISANCEST, which excludes the specified member.


@ISIBLINGS

The @ISIBLINGS() function returns the specified member and all siblings of that member. This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@ISIBLINGS(mbrName)

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

Notes

Essbase sorts the generated list of members in ascending order.

Using Sample Basic as an example, if you specify 200-30 for mbrName, Essbase returns 200-10, 200-20, 200-30, 200-40 (in that order). This order is important to consider when you use the @ISIBLINGS member set function with certain forecasting and statistical functions.

Example

In the Sample Basic database:

@ISIBLINGS(California)   

returns California, Oregon, Washington, Utah, and Nevada (in that order), because these members are siblings of California.

@ISIBLINGS(Qtr2)

returns Qtr1, Qtr2, Qtr3, and Qtr4 (in that order), because these members are siblings of Qtr2.

See Also

@SIBLINGS, which excludes the specified member.


@ISICHILD

The @ISICHILD() function returns TRUE if the current member is the specified member or a child of the specified member.

Syntax

@ISICHILD(mbrName)

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

Example

In the Sample Basic database:

@ISICHILD(South)   TRUE   for Texas, Oklahoma, Louisiana,
                            New Mexico, South

@ISICHILD(Profit)  FALSE  for Measures, Sales

See Also

@ISCHILD, which excludes the specified member.


@ISIDESC

The @ISIDESC() function returns TRUE if the current member is the specified member or a descendant of the specified member.

Syntax

@ISIDESC(mbrName)

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

Example

In the Sample Basic database:

@ISIDESC(South)  TRUE   for Texas, Oklahoma, Louisiana,
                          New Mexico, South

@ISIDESC(West)   FALSE  for Market, East, South, and Central

See Also

@ISDESC, which excludes the specified member.


@ISIPARENT

The @ISIPARENT() function returns TRUE if the current member is the specified member or the parent of the specified member.

Syntax

@ISIPARENT(mbrName)
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.

Example

In the Sample Basic database:

@ISIPARENT(Qtr1)     TRUE   for Year, Qtr1
@ISIPARENT(Margin)   FALSE  for Measures, Sales

See Also

@ISPARENT, which excludes the specified member.


@ISISIBLING

The @ISISIBLING() function returns TRUE if the current member is the specified member or a sibling of the specified member.

Syntax

@ISISIBLING(mbrName)
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.

Example

In the Sample Basic database:
@ISISIBLING(Qtr2)    TRUE   for Qtr1, Qtr2, Qtr3, and Qtr4
@ISISIBLING(Actual)  FALSE  for Scenario

See Also

@ISSIBLING, which excludes the specified member.


@ISLEV

The @ISLEV() function returns TRUE if the current member of the specified dimension is in the specified level.

Syntax

@ISLEV(dimName, levName | levNum)
dimName Name of a dimension.
levName | levNum A level name or an integer value that defines the number of a level. A value of 0 or a negative integer defines a level number.

Example

In the Sample Basic database:

@ISLEV(Market,0)    TRUE   if the current member of Market is New York,
                              California, Texas, or Illinois

@ISLEV(Year,1)      FALSE  if the current member of Year is Jan, Feb,
                              or Mar

See Also

@ISSAMELEV
@ISGEN


@ISMBR

The @ISMBR() function returns TRUE if the current member matches any one of the specified members.

Syntax

@ISMBR(mbrName | rangeList | mbrList)

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
rangeList A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension.
mbrList A comma-delimited list of members.

If any term returns TRUE, the @ISMBR function returns TRUE.

Notes

Example

In the Sample Basic database:

@ISMBR("New York":"New Hampshire")   TRUE   for Florida
@ISMBR(@CHILDREN(Qtr1))              FALSE  for Qtr2, Year

@ISPARENT

The @ISPARENT() function returns TRUE if the current member is the parent of the specified member. This function excludes the specified member.

Syntax

@ISPARENT(mbrName)

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

Example

In the Sample Basic database:

@ISPARENT("New York")  TRUE   for East
@ISPARENT(Profit)      FALSE  for Margin

See Also

@ISIPARENT, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.


@ISSAMEGEN

The @ISSAMEGEN() function returns TRUE if the current member is the same generation as the specified member.

Syntax

@ISSAMEGEN(mbrName)

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

Example

In the Sample Basic database:

@ISSAMEGEN(West)     TRUE   for East
@ISSAMEGEN(West)     FALSE  for California

See Also

@ISGEN
@GEN
@ISSAMELEV


@ISSAMELEV

The @ISSAMELEV() function returns TRUE if the current member is the same level as the specified member.

Syntax

@ISSAMELEV(mbrName)
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.

Example

In the Sample Basic database:

@ISSAMELEV(Sales)     FALSE  for Total Expenses 
@ISSAMELEV(Jan)       TRUE   for Apr, Jul, Oct

See Also

@ISLEV
@LEV
@ISSAMEGEN


@ISSIBLING

The @ISSIBLING() function returns TRUE if the current member is a sibling of the specified member. This function excludes the specified member.

Syntax

@ISSIBLING(mbrName)
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.

Example

In the Sample Basic database:

@ISSIBLING("New York")  TRUE   for Florida, New Hampshire
@ISSIBLING(Sales)       FALSE  for Margin

See Also

@ISISIBLING, which includes the specified member.


@ISUDA

The @ISUDA() function returns TRUE if the specified user-defined attribute (UDA) exists for the current member of the specified dimension at the time of the calculation.

Syntax

@ISUDA(dimName,UDAStr)

dimName Dimension name specification that contains the member you are checking.
UDAStr user-defined attribute (UDA) name string.

Notes

Example

This example is based on the Sample Basic database. The Market dimension has members that indicate a geographic location. Some members represent major markets. The example below calculates the database and stores a budget amount for the upcoming year based on the actual amount from this year. A different sales growth rate is applied to major markets than to small markets.

FIX (Budget)
   Sales (IF(@ISUDA(Market,"Major Market")) 
      Sales = Sales->Actual * 1.2; 
   ELSE 
      Sales = Sales->Actual * 1.1; 
   ENDIF;);
ENDFIX

This example tests to see if the current member of Market has a UDA called "Major Market". If it does, the Budget->Sales value is set to 120% of Actual->Sales. If it does not, the Budget->Sales value is set to 110% of Actual->Sales.

See Also

@UDA


@LEV

The @LEV() function returns the level number of the specified member.

Syntax

@LEV(mbrName)

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

Example

In the Sample Basic database:

Function           Level Returned 
@LEV(Margin)       1
@LEV("New York")   0

See Also

@CURLEV
@GEN


@LEVMBRS

The @LEVMBRS() function returns all members with the specified level number or level name in the specified dimension.

Syntax

@LEVMBRS(dimName, levName|levNum)

dimName Dimension name specification.
levName|levNum A level name or an integer value that defines the number of a level. The integer value must be 0 or a positive integer.

Notes

Examples

In the Sample Basic database:

@LEVMBRS(Measures,"Profit and Loss")  
@LEVMBRS(Measures,0)

both return the following members if level 0 of the Measures dimension is named Profit and Loss:

Sales, COGS, Marketing, Payroll, Misc, Opening Inventory, Additions, Ending Inventory, Margin %, Profit %, and Profit per Ounce (in that order).

@LEVMBRS(Scenario,0)

returns Actual, Budget, Variance, and Variance %.


The following example restricts the calculation to members with the combination Budget and one of the members of the Market dimension with a level name of "State".

FIX (Budget,@LEVMBRS(Market,State))
   CALC DIM (Year,Measures);
ENDFIX

See Also

@GENMBRS


@LIST

The @LIST() function creates and distinguishes lists that are processed by functions that require list arguments. @LIST can be used to create expLists, member lists, or rangeLists. @LIST treats a collection of parameters as one entity.

Syntax

@LIST(argument1, argument2, ..., argumentN)

argument1, argument2, ..., argumentN The list of arguments that are collected and treated as one argument so they can be processed by the parent function. Arguments can be member names, member combinations, member set functions, range functions, and numeric expressions.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Year dimension contains an additional member, Sales Correl. @LIST is used with the @CORRELATION function to determine the sales relationship between a product's two peak periods (Jan through Mar and Apr through May):

FIX(Sales)
"Sales Correl" = @CORRELATION(SKIPNONE,
  @LIST(Jan,Feb,Mar),@LIST(Apr,May,Jun));
ENDFIX

This example produces the following report:


        Colas     Actual    New York				  
                 Sales      
                 =====      
Jan               678          
Feb               645       
Mar               675        
Apr               712      
May               756      
Jun               890           

Sales Correl  0.200368468

@LN

The @LN() function returns the natural logarithm (base e) of the specified expression.

Syntax

@LN (expression)

expression Single member specification, member combination, or other numeric expression. If less than or equal to 0, Essbase returns #MISSING.

Example

The following example is based on a variation of Sample Basic:

LN_Sales = @LN(Sales);

This example produces the following result:


                                   Cola East
               Jan       Feb       Mar           Nov      Dec
Sales          100       110       120 . . .       0      210
LN_Sales   4.65052   4.70048   4.78749 . . . #MISSING  5.34710

See Also

@LOG10
@LOG
@EXP


@LOG

The @LOG() function returns the result of a logarithm calculation where you can specify both the base to use and the expression to calculate.

Syntax

@LOG (expression [, base])

expression Single member specification, variable name, function, or other numeric expression. If less than or equal to 0, Essbase returns #MISSING.
base Optional. Single member specification, member combination, or numeric expression.
  • If the base value is #MISSING, less than or equal to 0, or close to 1, Essbase returns #MISSING.
  • If the base is omitted, Essbase calculates the base-10 logarithm of the specified expression. @LOG(Sales) is equivalent to @LOG10(Sales).

The @LOG function returns the logarithm of expression calculated using the specified base. @LOG (x,b) is equivalent to logb(x).

Example

The following example is based on a variation of Sample Basic:

LOG2_Sales = @LOG(Sales,2);

This example produces the following result:


                                     Cola East
                Jan        Feb       Mar            Nov      Dec
Sales           100   #MISSING       120 . . .        0      210
LOG2_Sales  6.64386   #MISSING   6.90689 . . .  #MISSING  7.71425


See Also

@LN
@LOG10


@LOG10

The @LOG10() function returns the base-10 logarithm of the specified expression.

Syntax

@LOG10 (expression)

expression Single member specification, variable name, function, or other numeric expression. If less than or equal to 0, Essbase returns #MISSING.

Example

The following example is based on a variation of Sample Basic:

LOG10_Sales = @LOG10(Sales);

This example produces the following result:


                                 Product Actual
                       East      West     South   Central  
Sales                 87398    132931     50846    129680    
LOG10_Sales         4.94150   5.12363   4.70626   5.11287  


See Also

@LOG
@LN


@LSIBLINGS

The @LSIBLINGS() function returns the left siblings of the specified member. Left siblings are children that share the same parent as the member and that precede the member in the database outline. This function excludes the specified member.

This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@LSIBLINGS(mbrName)

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

Notes

Example

In the Sample Basic database:

@LSIBLINGS(Qtr4)       

returns Qtr3, Qtr2, and Qtr1 (in that order). These members appear above Qtr4 in the Sample Basic outline.

@LSIBLINGS(Utah)

returns Washington, Oregon, and California (in that order). These members appear above Utah in the Sample Basic outline.

See Also

@ILSIBLINGS, which includes the specified member.
@RSIBLINGS


@MATCH

The @MATCH function performs wildcard member selections.

Syntax

@MATCH (mbrName|genName|levName, "pattern")

mbrName The default or user-defined name of the member on which to base the search. Essbase searches the member names and alias names of the specified member and its descendants.
genName The default or user-defined name of the generation to search. Essbase searches all member names and member alias names in the generation.
levName The default or user-defined name of the level to search. Essbase searches all member names and member alias names in the level.
"pattern" The character pattern to search for, including a wildcard character (* or ?).

? substitutes one occurrence of any character. You can use ? anywhere in the pattern.

* substitutes any number of characters. You can use * only at the end of the pattern.

To include spaces in the character pattern, enclose the pattern in double quotation marks ("").

Description

This function performs a trailing-wildcard member selection. Essbase searches for member names and alias names that match the pattern you specify and returns the member and alias names it finds.

If the members names in the database you are searching are case-sensitive, the search is case-sensitive. Otherwise, the search is not case-sensitive. To define database member names as case-sensitive, choose Settings | Case Sensitive Members from the Application Manager Outline Editor menu.

You can use more than one @MATCH function in a calculation script.

If Essbase does not find any members that match the chosen character pattern, it returns no member names and continues with the other calculation commands in the calculation script.

Examples

In the Sample Basic database:

@MATCH(Product,"???-10")

returns: 100-10, 200-10, 300-10, and 400-10

@MATCH(Year,"J*")

returns: Jan, Jun, Jul

@MATCH(Product,"C*")

returns: 100 (Colas), 100-10 (Cola), 100-30 (Caffeine Free Cola), 300 (Cream Soda)


@MAX

The @MAX() function returns the maximum value among the results of the expressions in the specified member list.

Syntax

@MAX(expList)

expList Comma-delimited list of members, variable names, functions, and numeric expressions, all of which return numeric values.

Notes

Depending on the values in the list, @MAX may return a zero(0) or #MISSING value. For full control over skipping or inclusion of zero(0) and #MISSING values, it is recommended to use the @MAXS function instead of the @MAX function.

Example

This example is based on the Sample Basic database:

Qtr1 = @MAX(Jan:Mar);

This example produces the following report:


           Colas   New York   Actual		  
          Jan     Feb     Mar     Qtr1
          ===     ===     ===     ====
Sales     678     645     675     678

See Also

@MAXS
@MAXSRANGE
@MINS


@MAXRANGE

The @MAXRANGE() function returns the maximum value of the specified member across the specified range of members.

Syntax

@MAXRANGE (mbrName [ ,rangeList])

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

Notes

Depending on the values in the list, @MAXRANGE may return a zero(0) or #MISSING value. For full control over skipping or inclusion of zero(0) and #MISSING values, it is recommended to use the @MAXSRANGE function instead of the @MAXRANGE function.

Example

In the Sample Basic database:

Qtr1 = @MAXRANGE(Sales,@CHILDREN(Qtr1));

This example produces the following report:


           Colas   New York   Actual	 
          Jan     Feb     Mar     Qtr1
          ===     ===     ===     ====
Sales     678     645     675     678 

See Also

@MAXSRANGE
@MAXS
@MINSRANGE


@MAXS

The @MAXS() function returns the maximum value among the results of the expressions in the specified member list. @MAXS enables skipping of #MISSING and 0 values, in contrast with the @MAX function which cannot ignore these values.

Syntax

@MAXS (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList in the operation, regardless of their content
SKIPMISSING Ignores all #MISSING values
SKIPZERO Ignores all 0 values
SKIPBOTH Ignores all 0 and #MISSING values
expList Comma-delimited list of members, variable names, functions, or numeric expressions, all of which return numeric values

Notes

Example

For both examples, assume a database similar to Sample Basic. The Measures dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp (miscellaneous income and expenses). The data can include 0 and #MISSING values.

Example 1

Qtr1_Max = @MAXS(SKIPBOTH, Jan:Mar);

This example ignores #MISSING and 0 values for all members of the Measures dimension. This example produces the following results:


                   Jan       Feb       Mar  Qtr1_Max
              ========  ========  ========  ========  
COGS          #MISSING      1500      2300      2300
OtherInc_Exp      -500      -350         0      -350 

Example 2

Qtr1_Max = @MAXS(SKIPNONE, Jan:Mar);

This example includes #MISSING and 0 values in the calculation, for all members of the Measures dimension. This example produces the following results:


                   Jan       Feb       Mar  Qtr1_Max
              ========  ========  ========  ========  			  
COGS          #MISSING      1500      2300      2300
OtherInc_Exp      -500      -350         0         0 


See Also

@MAXSRANGE
@MAX
@MINS


@MAXSRANGE

The @MAXSRANGE() function returns the maximum value of the specified member across the specified range of members. @MAXSRANGE enables skipping of #MISSING and 0 values, in contrast with the @MAXRANGE function, which cannot ignore these values.

Syntax

@MAXSRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [ ,rangeList])

SKIPNONE Includes all cells specified in expList in the operation, regardless of their content
SKIPMISSING Ignores all #MISSING values
SKIPZERO Ignores all 0 values
SKIPBOTH Ignores all 0 and #MISSING values
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination
rangeList A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

For both examples, assume a database similar to Sample Basic. The Measures dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp (miscellaneous income and expenses). The data can include 0 and #MISSING values. For both members of the Measures dimension, the result is the same--the maximum value for the OtherInc_Exp member across the specified range.

Example 1

Qtr1_Max = @MAXSRANGE (SKIPBOTH, OtherInc_Exp, @CHILDREN(Qtr1));

This example ignores #MISSING and 0 values and produces the following results:


                   Jan       Feb       Mar  Qtr1_Max
              ========  ========  ========  ========
OtherInc_Exp      -500  #MISSING      -250      -250
COGS                 0      1500      2300      -250 

Example 2

Qtr1_Max = @MAXSRANGE (SKIPNONE, OtherInc_Exp, @CHILDREN(Qtr1));

Using the same data as Example 1, Example 2 demonstrates what happens if you do not skip 0 and #MISSING values in the data. Example 2 produces the following report:


                   Jan       Feb       Mar  Qtr1_Max
              ========  ========  ========  ========   
OtherInc_Exp      -500  #MISSING      -250  #MISSING
COGS                 0      1500      2300  #MISSING 

See Also

@MAXS
@MINSRANGE
@MAXRANGE


@MDALLOCATE

The @MDALLOCATE() function allocates values from a member, from a cross-dimensional member, or from a value across multiple dimensions. The allocation is based on a variety of criteria.

The @MDALLOCATE function allocates values that are input at an upper level to lower-level members in multiple dimensions. The allocation is based upon a specified share or spread of another variable. You can specify a rounding parameter for allocated values and account for rounding errors.

Syntax

@MDALLOCATE (amount, Ndim, allocationRange1 ... allocationRangeN,
basisMbr, [roundMbr], method [, methodParams]
[, round [, numDigits][, roundErr]])

amount A value, member, or cross-dimensional member that contains the value to be allocated into each allocationRange. The value may also be a constant.
  • If amount is a member, the member must be from a dimension to which an allocationRange belongs.
  • If amount is a cross-dimensional member, the member must include a member from every dimension of every allocationRange.
  • If a member or cross-dimensional member is not from an allocationRange dimension, Essbase displays a warning message.
If the amount parameter is a loaded value, it cannot be a Dynamic Calc member.
Ndim The number of dimensions across which values are allocated.
allocationRange1 ... allocationRangeN Comma-delimited lists of members, member set functions, or range functions from the multiple dimensions into which values from amount are allocated.
basisMbr A value, member, or cross-dimensional member that contains the values that are used as the basis for the allocation. The method you specify determines how the basis data is used.
roundMbr Optional. The member or cross-dimensional member to which rounding errors are added. This member (or at least one member of a cross-dimensional member) must be included in an allocationRange.
method The expression that determines how values are allocated. One of the following:
  • share: Uses basisMbr to calculate a percentage share. The percentage share is calculated by dividing the value in basisMbr for the current member in allocationRange by the sum across the allocationRange for that basis member:
    amount * (@CURRMBR()->basisMbr/@SUM(allocationRange->basisMbr))
  • spread: Spreads amount across allocationRange:
    amount * (1/@COUNT(SKIP,allocationRange))
  • SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH: Values to be ignored during calculation of the spread. You must specify a SKIP parameter only for spread.
    • SKIPNONE: Includes all cells.
    • SKIPMISSING: Excludes all #MISSING values in basisMbr, and stores #MISSING for values in allocationRange for which the basisMbr is missing.
    • SKIPZERO: Excludes all zero (0) values in basisMbr, and stores #MISSING for values in allocationRange for which the basisMbr is zero.
    • SKIPBOTH: Excludes all zero (0) values and all #MISSING values, and stores #MISSING for values in allocationRange for which the basisMbr is zero (0) or #MISSING.
  • percent: Takes a percentage value from basisMbr for each member in allocationRange and applies the percentage value to amount:
    amount * (@CURRMBR()->basisMbr * .01).
  • add: Takes the value from basisMbr for each member of allocationRange and adds the value to amount:
    amount + @CURRMBR()->basisMbr
  • subtract: Takes the value from basisMbr for each member of allocationRange and subtracts the value from amount:
    amount - @CURRMBR()->basisMbr
  • multiply: Takes the value from basisMbr for each member of allocationRange and multiplies the value by amount:
    amount * @CURRMBR()->basisMbr
  • divide: Takes the value from basisMbr for each member of allocationRange and divides the value by amount:
    amount/@CURRMBR()->basisMbr
round Optional. One of the following:
  • noRound: No rounding. noRound is the default.
  • roundAmt: Indicates that you want to round the allocated values. If you specify roundAmt, you also must specify numDigits to indicate the number of decimal places to round to.
numDigits An integer that represents the number of decimal places to round to. You must specify numDigits if you specify roundAmt.
  • If numDigits is 0, the allocated values are rounded to the nearest integer. The default value for numDigits is 0.
  • If numDigits is greater than 0, the allocated values are rounded to the specified number of decimal places.
  • If numDigits is a negative value, the allocated values are rounded to a power of 10.
If you specify roundAmt, you also can specify a roundErr parameter.
roundErr Optional. An expression that specifies where rounding errors should be placed. You must specify roundAmt in order to specify roundErr. If you do not specify roundErr, Essbase discards rounding errors.
To specify roundErr, choose from one of the following:
  • errorsToHigh: Adds rounding errors to the member with the highest allocated value. If allocated values are identical, adds rounding errors to the first value in allocationRange.
  • errorsToLow: Adds rounding errors to the member with the lowest allocated value. If allocated values are identical, adds rounding errors to the first value in allocationRange. #MISSING is treated as the lowest value in a list; if multiple values are #MISSING, rounding errors are added to the first #MISSING value in the list.
  • errorsToMbr: Adds rounding errors to the specified roundMbr, which must be included in allocationRange.

Notes

Example

Consider the following example from the Sample Basic database. A data value of 500 is loaded to Budget->Total Expenses->East for Jan and Colas. (For this example, assume that Total Expenses is not a Dynamic Calc member.)

You need to allocate the amount across each expense category for each child of East. The allocation for each child of East is based on the child's share of Total Expenses->Actual:

FIX("Total Expenses")
Budget = @MDALLOCATE(Budget->"Total Expenses"->East,2,
   @CHILDREN(East),@CHILDREN("Total Expenses"),Actual,,share);
ENDFIX 

This example produces the following report:


                                    Jan       Colas		
                        Marketing   Payroll   Misc   Total Expenses
                        =========   =======   ====   ==============
Actual  New York          94          51        0         145
        Massachusetts     23          31        1          55
        Florida           53          54        0         107
        Connecticut       40          31        0          71
        New Hampshire     27          53        2          82
          East           237         220        3         460
Budget  New York         102.174      55.435    0         #MI
        Massachusetts     25          33.696    1.087     #MI
        Florida           57.609      58.696    0         #MI
        Connecticut       43.478      33.696    0         #MI
        New Hampshire     29.348      57.609    2.173     #MI
          East           #MI          #MI       #MI       500

See Also

@ALLOCATE


@MDANCESTVAL

The @MDANCESTVAL() function returns ancestor-level data from multiple dimensions based on the current member being calculated.

Syntax

@MDANCESTVAL(dimCount, dimName1, genLevNum1. . . dimNameX, genLevNumX [,mbrName])

dimCount Integer value that defines the number of dimensions from which ancestor values are being returned.
dimName1, . . . dimNameX Defines the dimension names from which the ancestor values are to be returned. You must specify a genLevNum for every dimName.
genLevNum, . . . genLevNumX Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference. You must specify a dimName for every genLevNum.
mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, from which the ancestor values are to be returned.

Example

Marketing expenses are captured at the Product Family and Region level in a product planning application. The Marketing Expense data must be allocated down to each Product code and State level based on Sales contribution. Data is captured as follows:


                      Sales   Marketing
                      =====   =========				  
New York    100-10     300     N/A
            100-20     200     N/A
               100     500     N/A
Boston      100-10     100     N/A
            100-20     400     N/A
               100     500     N/A
East        100-10     400     N/A
            100-20     600     N/A
               100    1000     200

The Marketing Expense value of 200 at East and Product code 100 is allocated down to each Product code and State with the following formula:

Marketing = (Sales / @MDANCESTVAL(2, Market, 2, Product, 2, Sales)) * @MDANCESTVAL(2, Market, 2, Product, 2, Marketing);

which produces the following result:


                      Sales   Marketing
                      =====   =========				  
New York    100-10     300     60
            100-20     200     40
               100     500     N/A
Boston      100-10     100     20
            100-20     400     80
               100     500     N/A
East        100-10     400     N/A
            100-20     600     N/A
               100    1000     N/A

The Marketing expenses can then be reconsolidated across Products and Markets.

See Also

@ANCESTVAL
@SANCESTVAL
@MDPARENTVAL


@MDPARENTVAL

The @MDPARENTVAL() function returns parent-level data from multiple dimensions based on the current member being calculated.

Syntax

@MDPARENTVAL(numDim, dimName1, . . . dimNameX [,mbrName])

numDim Integer value that defines the number of dimensions from which parent values are being returned.
dimName1, . . . dimNameX Defines the dimension names from which the parent values are to be returned.
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination, from which the parent values are to be returned.

Example

Marketing expenses are captured at the Product Family and Region level in a product planning application. The Marketing Expense data must be allocated down to each Product code and State level based on Sales contribution.

Data is captured as follows:


                      Sales   Marketing
                      =====   =========					  
New York   100-10      300      N/A
           100-20      200      N/A
              100      500      N/A
Boston     100-10      100      N/A
           100-20      400      N/A
           100         500      N/A
East       100-10      400      N/A
           100-20      600      N/A
              100     1000      200

The Marketing Expense value of 200 at East and Product code 100 is allocated down to each Product code and State with the following formula:

Marketing = (Sales / @MDPARENTVAL(2, Market, Product, Sales)) * @MDPARENTVAL(2, Market, Product, Marketing);

which produces the following result:


                   Sales   Marketing
                   =====   =========					  
New York   100-10     300       60
           100-20     200       40
              100     500       N/A
Boston     100-10     100       20
           100-20     400       80
           100        500       N/A
East       100-10     400       N/A
           100-20     600       N/A
              100    1000       N/A

The Marketing expenses can then be reconsolidated across Products and Markets.

See Also

@PARENTVAL
@SPARENTVAL
@MDANCESTVAL


@MDSHIFT

The @MDSHIFT() function shifts a series of data values across multiple dimension ranges.

Syntax

@MDSHIFT (mbrName, shiftCnt1, dimName1, [range1|(range1)], . . . shiftCntX, dimNameX, [rangeX|(rangeX)])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination, from which the values are to be shifted.
shiftCnt1...shiftCntX Integer that defines the number of member positions to shift.
dimName1, . . . dimNameX Defines the dimension names in which the shift is to occur.
range1|(range1) . . . rangeX|(rangeX) Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension specified with the dimName parameter. If the range list is comma delimited, then the list must be enclosed in parentheses.

Example

The Budget figures for Ending Inventory need to be calculated by taking Prior Year->Opening Inventory results as a starting point:

                               
                                  Jan   Feb   Mar
Prior Year   Opening Inventory    110   120   130  . .
Budget       Ending Inventory     N/A   N/A   N/A . .

The following calculation script assumes that the Scenario dimension is as follows:


Scenario
       Prior Year
       Budget

FIX (Budget)
"Ending Inventory" = @MDSHIFT("Opening Inventory", 1, Year, , -1, Scenario,);
ENDFIX

In this example, range1 is not specified, so Essbase defaults to the level 0 members of the Year dimension, which was specified as the dimName1 parameter. Since range2 is also not specified, Essbase defaults to the level 0 members of the Scenario dimension, which was specified as the dimName2 parameter. This example produces the following result:


                                 Jan     Feb     Mar
                                 ===     ===     ===				   
Prior Year  Opening Inventory    110      120     130 . .
Budget      Ending Inventory     120      130     140 . .

See Also

@SHIFT


@MEDIAN

The @MEDIAN() function returns the median (the middle number) of the specified data set (expList). Half the numbers in the data set are larger than the median, and half are smaller.

Syntax

@MEDIAN (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the median.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the median.
SKIPZERO Excludes all zero (0) values from expList during calculation of the median.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the median.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the median is calculated.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Median. This example calculates the median sales values for all products and uses the @RANGE function to generate expList:

FIX (Product)
Median = @MEDIAN(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:


                         Jan     New York
                      Actual       Budget
                      ======       ======
Sales    Colas         678          640
         Root Beer     551          530
         Cream Soda    663          510
         Fruit Soda    587          620
         Diet Drinks   #MI          #MI
          Product     2479         2300

Median   Product       625          575

Because SKIPBOTH is specified in the calculation script, the #MI values for Diet Drinks are skipped. The remaining four products create an even-numbered data set. So, to calculate Median->Product->Actual, the two middle numbers in the set (587 and 663) are averaged to create the median (625). To calculate Median->Product->Budget, the two middle numbers in the set (530 and 620) are averaged to create the median (575).

See Also

@RANGE


@MEMBER

The @MEMBER function returns the member with the name that is provided as a character string.

Syntax

@MEMBER (String)

String A string (enclosed in double quotation marks) or a function that returns a string

Example

Typically, the @MEMBER function is used in combination with string functions that are used to manipulate character strings to form the name of a member. In the following example, the member name QTR1 is appended to the character string 2000_ to form the string 2000_QTR1. The @MEMBER function returns the member 2000_QTR1 and QTD is set to the value of this member.

QTD=@MEMBER(@CONCATENATE("2000_", QTR1));

See Also

@CONCATENATE
@SUBSTRING


@MERGE

The @MERGE() function merges two member lists that are processed by another function. Duplicate values from both lists are included only once in the merged list.

Syntax

@MERGE(list1, list2)

list1 The first list of member specifications to be merged.
list2 The second list of member specifications to be merged.

Notes

Examples

In the Sample Basic database,

@MERGE(@CHILDREN(Colas),@CHILDREN("Diet Drinks"));

returns Cola, Diet Cola, Caffeine Free Cola, Diet Root Beer, and Diet Cream Soda.

Diet Cola appears only once in the merged list, even though it is a child of both Colas and Diet Drinks.


In this example, the @MERGE function is used with the @ISMBR function to increase the marketing budget for major markets and for western markets.


Budget
(IF (@ISMBR(@MERGE(@UDA(Market,"Major Market"),
       @DESCENDANTS(West))))
Marketing = Marketing * 1.1;
ENDIF;);

This example produces the following report, which shows only the major markets in the East and all western markets:


                  Product     Year     Budget
                            Marketing		
                            =========
New York                      6039		
Massachusetts                 1276		
Florida                       2530		

California                    7260		
Oregon                        2090		
Washington                    2772		
Utah                          1837		
Nevada                        4521		

The values prior to running the calculation script were:

New York        5490
Massachusetts   1160
Florida         2300

California      6600
Oregon          1900
Washington      2520
Utah            1670
Nevada          4110

See Also

@LIST
@RANGE
@REMOVE


@MIN

The @MIN() function returns the minimum value among the results of the expressions in expList.

Syntax

@MIN (expList)

expList Comma-delimited list of members, variable names, functions, and numeric expressions, all of which return numeric values.

Notes

Depending on the values in the list, @MIN may return a zero(0) or #MISSING value. For full control over skipping or inclusion of zero(0) and #MISSING values, it is recommended to use the @MINS function instead of the @MIN function.

Example

In the Sample Basic database:

Qtr1 = @MIN(Jan:Mar);

This example produces the following report:


            Colas   New York   Actual			   
          Jan     Feb     Mar     Qtr1
          ===     ===     ===     ====
Sales     678     645     675     645 

See Also

@MINS
@MINRANGE
@MAX


@MINRANGE

The @MINRANGE() function returns the minimum value of mbrName across rangeList.

Syntax

@MINRANGE (mbrName [ ,rangeList])

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

Notes

Depending on the values in the list, @MINRANGE may return a zero(0) or #MISSING value. For full control over skipping or inclusion of zero(0) and #MISSING values, it is recommended to use the @MINSRANGE function instead of the @MINRANGE function.

Example

In the Sample Basic database:

Qtr1 = @MINRANGE(Sales,Jan:Mar);

This example produces the following report:


            Colas   New York   Actual			 
          Jan     Feb     Mar     Qtr1
		  ===     ===     ===     ====
Sales     678     645     675     645 

See Also

@MINSRANGE
@MIN
@MAXSRANGE


@MINS

The @MINS() function returns the minimum value across the results of the expressions in expList, allowing skipping of #MISSING and 0 values. @MINS enables skipping of #MISSING and 0 values, in contrast with the @MIN function, which always includes these values.

Syntax

@MINS (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes in the operation all cells specified in expList regardless of their content
SKIPMISSING Ignores all #MISSING values
SKIPZERO Ignores all 0 values
SKIPBOTH Ignores all 0 and #MISSING values
expList Comma-delimited list of member names, variable names, functions, or numeric expressions. expList provides a list of numeric values for which Essbase determines the minimum value.

Notes

Example

For both examples, assume a database similar to Sample Basic. The Measures dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp (miscellaneous income and expenses). The data can include 0 and #MISSING values.

Example 1

Qtr1_Min = @MINS(SKIPBOTH, Jan:Mar);

This example ignores #MISSING and 0 values for all members of the Measures dimension. This example produces the following results:


                   Jan       Feb       Mar  Qtr1_Min
              ========  ========  ========  ========  
COGS          #MISSING      1500      2300      1500
OtherInc_Exp      -500      -350         0      -500 

Example 2

Qtr1_Min = @MINS(SKIPNONE, Jan:Mar);

For all members of the Measures dimension, this example includes #MISSING and 0 values and produces the following results:


                   Jan       Feb       Mar  Qtr1_Min
              ========  ========  ========  ========  
COGS          #MISSING      1500      2300  #MISSING
OtherInc_Exp      -500      -350         0      -500 

See Also

@MINSRANGE
@MAXS
@MIN


@MINSRANGE

The @MINSRANGE() function returns the minimum value of mbrName across rangeList. @MINSRANGE enables skipping of #MISSING and 0 values, in contrast with the @MINRANGE function, which always includes these values in the calculation.

Syntax

@MINSRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [,rangeList])

SKIPNONE Includes in the operation all specified cells regardless of their content
SKIPMISSING Ignores all #MISSING values
SKIPZERO Ignores all 0 values
SKIPBOTH Ignores all 0 and #MISSING values
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

For both examples, assume a database similar to Sample Basic. The Measures dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp (miscellaneous income and expenses). The data can include 0 and #MISSING values. For both members of the Measures dimension, the result is the same--the minimum value for the OtherInc_Exp member across the specified range.

Example 1

Qtr1_Min = @MINSRANGE(SKIPBOTH, OtherInc_Exp, Jan:Mar);

This example ignores the 0 value for Mar and produces the following results:


                   Jan       Feb       Mar  Qtr1_Min
              ========  ========  ========  ========
COGS          #MISSING      1500      2300       350
OtherInc_Exp       500       350         0       350 

Example 2

Qtr1_Min = @MINS(SKIPNONE, OtherInc_Exp, Jan:Mar);

This example does not ignore the 0 value in the calculation. This example produces the following results:


                   Jan       Feb       Mar  Qtr1_Min
              ========  ========  ========  ======== 
COGS          #MISSING      1500      2300         0 
OtherInc_Exp       500       350         0         0 

See Also

@MINS
@MINRANGE
@MAXSRANGE


@MOD

The @MOD() function calculates the modulus of a division operation.

Syntax

@MOD (mbrName1, mbrName2)

mbrName1 and mbrName2 Members from the same dimension whose modulus is to be calculated.

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Factor. The modulus between Profit % and Margin % is calculated with the following formula:

Factor = @MOD("Margin %", "Profit %");

This example produces the following report:


              Market      Product      Scenario
             Margin %     Profit %      Factor
             ========     ========      ======
Jan           55.10        25.44         4.22
Feb           55.39        26.03         3.34
Mar           55.27        25.87         3.53

@MODE

The @MODE() function returns the mode (the most frequently occurring value) in the specified data set (expList).

Syntax

@MODE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the mode.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the mode.
SKIPZERO Excludes all zero (0) values from expList during calculation of the mode.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the mode.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the mode is calculated.

Notes

Example

The following example calculates the mode of the units sold for the Central region and uses the @RANGE function to generate expList:

FIX (Central)
"Mode" = @MODE(SKIPMISSING,
   @RANGE(Sales,@CHILDREN(Central)));
ENDFIX

This example produces the following report:


                       Colas   Actual    Jan
                             Units Sold    
                             ==========   
Units Sold   Illinois            3   
             Ohio                2   
             Wisconsin           3   
             Missouri          #MI   
             Iowa                0  
             Colorado            6  
               Central          14                 

Mode         Central             3 

See Also

@RANGE


@MOVAVG

The @MOVAVG() function applies a moving n-term average (mean) to an input data set. Each term in the set is replaced by a trailing mean of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVAVG modifies a data set for smoothing purposes.

Syntax

@MOVAVG (mbrName [, n [, rangeList]])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional. A positive integer value that represents the number of values to average. The default is 3.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Avg.

"Mov Avg" = @MOVAVG(Sales,3,Jan:Jun);

In this example, the @MOVAVG function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVAVG can be used with the @TREND function to forecast average sales data for a holiday season (for example, October - December).

This example produces the following report:


          Colas   New York   Actual		 
              Sales     Mov Avg	
              =====     =======	  
Jan            678        678	
Feb            645        645	
Mar            675        666	
Apr            712        677.3	
May            756        714.3	
Jun            890        786	

In this example, Essbase averages three values at a time for the moving average. The first two values (Jan,Feb) for Mov Avg and the first two values for Sales are the same. The value for Mar represents the trailing average of Jan, Feb, and Mar. The value for Apr represents the trailing average of Feb, Mar, and Apr. The remaining values represent the trailing average for each group of three values.

See Also

@MOVMAX
@MOVMED
@MOVMIN
@TREND


@MOVMAX

The @MOVMAX() function applies a moving n-term maximum (highest number) to an input data set. Each term in the set is replaced by a trailing maximum of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVMAX modifies a data set for smoothing purposes.

Syntax

@MOVMAX (mbrName [, n [, rangeList]])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional. A positive integer value that represents the number of values that are used to calculate the moving maximum. The default is 3.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Max.

"Mov Max" = @MOVMAX(Sales,3,Jan:Jun);

In this example, the @MOVMAX function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVMAX can be used with the @TREND function to forecast maximum sales data for a holiday season (for example, October - December).

This example produces the following report:


            Root Beer    New York    Actual			
                 Sales        Mov Max	
                 =====        =======
Jan               551           551	
Feb               641           641	
Mar               586           641	
Apr               630           641	
May               612           630	
Jun               747           747

In this example, Essbase uses three values at a time to calculate the moving maximum. The first two values (Jan,Feb) for Mov Max and the first two values for Sales are the same. The value for Mar represents the trailing maximum of Jan, Feb, and Mar. The value for Apr represents the trailing maximum of Feb, Mar, and Apr. The remaining values represent the trailing maximum for each group of three values.

See Also

@MOVAVG
@MOVMED
@MOVMIN
@TREND


@MOVMED

The @MOVMED() function applies a moving n-term median (middle number) to an input data set. Each term in the list is replaced by a trailing median of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVMED modifies a data set for smoothing purposes.

Syntax

@MOVMED (mbrName [, n [, rangeList]])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional. A positive integer value that represents the number of values that are used to calculate the moving median. The default is 3.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Med.

"Mov Med" = @MOVMED(Sales,3,Jan:Jun);

In this example, the @MOVMED function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVMED could be used with the @TREND function to forecast sales data for a holiday season (for example, October - December).

This example produces the following report:


            Colas      New York     Actual
                Sales        Mov Med
                =====        =======
Jan              678           678	
Feb              645           645	
Mar              675           675	
Apr              712           675	
May              756           712	
Jun              890           756	

In this example, Essbase uses three values at a time to calculate the moving median. The first two values (Jan,Feb) for Mov Med are the same as the first two values for Sales. The value for Mar represents the trailing median of Jan, Feb, and Mar. The value for Apr represents the trailing median of Feb, Mar, and Apr. The remaining values represent the trailing median of each group of three values.

See Also

@MOVAVG
@MOVMAX
@MOVMIN
@TREND


@MOVMIN

The @MOVMIN() function applies a moving n-term minimum (lowest number) to an input data set. Each term in the list is replaced by a trailing minimum of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVMIN modifies a data set for smoothing purposes.

Syntax

@MOVMIN (mbrName [, n [, rangeList]])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional. A positive integer value that represents the number of values that are used to calculate the moving minimum. The default is 3.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Min.

"Mov Min" = @MOVMIN(Sales,3,Jan:Jun);

In this example, the @MOVMIN function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVMIN can be used with the @TREND function to forecast minimum sales data for the holiday season (for example, October - December).

This example produces the following report:


            Colas     New York    Actual
                Sales       Mov Min	
                =====       =======
Jan              678          678	
Feb              645          645	
Mar              675          645	
Apr              712          645	
May              756          675	
Jun              890          712	

In this example, Essbase uses three values at a time to calculate the moving minimum. The first two values (Jan,Feb) for Mov Min and the first two values for Sales are the same. The value for Mar represents the trailing minimum of Jan, Feb, and Mar. The value for Apr represents the trailing mimimum of Feb, Mar, and Apr. The remaining values represent the trailing minimum for each group of three values.

See Also

@MOVAVG
@MOVMAX
@MOVMED
@TREND


@NAME

The @NAME() function passes the enclosed string or member name as a string to another function.

Syntax

@NAME (mbrName)

mbrName Any valid single member name, dimension name, or a string.

Notes

Essbase does not support strings in functions. It treats strings as values or array of values. The @NAME function processes strings.

Example

The following example is based on the Sample Basic database. A user defined function is used to retrieving the price from the table below. The user defined function (J_GetPrice) takes two string parameters time and product name to return the price for each product.

MonthName ProductId Price
Jan 100-10 1.90
Feb 100-10 1.95
Mar 100-10 1.98
Jan 100-20 1.95
Feb 100-20 2.00
Mar 100-20 2.05

Price = @J_GetPrice(@NAME(@CURRMBR(Product)),@NAME(@CURRMBR(Year)));

The following report illustrates the above example:


               Price   Actual  Market
                Jan     Feb     Mar
                ===     ===     ===
      100-10   1.90    1.95    1.98
      100-20   1.95    2.00    2.05

See Also

@CURRMBR


@NEXT

The @NEXT() function returns the nth cell value in the sequence rangeList from mbrName, retaining all other members identical to the current member. @NEXT cannot operate outside the given range.

Syntax

@NEXT (mbrName [, n, rangeList])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. If you do not specify n, then the default is set to 1, which provides the next member in the range. Using a negative value for n has the same effect as using the matching positive value in the @PRIOR function.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Example

In this example, Next Cash for each month is derived by taking the Cash value for the following month. Since n is not specified, the default is 1, which provides the next member in the range. Since rangeList is not specified, the level 0 members from the dimension tagged as Time are used (Jan,Feb,Mar, ...).

"Next Cash" = @NEXT(Cash);

This example produces the following report:


            Jan   Feb   Mar   Apr   May   Jun
            ===   ===   ===   ===   ===   ===			
Cash        100    90   120   110   150   100
Next Cash    90   120   110   150   100   #MI

See Also

@PRIOR
@SHIFT
@SHIFTMINUS
@SHIFTPLUS


@NEXTS

The @NEXTS() function returns the nth cell value in the sequence rangeList from the mbrName. @NEXTS provides the option to skip #MISSING, zero, or both #MISSING and zero values. @NEXTS works within a designated range and retains all other members identical to the current member.

Syntax

@NEXTS(SKIPNONE | SKIPMISSING | SKIPZERO | | SKIPBOTH mbrName[,n,rangeList])

SKIPNONE Includes all cells specified in the rangeList operation, regardless of their content.
SKIPMISSING Ignores all #MISSING values in the rangeList operation.
SKIPZERO Ignores all 0 in the rangeList operation.
SKIPBOTH Ignores all #MISSING and 0 values in the rangeList operation.
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. Using a negative value for n has the same effect as using the matching positive value in @PRIORS. If you do not specify n, then a default value of 1 is assumed, which returns the next prior member from the lowest level of the dimension set as Time in the database outline.
rangeList Optional. A valid member, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension set as Time.

Example

In this example, Next Cash for each month is derived by taking the Cash value for the following month and ignoring both #MISSING and zero values. Because n is not specified, the default is 1, which provides the next member in the range. Also, because rangeList is not specified, the level 0 members from the dimension set as Time are used (Jan,Feb,Mar, ...).

"Next Cash" = @NEXTS(SKIPBOTH, Cash);

The following report illustrates the above example:


             Jan     Feb    Mar    Apr    May    Jun
             ===     ===    ===    ===    ===    === 
Cash         1100    #MI    1000   1300     0    1400 
Next Cash    1000    1000   1300   1400   1400    #MI   

See Also

NEXT


@NPV

The @NPV() function calculates the Net Present Value of an investment based on the series of payments (negative values) and income (positive values).

Syntax

@NPV (cashflowMbr, rateMbrConst, discountFlag [, rangeList])

cashflowMbr Member specification providing a series of numeric values.
rateMbrConst Single member specification, variable name, or numeric expression, providing a constant value.
discountFlag Single member specification, variable name, or numeric expression set to 0 or 1 to indicate whether the function should discount from the first period. 1 means do not discount from the first period.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

In this example, Value is calculated with the following formula:

Value = @NPV(Cash, Rate, 0, FY1990:FY1994, FY1995:FY2000);

This example produces the following report:


       FY1990  FY1991  FY1992  FY1993  FY1994  FY1995
       ======  ======  ======  ======  ======  ======
Cash   (1,000)   500     600     500  #MISSING  #MISSING
Rate       0      0       0       0   #MISSING  #MISSING
Value     296    296     296     296      296     296

See Also

@PTD


@PARENT

The @PARENT () function returns the parent of the current member being calculated in the specified dimension. If you specify the optional mbrName, that parent is combined with the specified member.

This member set function can be used as a parameter of another function, where that parameter is a member or list of members.

Syntax

@PARENT(dimName [, mbrName])

dimName Single dimension name specification.
mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, that is combined with the parent returned.

Notes

Example

In the Sample Basic database:

@PARENT(Market,Sales)

returns Central->Sales, if the current member of Market being calculated is Colorado.

@PARENT(Measures)

returns Profit, if the current member of Measures being calculated is Margin.

See Also

@ANCEST
@CHILDREN
@ANCESTORS
@DESCENDANTS
@SIBLINGS


@PARENTVAL

The @PARENTVAL() function returns the parent values of the member being calculated in the specified dimension.

Syntax

@PARENTVAL (dimName [, mbrName])

dimName Single dimension name specification that defines the focus dimension of parent values.
mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination.

Example

This example is based on the Sample Basic database. The formula calculates Market Share for each state by taking each state's Sales value as a percentage of Sales for East (its parent) as a whole. Market Share->East is calculated as East's percentage of its parent, Market.

"Market Share" = Sales % @PARENTVAL(Market,Sales);

This example produces the following report:


                  Cola     Actual    Jan	
                  Sales     Market Share
                  =====     ============
New York           678         37.42
Massachusetts      494         27.26
Florida            210         11.59
Connecticut        310         17.11
New Hampshire      120          6.62
  East            1812         37.29

Market            4860        100

See Also

@MDPARENTVAL
@SPARENTVAL
@ANCESTVAL


@POWER

The @POWER() function returns the value of the specified member or expression raised to power.

Syntax

@POWER (expression,power)

expression Single member specification, variable name, function, or other numeric expression.
power Single member specification, variable name, function, or other numeric expression.

Notes

Example

@POWER(14,3)     2744
@POWER (2,8)      256

See Also

@FACTORIAL


@PRIOR

The @PRIOR() function returns the nth previous cell member from mbrName in rangeList. All other dimensions assume the same members as the current member. @PRIOR works only within the designated range.

Syntax

@PRIOR (mbrName [, n, rangeList])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. Using a negative value for n has the same effect as using the matching positive value in the @NEXT function. If you do not specify n, then a default value of 1 is assumed, which returns the next prior member from the lowest level of the dimension tagged as Time in the database outline.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Example

In this example, Prev Inventory for each month is derived by taking the Inventory value from the previous month. Since n is not specified, the default is 1, which provides the next prior member in the range. Since rangeList is not specified, the level 0 members from the dimension tagged as Time are used (Jan,Feb,Mar,...).

"Prev Inventory" = @PRIOR(Inventory);

This example produces the following report:


                Jan     Feb     Mar     Apr    May    Jun
                ===     ===     ===     ===    ===    ===			
Inventory      1100    1200    1000    1300   1300   1400
Prev Inventory  #MI    1100    1200    1000   1300   1300

See Also

@NEXT
@SHIFT
@SHIFTMINUS
@SHIFTPLUS


@PRIORS

The @PRIORS() function returns the nth previous cell member from mbrName in the rangeList. @PRIORS provides options to skip #MISSING, zero, or both #MISSING and zero values. All other dimensions assume the same members as the current member. @PRIORS works within the designated range.

Syntax

@PRIORS(SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH mbrName[,n, rangeList])

SKIPNONE Includes all cells specified in the rangeList operation regardless of their content.
SKIPMISSING Ignores all #MISSING values in the rangeList operation.
SKIPZERO Ignores all zero values in the rangeList operation.
SKIPBOTH Ignores all #MISSING and zero values in the rangeList .
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. Using a negative value for n has the same effect as using the matching positive value in the @NEXTS function. If you do not specify n, then a default value of 1 is assumed, which returns the next prior member from the lowest level of the dimension set as Time in the database outline.
rangeList Optional. A valid member, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension set as Time.

Example

In this example, Prev Inventory for each month is derived by taking the Inventory value from the previous month and ignoring #MISSING and zero values. Because n is not specified, the default is 1, which provides the next prior member in the range. Also, because rangeList is not specified, the level 0 members from the dimension are set as Time used as (Jan,Feb,Mar,...).

"Prev Inventory" = @PRIORS(SKIPBOTH,Inventory);

The following report illustrates this example:


                  Jan      Feb      Mar     Apr     May    Jun
                  ===      ===      ===     ===     ===    === 
 Inventory       1100     #MI     1000     1300      0    1400
 Prev Inventory   #MI    1100     1100     1000   1300    1300     	  

See Also

PRIOR


@PTD

The @PTD() function calculates the period-to-date values of members in the dimension tagged as Time. By default, data is summed unless Accounts are tagged as "First" or "Last".

Syntax

@PTD (timePeriodList)

timePeriodList Range of members from the dimension tagged as Time.

Notes

Example

In this example, assume that the Year dimension in the Sample Basic database outline contains two additional members, YTD and QTD. Using a calculation script, the YTD and QTD members are calculated as follows:

YTD = @PTD(Jan:May);
QTD = @PTD(Apr:May);

In this example Opening Inventory is tagged with a time balance of First, and Ending Inventory is tagged with a time balance of Last.

This example produces the following report:


                            Product   Market   Scenario 							
                   Sales    Opening Inventory     Ending Inventory
                   =====    =================     ================
Jan                31538         117405               116434
Feb                32069         116434               115558
Mar                32213         115558               119143
  Qtr1             95820         117405               119143
Apr                32917         119143               125883
May                33674         125883               136145
Jun                35088         136145               143458
  Qtr2            101679         119143               143458
QTD                66591         245026               262028
YTD               162411         362431               381171

See Also

@NPV


@RANGE

The @RANGE() function 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.

Syntax

@RANGE(mbrName [, rangeList])

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

Examples

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

Notes

Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX/ENDFIX statement.

See Also

@LIST
@MERGE
@REMOVE


@RANK

The @RANK() function returns the rank of the specified members or the specified value among the values in the specified data set. The rank of a value is equivalent to its position (its rank) in the sorted data set.

Syntax

@RANK (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, value, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the rank.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the rank.
SKIPZERO Excludes all zero (0) values from expList during calculation of the rank.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the rank.
value (1) The member or member combination for which the rank is calculated, or (2) a constant value for which the rank is calculated.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the rank is calculated.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Rank. Essbase ranks the sales values for a set of products:

"Sales Rank" = @RANK(SKIPBOTH,Sales,
    @RANGE(Sales,@LEVMBRS(Product,1)));

This example produces the following report. Since SKIPBOTH is specified in the formula, the #MI value for Sales->Diet Drinks is not included in the ranked list:


                 New York   Actual    Jan
                   Sales       Sales Rank
                   =====       ==========
Colas               678            1
Root Beer           551            4
Cream Soda          663            2
Fruit Soda          587            3
Diet Drinks         #MI          #MI

@RDESCENDANTS

The @RDESCENDANTS function returns all descendants of the specified member, or those down to the specified generation or level. This function excludes the specified member. If one or more of the descendants are shared, the result also includes either all descendants of each member being shared or descendants down to the specified generation or level.

You can use this member set function as a parameter of another function, where that parameter is a list of members. In the absence of shared members, @RDESCENDANTS and @DESCENDANTS return the same result.

Syntax

@RDESCENDANTS (mbrName [, genLevNum| genLevName])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination
genLevNum Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Optional. Level name or generation name down to which to select the members.

Notes

Example

Example 1

Assume a variation of the Sample Basic database such that the Product dimension includes the following members:

Product
   100
     100-10
     100-20
     100-30
   200
     200-10
     200-20
     200-30
     200-40
   Diet
     100 (Shared Member)
     200 (Shared Member)

Diet has two children "100" and "200". The members "100" and "200" are shared members.

@RDESCENDANTS(Diet)

returns the members: 100, 100-10, 100-20, 100-30, 200, 200-10, 200-20, 200-30, 200-40 (in that order).

Example 2

@RDESCENDANTS(Profit)

returns Margin, Sales, COGS, Total Expenses, Marketing, Payroll, and Misc (in that order) and is identical to @DESCENDANTS(Profit).

See Also

@DESCENDANTS
@IRDESCENDANTS
@IDESCENDANTS
@ISDESC
@ANCESTORS
@CHILDREN
@SIBLINGS


@RELATIVE

The @RELATIVE function returns all members at the specified generation or level that are above or below the specified member in the database outline.

Syntax

@RELATIVE (mbrName, genLevNum | genLevName)

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
genLevNum An integer value that defines the number of a generation or level. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
genLevName Generation or level name specification.

Notes

Examples

In the Sample Basic database:

@RELATIVE(Qtr1,3)
@RELATIVE(Qtr1,0)

both return the three members that are at generation 3 (or level 0) and that are below Qtr1 in the Sample Basic outline: Jan, Feb, and Mar (in that order).

@RELATIVE(Profit,-1)

returns the two members that are at level 1 and that are below Profit: Margin and Total Expenses (in that order).


@REMAINDER

The @REMAINDER() function returns the remainder value of expression.

Syntax

@REMAINDER (expression)

expression Single member specification, variable name, or other numeric expression.

Example

Margin = @REMAINDER("Margin %");

This example produces the following report:


                          Product   Market 
                  Margin %                  Margin         
            Jan     Feb     Mar      Jan      Feb      Mar
            ===     ===     ===      ===      ===      === 
Scenario  55.10   55.39   55.27     0.10     0.39     0.27 

See Also

@TRUNCATE


@REMOVE

The @REMOVE() function removes values or members in one list from another list.

Syntax

@REMOVE (list1, list2)

list1 A list of member specifications, from which the members specified in list2 are removed.
list2 A list of member specifications to be removed from list1.

Examples

In the Sample Basic database,

@REMOVE(@CHILDREN(East),@LIST("New York",Connecticut))

returns Massachusetts, Florida, New Hampshire.


The following example is based on the Sample Basic database. Assume that the Market dimension contains an additional member, Non-West.

A special analysis requires a sum of the actual sales values of a particular product family for non-western states. In this example, the @REMOVE function is used with the @SUMRANGE function to perform this analysis. The @LIST function is used to group the last two arguments of the @REMOVE function (the children of West plus two additional members, Texas and New Mexico).

FIX(Sales)
"Non-West"=@SUMRANGE(Sales,@REMOVE(@LEVMBRS(Market,0),
          @LIST(@CHILDREN(West),Texas,"New Mexico")));
ENDFIX

This example produces the following report:


                    Jan      Colas     Actual					
                             Sales
                             =====
Non-West                     5114		
	
New York                      678			
Massachusetts                 494			
Florida                       410			
Connecticut                   310			
New Hampshire                 213			
  East                       2105
  	 		
California                    941			
Oregon                        450			
Washington                    320			
Utah                          490			
Nevada                        138			
  West                       2339	
  		
Texas                         642			
Oklahoma                      180			
Louisiana                     166			
New Mexico                    219	
  South                      1207	
  		
Illinois                      579			
Ohio                          430			
Wisconsin                     490			
Missouri                      360			
Iowa                          161			
Colorado                      643			
  Central                    2663			

See Also

@LIST
@MERGE
@RANGE


@ROUND

The @ROUND() function rounds expression to numDigits.

Syntax

@ROUND (expression,numDigits)

expression Single member specification, variable name, or other numeric expression.
numDigits Single member specification, variable name, or other numeric expression that provides an integer value. If numDigits is 0, the number is rounded to the nearest integer. If numDigits is greater than 0, expression is rounded to the specified number of decimal places. If numDigits is a negative value, expression is rounded to a power of 10. The default value for numDigits is 0.

Example

The following example is based on the Sample Basic database:

SET UPDATECALC OFF;
Profit = @ROUND("Profit_%", 1);

This example produces the following report:


                          Market Product 
                   Profit_%                 Profit         
            Jan     Feb     Mar      Jan      Feb      Mar
            ===     ===     ===      ===      ===      === 
Scenario   21.37   19.09   18.46    21.4      19.1    18.5 

See Also

@ABS
@INT
@TRUNCATE
@REMAINDER


The @RSIBLINGS() function returns all of the right siblings of the specified member. Right siblings are children that share the same parent as the member and that follow the member in the database outline. This function excludes the specified member.

This member set function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@RSIBLINGS(mbrName)

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

Notes

Examples

In the Sample Basic database:

@RSIBLINGS(Florida)

returns Connecticut and New Hampshire (in that order). These members appear below Florida in the Sample Basic outline.

@RSIBLINGS(Sales)

returns COGS because this member appears below Sales in the Sample Basic outline.

See Also

@IRSIBLINGS, which includes the specified member.
@LSIBLINGS


@SANCESTVAL

The @SANCESTVAL() function returns ancestor-level data based on the shared ancestor value of the current member being calculated.

Syntax

@SANCESTVAL (rootMbr,genLevNum [, mbrName])

rootMbr Defines a member that is used to search for the nearest occurrence of an ancestor of a shared member.
genLevNum Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference.
mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, for which the ancestor values are to be returned.

Notes

Example

Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as ancestors that contain shared members as children. The Marketing Expense data must be allocated down to each Product code based on Sales contribution.

The following Product hierarchy is defined:


Product
   100
        100-10
        100-20
   200
        200-10
        200-20
   Diet ~
        100-10 SHARED
        200-10 SHARED
   Caffeine Free ~
        100-20 SHARED
        200-20 SHARED


                 Sales   Marketing
                 =====   =========		 
100-10            300       0
100-20            200       0
100               500       0
200-10            100       0
200-30            400       0
200               900       0
100-10            300       0
200-10            100       0
Diet              400      50
100-20            200       0
200-30            400       0 
Caffeine Free     600      40

The Marketing Expense value is allocated down to each Product code with the following formula:

Marketing = (Sales / @SANCESTVAL(Product, 2, Sales)) * @SANCESTVAL(Product, 2, Marketing);

which produces the following result:


                 Sales    Marketing
                 =====    =========
100-10           300       37.5
100-20           200       13.3
100              500       #MI
200-10           100       12.5
200-30           400       26.7
200              900       #MI
100-10           300       37.5
200-10           100       12.5
Diet             400       50
100-20           200       13.3
200-30           400       26.7
Caffeine Free    600       40

The Marketing expenses can then be reconsolidated across Products and Markets.

See Also

@ANCESTVAL
@MDANCESTVAL
@SPARENTVAL


@SHIFT

The @SHIFT() function returns the nth cell value in the sequence rangeList from mbrName, retaining all other members identical to the current member.

This function gets either the prior or next (nth past or future value in rangeList) value of mbrName, based on n. The direction of shift is wholly based on n, with positive n values producing an effect equivalent to @NEXT and negative values of n producing an equivalent effect to @PRIOR.

Syntax

@SHIFT (mbrName [,n, rangeList])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. Using a negative value for n has the same effect as using a positive value in the @PRIOR function. n must be a numeric value, not a reference, such as a member name.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

In this example, Prev Asset for each month is derived by taking the Asset value from the previous month because -1 is specified as the n parameter. Next Avl Asset for each month is derived by taking the Asset value from two months following the current month because 2 is specified as the n parameter. Since rangeList is not specified for either formula, the level 0 members from the dimension tagged as Time are used.

"Prev Asset" = @SHIFT(Asset,-1);
"Next Avl Asset" = @SHIFT(Asset,2);

This examples produces the following report:


                 Jan    Feb    Mar    Apr    May    Jun
                 ===    ===    ===    ===    ===    ===			   
Asset            100    110    105    120    115    125
Prev Asset       #MI    100    110    105    120    115
Next Avl Asset   105    120    115    125    #MI    #MI

See Also

@MDSHIFT
@NEXT
@PRIOR
@SHIFTPLUS
@SHIFTMINUS


@SHIFTMINUS

The @SHIFTMINUS() function can be used in place of the @SHIFT() function, the @PRIOR() function, or the @NEXT() function to improve performance if the formula meets the following criteria:

If these criteria are met, consider rewriting your formula using @SHIFTMINUS() instead. @SHIFTMINUS() runs the formula in block mode, improving performance.

Syntax

@SHIFTMINUS (mbrName [,n, rangeList])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. n must be a numeric value, not a reference, such as a member name. If you are using @SHIFTPLUS to replace the @NEXT function, use 1 as the value for n. If you are using @SHIFTPLUS to replace the @PRIOR function, use -1 as the value for n.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as time.

Example

The following example shows a formula using @SHIFT().

Sales = Loss - @SHIFT(Sales, 1);

Here is the formula using @SHIFTMINUS() to improve performance:

@SHIFTMINUS (Loss, Sales, 1)

See Also

@SHIFT
@SHIFTPLUS
@PRIOR
@NEXT


@SHIFTPLUS

The @SHIFTPLUS() function can be used in place of the @SHIFT() function, the @PRIOR() function, or the @NEXT() function to improve performance if the formula meets the following criteria:

If these criteria are met, consider rewriting your formula using @SHIFTPLUS() instead. @SHIFTPLUS() runs the formula in block mode, improving performance.

Syntax

@SHIFTPLUS (mbrName [,n, rangeList])

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
n Optional signed integer. n must be a numeric value, not a reference, such as a member name. If you are using @SHIFTPLUS to replace the @NEXT function, use 1 as the value for n. If you are using @SHIFTPLUS to replace the @PRIOR function, use -1 as the value for n.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as time.

Example

The following example shows a formula using @SHIFT().

Sales = Loss + @SHIFT(Sales, 1);

Here is the formula using @SHIFTPLUS() to improve performance:

@SHIFTPLUS (Loss, Sales, 1);

See Also

@SHIFT
@SHIFTMINUS
@PRIOR
@NEXT


@SIBLINGS

The @SIBLINGS() function returns all siblings of the specified member. This function excludes the specified member.

This function can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@SIBLINGS (mbrName)

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

Notes

Examples

In the Sample Basic database:

@SIBLINGS (Washington)

returns Oregon, California, Utah, and Nevada (in that order).

@SIBLINGS(East)  

returns West, South, and Central (in that order).

See Also

@ISIBLINGS, which includes the specified member.
@ISISIBLING
@ISSIBLING
@LSIBLINGS
@RSIBLINGS


@SLN

The @SLN() function calculates the amount per period that an asset in the current period may be depreciated, calculated across a range of periods. The depreciation method used is straight-line depreciation.

More than one asset may be depreciated over the range. The value is depreciated from its entry period to the last period in the range. The resulting value represents the sum of all the per-period depreciation values of each asset being depreciated.

Syntax

@SLN(costMbr, salvageMbrConst, lifeMbrConst [, rangeList])

costMbr Single member specification representing an input asset for the current period.
salvageMbrConst Single member specification, variable name, or numeric expression, providing a constant numeric value. This value represents the value of the asset in the current period at the end of the useful life of the asset.
lifeMbrConst Single member specification, variable name, or numeric expression representing the useful life of the asset.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. rangeList represents the range over which the function accepts input and returns depreciation values. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

In this example, the depreciation for each year is calculated by taking into account the initial asset (Asset), the salvage value of the asset (Residual), and the life of the asset (Life).

"SLN Dep" = @SLN(Asset,Residual,Life,FY1991:FY1995);

This example produces the following report:


            FY1991  FY1992  FY1993  FY1994  FY1995  FY1996
            ======  ======  ======  ======  ======  ======
Asset        9,000       0   1,000       0       0    0
Residual    750.00    0.00    0.00    0.00       0    0
Life          5.00     #MI    5.00    0.00    0.00    0
SLN Dep       1650    1650    1850    1850    1850    0

See Also

@DECLINE
@SYD


@SPARENTVAL

The @SPARENTVAL() function returns parent-level data based on the shared parent value of the current member being calculated.

Syntax

@SPARENTVAL(RootMbr [, mbrName])

RootMbr Defines a member that is used to search for the nearest occurrence of a parent of a shared member.
mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, from which the parent values are returned.

Notes

Example

Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as parents that contain shared members as children. The Marketing Expense data must be allocated down to each Product code based on Sales contribution.

The following Product hierarchy is defined:


Product
      100
            100-10
            100-20
      200
            200-10
            200-20
     Diet ~
            100-10 SHARED
            200-10 SHARED
 Caffeine Free ~
            100-20 SHARED
            200-20 SHARED


                 Sales    Marketing
                 =====    =========	 
100-10           300         0
100-20           200         0
100              500         0
200-10           100         0
200-30           400         0
200              900         0
100-10           300         0
200-10           100         0
Diet             400        50
100-20           200         0
200-30           400         0
Caffeine Free    600        40

The Marketing Expense value is allocated down to each Product code with the following formula:

Marketing = (Sales / @SPARENTVAL(Product, Sales)) * @SPARENTVAL(Product, Marketing);

which produces the following result:


                 Sales    Marketing
                 =====    =========		 
100-10           300         37.5
100-20           200         13.3
100              500         #Missing
200-10           100         12.5
200-30           400         26.7
200              900         #Missing
100-10           300         37.5
200-10           100         12.5
Diet             400         #Missing
100-20           200         13.3
200-30           400         26.7
Caffeine Free    600         #Missing

The Marketing expenses can then be reconsolidated across Products and Markets.

See Also

@PARENTVAL
@MDPARENTVAL
@SANCESTVAL


@SPLINE

The @SPLINE() function applies a smoothing spline to a set of data points. A spline is a mathematical curve that smoothes or interpolates data.

Syntax

@SPLINE (YmbrName [, s [, XmbrName [, rangeList]]])

YmbrName A valid single member name that contains the dependent variable values used (when crossed with rangeList) to construct the spline.
s Optional. A zero (0) or positive value that determines the smoothness parameter. The default value is 1.0.
XmbrName Optional. A valid single member name that contains the independent variable values used (when crossed with rangeList) to construct the spline. The default independent variable values are 0,1,2,3, and so on.
rangeList Optional. A comma-delimited list of members, member set functions, or range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Examples

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Spline. The formula calculates the spline of Sales values for Jan through Jun, based on a smoothness parameter of 2.

"Sales Spline" = @SPLINE(Sales,2,,Jan:Jun);

This example produces the following report:


             Colas     Actual    New York
               Sales       Sales Spline
               =====       ============
Jan             645         632.8941564
Feb             675         675.8247101
Mar             712         724.7394598
Apr             756         784.2860765
May             890         852.4398456
Jun             912         919.8157517

See Also

@TREND

Algorithm







@STDEV

The @STDEV() function calculates the standard deviation of the specified data set (expList). The calculation is based upon a sample of a population. Standard deviation is a measure of how widely values are dispersed from their mean (average).

Syntax

@STDEV (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the standard deviation.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the standard deviation.
SKIPZERO Excludes all zero (0) values from expList during calculation of the standard deviation.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the standard deviation.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the standard deviation is calculated.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Std Deviation. This example calculates the standard deviation (based on a sample of a population) of the sales values for all products and uses the @RANGE function to generate expList.

FIX (Product)
"Std Deviation" = @STDEV(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:


                               Jan      New York
                              Actual      Budget
                              ======      ====== 
Sales          Colas           678         640
               Root Beer       551         530
               Cream Soda      663         510
               Fruit Soda      587         620
               Diet Drinks     #MI         #MI
                 Product      2479        2300

Std Deviation  Product          60.73       64.55

See Also

@RANGE
@STDEVP
@STDEVRANGE


@STDEVP

The @STDEVP() function calculates the standard deviation of the specified data set (expList). The calculation is based upon the entire population. Standard deviation is a measure of how widely values are dispersed from their mean (average).

Syntax

@STDEVP (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the standard deviation.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the standard deviation.
SKIPZERO Excludes all zero (0) values from expList during calculation of the standard deviation.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the standard deviation.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the standard deviation is calculated.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Std Deviation. This example calculates the standard deviation (based on the entire population) of the sales values for all products and uses the @RANGE function to generate expList.

FIX (Product)
"Std Deviation" = @STDEVP(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:

                              Jan    New York
                             Actual     Budget
                             ======     ======						 
Sales           Colas         678        640
                Root Beer     551        530
                Cream Soda    663        510
                Fruit Soda    587        620
                Diet Drinks   #MI        #MI
                  Product    2479       2300
	  
Std Deviation   Product        52.59      55.90

See Also

@RANGE

@STDEV
@STDEVRANGE


@STDEVRANGE

The @STDEVRANGE() function calculates the standard deviation of all values of the specified member (mbrName) across the specified data set (rangeList). The calculation is based upon a sample of a population. Standard deviation is a measure of how widely values are dispersed from their mean (average).

Syntax

@STDEVRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH,
mbrName [, rangeList])

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the standard deviation.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the standard deviation.
SKIPZERO Excludes all zero (0) values from expList during calculation of the standard deviation.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the standard deviation.
mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Std Deviation. This example calculates the standard deviation (based on a sample of a population) of the sales values for all products.

FIX (Product)
"Std Deviation" = @STDEVRANGE(SKIPBOTH,Sales,@CHILDREN(Product));
ENDFIX

This example produces the following report:


                               Jan      New York
                              Actual      Budget
                              ======      ====== 
Sales          Colas           678         640
               Root Beer       551         530
               Cream Soda      663         510
               Fruit Soda      587         620
               Diet Drinks     #MI         #MI
                 Product      2479        2300

Std Deviation  Product          60.73       64.55

See Also

@STDEV
@STDEVP


@SUBSTRING

The @SUBSTRING function returns the requested string of characters from an existing source string. The source string can be a text string or a member name, or it can result from a specified function that returns a text string or a single member name.

Syntax

@SUBSTRING (String, StartPosition [, EndPosition])

String A string or member name, or a function that returns a string or a single member name
StartPosition Beginning character position within String to include in the substring. An integer greater than or equal to 0, where 0 corresponds to the first character in String, 1 corresponds to the second character, and so on.
EndPosition Optional. The first position past the last character to be included in the substring. An integer greater than or equal to 0, where 0 corresponds to the first character in String, 1 corresponds to the second character, and so on. If EndPosition is not specified or is less than StartPosition, Essbase returns all remaining characters from the source string.

Example

The following examples are based on the Sample Basic database:

Function statement Result
@SUBSTRING ("100-10",1) "00-10"
@SUBSTRING ("200-21",0,2) "20"
@SUBSTRING (@PARENT(Jan),3)
(The parent of Jan is Qtr1.)
"1"

See Also

@CONCATENATE
@MEMBER


@SUM

The @SUM() function returns the summation of all the values in expList.

Syntax

@SUM (expList)

expList Comma-delimited list of member specifications, variable names, or numeric expressions, all of which provide numeric values.

Example

In the Sample Basic database:

FIX("Total Expenses")
West=@SUM(West,East);
ENDFIX

Since the calculation script fixes on Total Expenses, the value for Total Expenses->West is equal to the sum of the value for East and the values for the states making up the West. For Sales, West and East are simply the sum of the states making up each region (that is, Sales->West is not equal to the sum of East and West). This example produces the following report:


                  Product    Qtr1    Actual
                   Sales    Total Expenses	
                   =====    ==============   
New York            7705        2068	
Massachusetts       3660         892	
Florida             4132        1313	
Connecticut         3472        1087	
New Hampshire       1652         801	
  East             20621        6161	
California         11056        2742	
Oregon              5058        1587	
Washington          4835        1621	
Utah                4209        1544	
Nevada              6516        2193	
  West             31674       15848	

See Also

@SUMRANGE


@SUMRANGE

The @SUMRANGE() function returns the summation of all the values of the specified member (mbrName) across the specified range (rangeList).

Syntax

@SUMRANGE(mbrName [, rangeList])

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

Example

The following example is based on the Sample Basic database. Assume that the Year dimension contains an additional member, Partial Year. The formula for Partial Year sums the values for New York across the range of Jan through Jun. The calculation script fixes on Sales, so this formula is applied only to Sales values.

FIX(Sales)
"Partial Year"=@SUMRANGE("New York",Jan:Jun);
ENDFIX
This example produces the following report:

           Actual   New York   Colas    		   
                     Sales    
                     =====					 
Jan                   678		
Feb                   645		
Mar                   675		
Apr                   712		
May                   756		
Jun                   890		
Partial Year         4356		

See Also

@SUM


@SYD

The @SYD() function calculates the amount per period that an asset in the current period may be depreciated, across a range of periods. The depreciation method used is sum of the year's digits. More than one asset may be depreciated over the range. The value is depreciated from its entry period to the last period in the range. The resulting value represents the sum of all per-period depreciation values of each asset.

Syntax

@SYD (costMbr, salvageMbrConst, lifeMbrConst [, rangeList])

costMbr Single member specification representing an input asset for the current period.
salvageMbrConst Single member specification, variable name, or numeric expression, providing a constant numeric value. This value is the value of the asset in the current period after the useful life of the asset.
lifeMbrConst Single member specification, variable name, or numeric expression representing the useful life of the asset.
rangeList Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. rangeList represents the range over which the function accepts input and returns depreciation values. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

Example

In this example, the depreciation for each year is calculated by taking into account the initial asset (Asset), the salvage value of the asset (Residual), and the life of the asset (Life).

"SYD Dep"=@SYD(Asset,Residual,Life,FY1991:FY1994,FY1995);

This example produces the following report:


          FY1991    FY1992    FY1993    FY1994    FY1995
          ======    ======    ======    ======    ======	  
Asset     9,000         0       1,000       0          0
Residual 750.00      0.00        0.00    0.00          0
Life       5.00  #MISSING        3.00    0.00       0.00
SYD Dep    2750      2200        2150    1433        717

See Also

@DECLINE
@SLN


@TODATE

The @TODATE() function converts date strings to numbers that can be used in calculation formulas. @TODATE converts date strings into the number of seconds elapsed since midnight, January 1, 1970.

Syntax

@TODATE (formatString, dateString)

formatString The format of the date string, either "mm-dd-yyyy" or "dd-mm-yyyy".
dateString The date string.

Notes

Example

The following example is based on the Sample Basic database.

Marketing
(IF (@ATTRIBUTEVAL("Intro Date") >
        @TODATE("mm-dd-yyyy","06-30-1996"))
Marketing - (Marketing * .1);
ENDIF;);

This formula searches for members with an Intro Date attribute member that is later than 6-30-96 and decreases Marketing for those members by 10 percent. In order to process the formula, Essbase converts the date strings to numbers before it calculates.

This example produces the following report:


                                  Actual    Jan   Massachusetts
                                         Marketing
Intro Date_12-10-1996      200-30            9			
                           200-40            9			
Intro Date_10-01-1996      400-10            9			
                           400-20            9			
Intro Date_07-26-1996      200-20            9			
Intro Date_06-26-1996      300-10            9			
                           300-20            9
                           300-30            9			
Intro Date_04-01-1996      100-20           10			
                           100-30           10			
Intro Date_03-25-1996      100-10           10			
Intro Date_09-27-1995      200-10           10			

See Also

@ATTRIBUTE
@ATTRIBUTEVAL
@WITHATTR


@TREND

The @TREND() function calculates future values based on curve-fitting to historical values. The @TREND procedure considers a number of observations; constructs a mathematical model of the process based on these observations (that is, fits a curve); and predicts values for a future observation. You can use weights to assign credibility coefficients to particular observations, report errors of the curve fitting, choose the forecasting method to be used (for example, linear regression), and specify certain data filters.

Syntax

@TREND (Ylist, [Xlist], [weightList], [errorList], [XforecastList],
YforecastList, method[, method parameters] [, Xfilter1 [, parameters]] [, XfilterN [, parameters]]
[, Yfilter1 [, parameters]] [, YfilterN [, parameters]])

Ylist An expression list that contains known observations; for example, sales figures over a period of time.
Xlist Optional. An expression list that contains underlying variable values. For example, for each sales figure in Ylist, Xlist may contain a value for associated time periods. If you do not specify Xlist, the default variable values are 1,2,3, and so on, up to the number of values in Ylist.
weightList Optional. An expression list that contains weights for the data points in Ylist, for the linear regression method only. If values in weightList are #MISSING, the default is 1. Weights for methods other than linear regression are ignored. Negative weights are replaced with their absolute values.
errorList Optional. Member list that represents the differences between the data points in Ylist and the data points on the line or curve (as specified for method).
XforecastList Optional. Expression list that contains the underlying variable values for which the forecasting is sought. If you do not specify XforecastList, the values are assumed to be as follows:
{(last value in Xlist + 1), (last value in Xlist + 2), ...}
up to (last value in Xlist + the number of values in YforecastList)

If you forecast consecutively from where Ylist stops, you do not need to specify XforecastList. If you want to move the forecasting period forward, specify the new period with XforecastList.

YforecastList A member list into which the forecast values are placed.
method A choice among LR (linear regression), SES (single exponential smoothing), DES (double exponential smoothing), and TES (triple exponential smoothing). Method parameters must be numeric values, not member names. Method parameters may be any of the following:
  • LR[,t]: standard linear regression with possible weights assigned to each data point and an optional seasonal adjustment period [t], where [t] is the length of the period. In general, the weights are equal to 1 by default. You might want to increase the weight if the corresponding observation is important, or decrease the weight if the corresponding observation is an outlier or is unreliable.
  • SES[,c]: single exponential smoothing with parameter c (default c=0.2). This method uses its own weight system, using the single parameter c. Increasing this parameter gives more weight to early observations than to later ones.
  • DES[[,c1],c2]: double exponential smoothing (Holt's method) with optional parameters c1, c2 (default c1=0.2, c2=0.3). This is a two-parameter weight system and a linear subsequent approximation scheme. The first parameter controls weight distribution for the intercept; the second parameter controls weight distribution for the slope of the line fit.
  • TES[[[[,T],c1],c2],c3]: triple exponential smoothing (Holt-Winters method) with optional parameters c1, c2, c3, T (default c1=0.2, c2=0.05, c3=0.1, T=1). This is a three-parameter weight system and a linear model with a multiplicative seasonal component.
Xfilter1 ... XfilterN Optional. Use one or more of the following filter methods to scale Xlist:
  • XLOG[,c]: logarithmic change with shift c (x' = log(x+c)) (default c=1
  • XEXP[,c]: exponential change with shift c (x' = exp(x+c)) (default c=0).
  • XPOW[,c]: power change with power c (x' = x^c) (default c=2).
Yfilter1 ... YfilterN Optional. Use one or more of the following filter methods to scale Ylist:
  • YLOG[,c]: logarithmic change with shift c (y' = log(y+c)) (default c=1)
  • YEXP[,c]: exponential change with shift c (y' = exp(y+c)) (default c=0).
  • YPOW[,c]: power change with power c (y' = y^c) (default c=2).

Notes

Example

The following example is based on the Sample Basic database. It forecasts sales data for May through December, based on the trend of the same sales data from January through April. The method used is linear regression with no seasonal adjustment.

Sales(@TREND(Jan:Apr,,,,,May:Dec,LR););

This example produces the following report:


           Actual    Sales    West
                     Colas
                     =====
Jan                   2339			
Feb                   2298			
Mar                   2313			
Apr                   2332			
May                   2319			
Jun                   2318.4			
Jul                   2317.8			
Aug                   2317.2			
Sep                   2316.6			
Oct                   2316			
Nov                   2315.4			
Dec                   2314.8			
  Year               27817.2		

See Also

@LIST

Algorithm

Click the following links to see the algorithms used for each method:


@TRUNCATE

The @TRUNCATE() function removes the fractional part of expression, returning the integer.

Syntax

@TRUNCATE (expression)

expression Single member specification, function, variable name, or other numeric expression, which returns a numeric value.

Example

In the following example, Total Sales is calculated by (1) taking the sum of the values for Direct Sales and Other Sales and (2) truncating the summed values.

"Total Sales" = @TRUNCATE(@SUM("Direct Sales":"Other Sales"));

This example produces the following report:


                   Colas      New York     Actual
                      Jan       Feb       Mar
                      ===       ===       ===
Direct Sales        678.557   645.874   675.299
Other Sales         411.299   389.554   423.547
Total Sales        1089      1035      1098

See Also

@REMAINDER
@ROUND


@UDA

The @UDA function returns members based on a common attribute, which you have defined as a user-defined attribute (UDA) on the Essbase server.

Syntax

@UDA (dimName, uda)

dimName Name of the dimension with which the uda is associated.
uda Name of the user-defined attribute as it appears in the database outline.

Notes

Example

In the Sample Basic database:

@UDA(Market, "New Mkt")

Returns a list of members with the UDA of New Mkt.

See Also

@ISUDA


@VAR

The @VAR() function calculates the variance (difference) between two members. The variance calculation recognizes the difference between accounts that are tagged in the database outline as "Expense" or "No Expense" and calculates the variance accordingly.

Syntax

@VAR (mbrName1, mbrName2)

mbrName1 and mbrName2 Members from the same dimension whose variance results are to be calculated. The variance is derived by subtracting mbrName2 values from mbrName1, unless an account is tagged as "Expense", in which case mbrName1 values are subtracted from mbrName2.

Example

The following example is based on the Sample Basic database. The variance between Actual and Budget is calculated as follows:

Variance = @VAR(Actual,Budget);

In this example Sales is defined as "No Expense", whereas COGS is tagged as "Expense". This example produces the following report:


                Year    Product    Market 
                   Sales      COGS	
                   =====      ====
Actual            400855     179336	
Budget            373080     158940	
Variance           27775    (20396)

See Also

@VARPER
@VARIANCE, which calculates statistical variance based on a sample of a population
@VARIANCEP, which calculates statistical variance based on an entire population


@VARPER

The @VARPER() function calculates the percent variance (difference) between two members. The variance calculation recognizes the difference between accounts that are tagged in the database outline as "Expense" or "No Expense" and calculates the variance accordingly.

Syntax

@VARPER (mbrName1, mbrName2)

mbrName1 and mbrName2 Members from the same dimension whose variance results are to be calculated. The percent variance is derived by taking the percent variance of mbrName2 values from mbrName1, unless an account is tagged as "Expense", in which case mbrName1 values are taken as a percent variance of mbrName2.

Example

The following example is based on the Sample Basic database. The percent variance between Actual and Budget is calculated as follows:

Variance = @VARPER(Actual,Budget);

In this example Sales is defined as "No Expense", whereas COGS is tagged as "Expense". This example produces the following report:


             Year     Product     Market
                Sales        COGS	
                =====        ====
Actual          400855      179336	
Budget          373080      158940	
Variance %         7.4	     (12.8)

See Also

@VAR
@VARIANCE, which calculates statistical variance based on a sample of a population
@VARIANCEP, which calculates statistical variance based on an entire population


@VARIANCE

The @VARIANCE() function calculates the statistical variance of the specified data set (expList). The calculation is based upon a sample of a population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.

Syntax

@VARIANCE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the variance.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the variance.
SKIPZERO Excludes all zero (0) values from expList during calculation of the variance.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the variance.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the variance is calculated.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Var. This example uses the @RANGE function to generate expList, and calculates the variance of the sales values for a product family.

FIX (Product)
"Sales Var" = @VARIANCE(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:


                              Jan      New York							  
                            Actual        Budget
                            ======        ======						
Sales       Colas            678           640
            Root Beer        551           530
            Cream Soda       663           510
            Fruit Soda       587           620
            Diet Drinks      #MI           #MI
             Product        2479          2300

Sales Var   Product         3687.58       4166.67

See Also

@VARIANCEP


@VARIANCEP

The @VARIANCEP() function calculates the statistical variance of the specified data set (expList). The calculation is based upon the entire population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.

Syntax

@VARIANCEP (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)

SKIPNONE Includes all cells specified in expList, regardless of their content, during calculation of the variance.
SKIPMISSING Excludes all #MISSING values from expList during calculation of the variance.
SKIPZERO Excludes all zero (0) values from expList during calculation of the variance.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the variance.
expList Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the variance is calculated.

Notes

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Var. This example uses the @RANGE function to generate expList and calculates the variance of the sales values for a product family.

FIX (Product)
"Sales Var" = @VARIANCEP(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:


                              Jan      New York
                            Actual        Budget
                            ======        ======
Sales       Colas            678           640
            Root Beer        551           530
            Cream Soda       663           510
            Fruit Soda       587           620
            Diet Drinks      #MI           #MI
             Product        2479          2300

Sales Var   Product         2765.69       3125

See Also

@VARIANCE


@WITHATTR

The @WITHATTR() function returns all base members that are associated with an attribute that satisfies the conditions you specify. You can use operators such as >, <, =, and IN to specify conditions that must be met. @WITHATTR can be used as a parameter of another function, where that parameter is a list of members.

Syntax

@WITHATTR (dimName, "operator", value)

dimName Single attribute dimension name.
operator Operator specification, which must be enclosed in quotation marks ("").
value A value that, in combination with the operator, defines the condition that must be met. The value can be an attribute member specification, a constant, or a date-format function (that is, @TODATE).

Notes


 

Examples

The following table shows examples, based on the Sample Basic database, for each type of operator:

> @WITHATTR(Population,">","18000000") Returns New York, California, and Texas
>= @WITHATTR(Population,">=",10000000) where 10,000,000 is not a numeric attribute member, but a constant Returns New York, Florida, California, Texas, Illinois, and Ohio
< @WITHATTR(Ounces,"<","16") Returns Cola, Diet Cola, Old Fashioned, Sasparilla, and Diet Cream
<= @WITHATTR("Intro Date","<=",
@TODATE("mm-dd-yyyy",
"04-01-1996"))
Returns Cola, Diet Cola, Caffeine Free Cola, and Old Fashioned
= = @WITHATTR("Pkg Type","= =",Can) Returns Cola, Diet Cola, and Diet Cream
<> or != @WITHATTR(Caffeinated,"<>",True) Returns Caffeine Free Cola, Sasparilla, Birch Beer, Grape, Orange Strawberry
IN @WITHATTR("Population","IN",Medium) Returns Massachusetts, Florida, Illinois, and Ohio

The following two examples show @WITHATTR used in a calculation script, based on the Sample Basic database:


/* To increase by 10% the price of products that are greater than
or equal to 20 ounces */


FIX (@WITHATTR(Ounces,">=","20"))
Price = Price * 1.1;
ENDFIX

/* To increase by 10% the marketing budget for products brought
to market after a certain date */


FIX (@WITHATTR("Intro Date",">",
@TODATE("mm-dd-yyyy","06-26-1996")));
Marketing = Marketing * 1.1;
ENDFIX

See Also

@ATTRIBUTE
@ATTRIBUTEVAL
@TODATE


@XREF

The @XREF function is a cross-database function that allows a calculation taking place in one Essbase database to incorporate values from a different, possibly remote database.

Description

The following terminology is used to describe the @XREF function:

The @XREF function retrieves values from a data source to be used in a calculation on a data target. @XREF does not impose member and dimension mapping restrictions, which means that the data source and data target outlines can be different.

As arguments, this function takes a location alias, an implied list of members that represents the current point of view, and an optional list of members to qualify the @XREF query on the data source. The second argument (the members making up the current point of view) is implied; that is, these members are not specified as an @XREF parameter. An @XREF query that omits the third argument indicates that a given data point in the data target will be set to the same data point in the data source.

Syntax

@XREF (locationAlias [, mbrList])

locationAlias A location alias for the data source. A location alias is a descriptor that identifies the data source. The location alias specifies a server, application, database, username, and password. Location aliases are set by the database administrator at the database level using Application Manager, ESSCMD, or the API. For more information about setting location aliases, see the appropriate documentation. For example, if you want to set location aliases through Application Manager, see the Database Administrator's Guide or the Application Manager Online Help.
mbrList Optional. A comma-delimited list of member names that qualify the @XREF query. The members you specify for mbrList are sent to the data source in addition to the members in the current point of view in the data target. The data source then constructs a member combination, using in order of precedence:
  • The members specified in mbrList
  • The members in the current point of view
  • The top member in any unspecified dimensions in the data source

The mbrList parameter (1) modifies the point of view on the data target or (2) defines a specific point of view on the data source. For example, the following formula modifies the point of view on the data target:

1999->Jan->Inventory = @XREF(sourceDB,Dec);

If the cube on the data source (sourceDB) contains data only from 1998, this formula sets Inventory for Jan in 1999 to the Inventory value for Dec from 1998.

The following formula defines a specific point of view on the data target:

Sales->Jan = @XREF(sourceDB,January);

Assume that the data target contains the member Jan, while the data source (sourceDB) contains the member January. This formula simply maps the member in the data target (Jan) with its corresponding member in the data source (January), and pulls Sales->January from sourceDB.

See Notes for more information about the mbrList parameter.

Notes

Example

For this example, consider the following two databases:


Main Database
Year
  Qtr1
  Qtr2
Measures
  Sales
  Units  
Product
  100
    100-10
    100-20
Market
  East
  West
Scenario
  Budget
  Forecast

Inflation Rates Database


Year
  Qtr1
  Qtr2  
Assumptions
  Inflation
  Deflation = Inflation * .5 (Dynamic Calc)  
Country
  US
  Canada
  Europe

The following formula is associated with the Main Database:

Units = Units * @XREF(InflatDB,Inflation,US);

Where InflatDB is the location alias for the Inflation Rates Database and Inflation is the member for which a data value is retrieved from InflatDB.

In this example, Essbase calculates the following member combinations:

Units->Qtr1->100-10->East->Budget = Units->Qtr1->100-10->East->Budget *
Inflation->Qtr1->US

Units->Qtr2->100-10->East->Budget = Units->Qtr2->100-10->East->Budget *
Inflation->Qtr2->US
and so on.



Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.