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.

The minimum application permission required to run an ASO custom calculation is Database Update.

For more information about allocations and to understand the input parameters, see Performing Custom Calculations and Allocations on Aggregate Storage Databases.

Syntax

• DBS-NAME

• MDX-SET

• ALLOC-NUMERIC

• MDX-TUPLE

• MDX-MBR

Keywords

You can run ASO custom allocations in the following ways using MaxL execute allocation.

execute allocation … pov MDX-SET

Required. Provide an MDX set defining the context region in which the allocation should be performed.

execute allocation … amount ALLOC-NUMERIC

Required. Provide an MDX numeric value expression indicating the amount to be allocated.

execute allocation … 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.

execute allocation … 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.

execute allocation … target MDX-TUPLE

Required. Provide an MDX tuple defining the database region where the allocation results should be written.

execute allocation … targettimespan MDX-SET

Optional. Provide an MDX set indicating one or more time periods to be considered for the allocation target. Time periods must be level 0 members in a Time dimension.

execute allocation … targettimespanoptions

Optional, but required if targettimespan is used.

Select a method for allocating values across the target time span:

• divideamount–Divide the amount evenly across the time periods.

• repeatamount–Repeat the amount across the time periods.

execute allocation … offset MDX-TUPLE

Optional. If you use offsetting entries (general ledger counterbalancing measures), provide an MDX tuple defining the location in the database where an offsetting value should be written for each source amount.

execute allocation … debitmember MDX-MBR

Optional. If you use double-entry accounting, provide an MDX member expression indicating the member to which positive result values should be written.

execute allocation … creditmember MDX-MBR

Optional. If you use double-entry accounting, provide an MDX member expression indicating the member to which negative result values should be written.

execute allocation … range MDX-SET

Required. Provide an MDX set indicating the database region in which allocated values are calculated and written.

execute allocation … excludedrange MDX-SET

Optional. Provide an MDX set specifying locations in the range where you do not want allocation values written.

execute allocation … 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.

execute allocation … 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.

execute allocation … basistimespanoptions

Optional, but required if basistimespan is used. Select a method for using the basis time span:

• splitbasis–Use the basis value for each time period individually.

• combinebasis–Use the sum of the basis values across the time periods specified by basistimespan.

execute allocation … share

Optional. For the allocation method, allocate the amount(s) proportionately to the basis values. For syntax, see Allocation Method Specification in Notes.

execute allocation … spread

Optional. For the allocation method, 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:

• skip_missing–Skip missing basis values.

• skip_zero–Skip zero basis values.

• skip_negative–Skip negative basis values.

execute allocation … zeroamountoptions

Optional. If this syntax is omitted, zero or #MISSING amount values are allocated. If you don't want to allocate empty values, specify how to handle them:

• skip_to_next_amount–Skip to the next nonzero, non-#MISSING amount value.

• abort–Cancel the entire allocation operation.

execute allocation … zerobasisoptions

Optional. For share allocation method, this option specifies the action when the sum of all basis values is zero. For spread allocation method, this option specifies the action when all the basis values are skipped. Select one of the following options:

• skip_to_next_amount–Skip to the next nonzero, non-#MISSING amount value.

• abort–Cancel the entire allocation operation.

execute allocation … round

Optional rounding specification. Choose INTEGER or MDX-NUMERIC, plus one of the subsequent options, to specify the rounding method.

• round INTEGER|MDX-NUMERIC–Round to a specified number of decimal places, using an integer or an MDX numeric value expression. The value must be between 100 and -100, and is truncated if it is not a whole number.

• discard errors–Perform rounding, but discard rounding errors.

• errors_to_lowest–Add rounding errors to the lowest allocated value.

• errors_to_highest–Add rounding errors to the highest allocated value.

• errors_to_location MDX-TUPLE–Provide an MDX tuple indicating a cell to which the rounding error should be added.

execute allocation … [override]|add|subtract values

Optional. Values generated by an ASO custom allocation can be added to (or subtracted from) existing values, instead of overwriting them. Overwriting is the default.

When performing custom allocations on an aggregate storage cube with a federated partition, you can only override existing values. You cannot add to, nor subtract from, existing values.

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

Rounding Method Specification

Example

The following statement executes an allocation.

execute allocation process 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])"
add values;