Essbase @MDALLOCATE

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.

This 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]])
Parameter Description
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

  • When you use @MDALLOCATE 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 decrease calculation
    time.
  • For a more complex example using the @MDALLOCATE function,
    see the Oracle Essbase Database Administrator’s Guide.
  • If you have very large allocationRange lists, Essbase may return error messages during the calculation. If
    you receive error messages, you may need to raise the number for CALCLOCKBLOCK
    DEFAULT or use CALCLOCKBLOCK HIGH in your calculation script.

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