Execute Calculation (Aggregate Storage)

Click here for non-aggregate storage version

Use this statement to execute a custom calculation script expressed in MDX, specifying the calculation script file, source region, and point of view (POV). Optionally, specify the target, offset, and debit or credit members.

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

For more information about custom calculation script parameters, see Performing Custom Calculations and Allocations on Aggregate Storage Databases.

Syntax

Description of the illustration excalc_as.gif

Keywords

You can run ASO custom calcs in the following ways using MaxL execute calculation.

execute calculation … with local script_file

Required. Run the specified local calculation script file. Custom calculation scripts are expressed in MDX. The following is an example of a custom calculation script, script.txt.


(AccountA,Proj1) := 100;
([AccountB], [Proj1]) := ([AccountB], [Proj1]) * 1.1;
(AccountC,Proj1) := 
	((AccountB,Proj1,2007) + (AccountB, Proj1)) / 2;
(AccountA,Proj2) := 
	((AccountD,Proj1) + 
	  (AccountB,Proj2)) / 2;

For information about writing custom calculation scripts, see Performing Custom Calculations and Allocations on Aggregate Storage Databases.

execute calculation … pov MDX-SET

Required. Provide an MDX set defining the context region in which the calculation should be performed. The calculation script will be executed once for every cross-product in the POV region.

execute calculation … sourceregion MDX-SET

Required. Provide an MDX set specifying the region of the cube referred to by the formulas in the script. At a minimum, the source region should include all members from the right-hand sides of the assignment statements in the custom calculation script.

execute calculation … target MDX-TUPLE

Optional. Provide an MDX tuple defining the database region where results should be written. You can use only stored, level-0 members in the tuple; do not use upper-level members, attribute members, or dynamic calc members.

execute calculation … 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. You can specify only stored, level-0 members; do not use upper-level members, attribute members, or dynamic calc members.

execute calculation … 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. You can specify only stored, level-0 members; do not use upper-level members, attribute members, or dynamic calc members.

execute calculation … offset MDX-TUPLE

Optional. If you use offsetting entries (general ledger counterbalancing measures), provide an MDX tuple defining the location in the database where the offsetting value for each source amount should be written. You can use only stored, level-0 members in the tuple; do not use upper-level members, attribute members, or dynamic calc members.

execute calculation … override|add|subtract values

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

Notes

Each clause can only be entered once.
The script_file, pov, and sourceregion clauses are mandatory; the others are optional.
The optional clauses following the sourceregion specification can be entered in any order, each separated by white space.
You can specify only stored, level-0 members on the left side of the assignment statement in the custom calculation script; do not use upper-level members, attribute members, or dynamic calc members.
You can specify only stored, level-0 members in the following clauses: DebitMember, CreditMember, Target, and Offset.

Example

The following statement executes script.txt referenced above. For a sample use case, see Performing Custom Calculations and Allocations on Aggregate Storage Databases.


execute calculation on database app.db with
  local script_file "script.txt"
  POV "Crossjoin({[VisionUS]},
    Crossjoin({[101]},
      Crossjoin ({[Jan]},
        Crossjoin({[Scenario]},
          Descendants(Geography, Geography.Levels(0))))))"
  SourceRegion "Crossjoin({[AccountB], [AccountD]},
    Crossjoin({[Proj1], [Proj2]}, {[2007]}))"
  Target "(Allocation)"
  DebitMember "[BeginningBalance_Debit]"
  CreditMember "[BeginningBalance_Credit]"
  Offset  "([Account_000], [Project_000])"
  add values;