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 script file, source region, and point of view (POV). Optionally specify the target, offset, and debit or credit members.

Minimum permission required: 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

You can execute custom calculations with the following options:

Keywords

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.

pov <mdx-set>

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

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.

target <mdx-tuple>

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

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

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

offset <mdx-tuple>

Optional. If offsetting entries are used, provide an MDX tuple defining the location in the database where an offsetting value for each source amount is written. You can use only stored, level-0 members in the tuple; do not use upper-level members, attribute members, or dynamic calc members.

override|add|subtract values

Optional. Generated calculation values 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;