Execute Calculation (Aggregate Storage)
Click here for non-aggregate storage version
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: execute.
For more information about custom calculation script parameters, see “Performing Custom Calculations and Allocations on Aggregate Storage Databases” in the Oracle Essbase Database Administrator's Guide.
Syntax
You can execute custom calculations with the following options:
| Keyword | Description |
|---|---|
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” in the Oracle Essbase Database Administrator's Guide. |
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. |
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 script_file, pov, and sourceregion clauses are mandatory; the others are optional.
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” in the Oracle Essbase Database Administrator's Guide.
execute calculation on app.db with
local script_file "script.txt"
POV "Crossjoin({[VisionUS]},
Crossjoin({[101]},
Crossjoin ({[Jan]},
Crossjoin({[Scenario]},
Descendants(Geography, Geography.Levels(0))))))"
Target "(Allocation)"
DebitMember "[BeginningBalance_Debit]"
CreditMember "[BeginningBalance_Credit]"
Offset "([Account_000], [Project_000])"
SourceRegion "Crossjoin({[AccountB], [AccountD]},
Crossjoin({[Proj1], [Proj2]}, {[2007]}))" ;