Allocate one or more given source amounts to a target range of cells in an aggregate storage database. The source amount can be allocated to the target proportionately to a given basis, or the source amount can be spread evenly to the target region.
Allocations are typically used in the budgeting process to distribute revenues or costs.
Minimum permission required: execute.
For more information about allocations and to understand the input parameters, see “Performing Custom Calculations and Allocations on Aggregate Storage Databases” in the Oracle Essbase Database Administrator's Guide.
Syntax
Keyword | Description |
---|---|
pov <mdx-set> | Required. Provide an MDX set defining the context region in which the allocation is performed. |
amount <alloc-numeric> | Required. Provide an MDX numeric value expression indicating the amount to be allocated. |
amountcontext <mdx-tuple> | Optional. Provide an MDX tuple with one member from each dimension missing from pov and amount. This clause is required when amount is an arithmetic expression and pov does not specify two or more dimensions. It should not be used otherwise. |
amounttimespan <mdx-set> | Optional. Provide an MDX set indicating one or more time periods to be considered for the amount. The amount value is aggregated over the specified time periods, and the aggregated amount value is allocated. Time periods must be level 0 members in a Time dimension. |
target <mdx-tuple> | Required. Provide an MDX tuple defining the database region where results are written. |
targettimespan <mdx-set> | Optional. Provide an MDX set indicating one or more time periods to be considered for the target. Time periods must be level 0 members in a Time dimension. |
targettimespanoptions | Optional, but required if targettimespan is used. Select a method for allocating values across the target time span:
|
offset <mdx-tuple> | Optional. If offsetting entries are used, provide an MDX tuple defining the location in the database where an offsetting value is written for each source amount. |
debitmember <mdx-mbr> | Optional. If double-entry accounting is used, provide an MDX member expression indicating the member to which positive result values are written. |
creditmember <mdx-mbr> | Optional. If double-entry accounting is used, provide an MDX member expression indicating the member to which negative result values are written. |
range <mdx-set> | Required. Provide an MDX set indicating the database region in which allocated values are calculated and written. |
excludedrange <mdx-set> | Optional. Provide an MDX set specifying locations in the range where you do not want allocation values written. |
basis <mdx-tuple> | Required in most cases. Provide an MDX tuple that, when combined with the range, defines the location of basis values that determine how the amount is allocated. The basis can consist of upper-level or level 0 members. Optional if the allocation method used is spread, and no values are skipped; required otherwise. Basis must be omitted when the allocation method spread is used without skip options. |
basistimespan <mdx-set> | Optional. Provide an MDX set that indicates one or more time periods to be considered for the basis. Time periods must be level 0 members in a Time dimension. |
basistimespanoptions | Optional, but required if basistimespan is used. Select a method for using the basis time span:
|
share | Optional. Specify to allocate the amount(s) proportionately to the basis values. For syntax, see Allocation Method Specification in Notes. |
spread | Optional. Specify to allocate the amount(s) evenly. For syntax, see Allocation Method Specification in Notes. You can include one or more of the following skip options when using spread allocation:
|
zeroamountoptions | Optional. If omitted, zero or #MISSING amount values are allocated. Otherwise, specify treatment of amount values that are zero or #MISSING:
|
zerobasisoptions | Optional. For share, this option specifies the action when the sum of all basis values is zero. For spread, this option specifies the action when all the basis values are skipped. Select one of the following options:
|
round | Optional. Specify rounding options. The following options are available:
|
Notes
The clauses following the with keyword can be entered in any order, each separated by white space.
Each clause can only be entered once.
The pov, amount , target, range, and basis clauses are mandatory; the others are optional.
You can specify only stored, level-0 members in all of the clauses except for amount, amountcontext, basis, and the number of rounding digits; for all other arguments, do not use upper-level members, attribute members, or dynamic calc members.
Allocation Method Specification
Example
The following statement executes an allocation. For a more complete use case, see “Performing Custom Calculations and Allocations on Aggregate Storage Databases” in the Oracle Essbase Database Administrator's Guide.
execute allocation on database glrpt.db with pov "Crossjoin({[VisionUS]}, Crossjoin({[5740]}, Crossjoin({[USD]}, Descendants([Geography],[Geography].Levels(0)))))" amount "Jan + Feb" amountcontext "([100], [Beginning Balance], [Actual], [CostCenter1])" target "([Allocation], [CostCenter1])" offset "([Allocation], [CostCenter1], [100], [YearNA])" debitmember "[Debit]" creditmember "[Credit]" range "Crossjoin(Descendants([999], [Department].Levels(0)), Descendants([Year], [Year].Levels(0)))" excludedrange "{[9994], [9995], [9996]}" basis "([SQFT], [Balance], [Actual], [CostCenter2])" share zeroamountoptions abort zerobasisoptions abort negativebasisoptions zero_value targettimespanoptions divideamount round "Currency.CurrentMember.CurrencyPrecision" errors_to_location "([101], [Jan])" ;