Essbase @ALLOCATE

Allocates values from a member, from a cross-dimensional member, or
from a value across a member list. The allocation is based on a variety of
criteria.

This 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]])
Parameter Description
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, a warning
    message is displayed.

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,
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, rounding errors are discarded.

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.
    (For this option, Essbase does not distinguish between
    #MI and zero values.)
  • 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

  • When you use @ALLOCATE in a calculation script, use it within
    a FIX statement; for example, FIX on the member to which the allocation amount
    is loaded. Although FIX is not required, using it may improve calculation
    performance.
  • If you use @ALLOCATE in a member formula, your formula should
    look like this:

    Member Name = @ALLOCATE (...)

    This is because allocation functions never return a value; rather, they
    calculate a series of values internally based on the range specified.

  • For an example that explains the use of rounding error processing
    with the @ALLOCATE function, see the Oracle Essbase Database Administrator’s Guide.

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