Contents
Data Declarations
ARRAY
• Syntax
– Array ArrayName[DimName] = {initial values };
• Summary
– Declares an array variable with as many members as the dimension name that follows it (declare one-dimensional array variables)
– Can be initialized with values if desired
• Optional
VAR
Declare a temporary variable that contains a single value
Control Flow
ENDEXCLUDE
ENDFIXX
ENDLOOP
EXCLUDE
FIX
LOOP
Functional
AGG
CALC
CALC ALL
CALC DIM
CALC TWOPASS
CCONV
CLEARBLOCK
CLEARCCTRACK
CLEARDATA
DATACOPY
DATAEXPORT
SET AGGMISSG
SET CACHE
SET CALCTASKDIMS
SET CALCHASHTBL
SET CALCPARALLEL
SET CLEARUPDATESTATUS
SET DATAEXPORTOPTIONS
SET COPYMISSINGBLOCK
SET CREATEBLOCKONEQ
SET CREATENONMISSINGBLK
SET FRMLBOTTOMUP
SET LOCKBLOCK
SET MSG
SET NOTICE
SET UPDATECALC
SET UPTOLOCAL
Conditionals
ELSE
ELSEIF
ENDIF
IF
Boolean
@ISACCTYPE
@ISANCEST
@ISIANCEST
@ISCHILD
@ISICHILD
@ISDESC
@ISIDESC
@ISGEN
@ISLEV
@ISMBR
@ISPARENT
@ISIPARENT
@ISSIBLING
@ISISIBLING
@ISSAMEGEN
@ISSAMELEV
@ISUDA
Relationship Functions
@ANCESTVAL
@ATTRIBUTEVAL
@ATTRIBUTEBVAL
@ATTRIBUTESVAL
@CURGEN
@CURLEV
@GEN
@LEV
@MDANCESTVAL
@MDPARENTVAL
@PARENTVAL
@SANCESTVAL
@SPARENTVAL
@XREF
Operators
*
/
%
+
—
>
>=
<
<=
==
<>
!=
AND
NOT
OR
->
Math
@ABS
Return the absolute value of an expression
@AVG
Return the average value of the values in the specified member list
@EXP
Return the value of e (the base of natural logarithms) raised to power of the specified expression
@FACTORIAL
Return the factorial of an expression
@INT
Return the next-lowest integer value of a member or expression
@LN
Return the natural logarithm of a specified expression
@LOG
Return the logarithm to a specified base of a specified expression
@LOG10
Return the base-10 logarithm of a specified expression
@MAX
Return the maximum value among the expressions in the specified member list
@MAXS
Return the maximum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values
@MIN
Return the minimum value among the expressions in the specified member list
@MINS
Return the minimum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values
@MOD
Return the modulus produced by the division of two specified members
@POWER
Return the value of the specified member raised to the specified power
@REMAINDER
Return the remainder value of an expression
@ROUND
Return the member or expression rounded to the specified number of decimal places
@SUM
Return the summation of values of all specified members
@TRUNCATE
Return the truncated value of an expression
@VAR
Return the variance (difference) between two specified members.
See Calculating Variances or Percentage Variances Between Actual and Budget Values.
@VARPER
Return the percentage variance (difference) between two specified members.
See Calculating Variances or Percentage Variances Between Actual and Budget Values.
Member Set
@ALIAS
@ALLANCESTORS
@ANCEST
@ANCESTORS
@ATTRIBUTE
@CHILDREN
@CURRMBR
@DESCENDANTS
@ENUMVALUE
@GENMBRS
@IALLANCESTORS
@IANCESTORS
@ICHILDREN
@IDESCENDANTS
@ILANCESTORS
@ILDESCENDANTS
@ILSIBLINGS
@IRDESCENDANTS
@IRSIBLINGS
@ISIBLINGS
@LANCESTORS
@LDESCENDANTS
@LEVMBRS
@LIST
@LSIBLINGS
@MATCH
@MEMBER
@MERGE
@NAME
@NEXTSIBLING
@PARENT
@PREVSIBLING
@RANGE
@RDESCENDANTS
@RELATIVE
@REMOVE
@RSIBLINGS
@SHARE
@SHIFTSIBLING
@SIBLINGS
@UDA
@WITHATTR
@XRANGE
@EQUAL
@NOTEQUAL
@EXPAND
@MBRPARENT
@MBRCOMPARE
@BETWEEN
@LIKE
Range (Financial)
@ACCUM
@AVGRANGE
@COMPOUND
@COMPOUNDGROWTH
@CURRMBRRANGE
@DECLINE
@DISCOUNT
@GROWTH
@INTEREST
@IRR
@MAXRANGE
@MAXSRANGE
@MDSHIFT
@MINRANGE
@MINSRANGE
@NEXT
@NEXTS
@NPV
@PRIOR
@PRIORS
@PTD
@SHIFT
@SHIFTMINUS
@SHIFTPLUS
@SLN
@SUMRANGE
@SYD
Allocation
@ALLOCATE
Источник информации
Allocates values from a member, from a cross-dimensional member, or from a value across a member list. The allocation is based on a variety of criteria.
Syntax
@ALLOCATE (amount, allocationRange, basisMbr, [roundMbr], method [, methodParams] [, round [, numDigits][, oundErr]])
| amount | A value, member, or cross-dimensional member that contains the value to be allocated into allocationRange. The value may also be a constant.
If the amount parameter is a loaded value, it cannot be a Dynamic Calc member. |
| allocationRange | A comma-delimited list of members, member set functions, or range functions, into which value(s) from amount are allocated. allocationRange should be from only one level (for example, @CHILDREN(Total Expenses) rather than from multiple levels (for example, @DESCENDANTS(Product)). |
| basisMbr | A value, member, or cross-dimensional member that contains the values that provide the basis for the allocation. The method you specify determines how the basis data is used. |
| roundMbr | Optional. The member or cross-dimensional member to which rounding errors are added. The member (or at least one member of a cross-dimensional member) must be included inallocationRange. |
| method | The expression that determines how values are allocated. One of the following:
|
| round | Optional. One of the following:
|
| numDigits | An integer that represents the number of decimal places to round to. You must specify numDigits if you specify roundAmt.
If you specify roundAmt, you also can specify a roundErr parameter. |
| roundErr | Optional. An expression that specifies where rounding errors should be placed. You must specify roundAmt in order to specify roundErr. If you do not specify roundErr, rounding errors are discarded.To specify roundErr, choose from one of the following:
|
Description
This function allocates values that are input at an upper level to lower-level members. The allocation is based upon a specified share or spread of another variable. For example, you can allocate values loaded to a parent member to all of that member’s children. You can specify a rounding parameter for allocated values and account for rounding errors.
Notes
When you use @ALLOCATE in a calculation script, use it within a FIX statement; for example, FIX on the member to which the allocation amount is loaded. Although FIX is not required, using it may improve calculation performance.
If you use @ALLOCATE in a member formula, your formula should look like this:
Member Name = @ALLOCATE (…)
This is because allocation functions never return a value; rather, they calculate a series of values internally based on the range specified.
For an example that explains the use of rounding error processing with the @ALLOCATE function, see the Hyperion Essbase — System 9 Database Administrator’s Guide.
Example
Consider the following example from the Sample Basic database. The example assumes that the Scenario dimension contains an additional member, PY Actual, for the prior year’s actual expenses. Data values of 7000 and 8000 are loaded into Budget->Total Expenses for Jan and Feb, respectively. (For this example, assume that Total Expenses is not a Dynamic Calc member.)
You need to allocate values to each expense category (to each child of Total Expenses). The allocation for each of child of Total Expenses is based on the child’s share of actual expenses for the prior year (PY Actual).:
FIX(«Total Expenses»)
Budget = @ALLOCATE(Budget->»Total Expenses»,@CHILDREN(«Total Expenses»),
«PY Actual»,,share);
ENDFIX
This example produces the following report:
Product Market
PY Actual Budget
Jan Feb Jan Feb
=== === === ===
Marketing 5223 5289 3908.60 4493.63
Payroll 4056 4056 3035.28 3446.05
Misc 75 71 56.13 60.32
Total Expenses 9354 9416 7000 8000
@MDALLOCATE
Источник информации
Allocates values from a member, from a cross-dimensional member, or from a value across multiple dimensions. The allocation is based on a variety of criteria.
Syntax
@MDALLOCATE (amount, Ndim, allocationRange1 … allocationRangeN, basisMbr, [roundMbr], method [, methodParams] [, round [, numDigits][, roundErr]])
| amount | A value, member, or cross-dimensional member that contains the value to be allocated into each allocationRange. The value may also be a constant.
If the amount parameter is a loaded value, it cannot be a Dynamic Calc member. |
| Ndim | The number of dimensions across which values are allocated. |
| allocationRange1…allocationRangeN | Comma-delimited lists of members, member set functions, or range functions from the multiple dimensions into which values from amount are allocated. |
| basisMbr | A value, member, or cross-dimensional member that contains the values that are used as the basis for the allocation. The method you specify determines how the basis data is used. |
| roundMbr | Optional. The member or cross-dimensional member to which rounding errors are added. This member (or at least one member of a cross-dimensional member) must be included in an allocationRange. |
| method | The expression that determines how values are allocated. One of the following:
|
| round | Optional. One of the following:
|
| numDigits | An integer that represents the number of decimal places to round to. You must specify numDigits if you specify roundAmt.
If you specify roundAmt, you also can specify a roundErr parameter. |
| roundErr | Optional. An expression that specifies where rounding errors should be placed. You must specify roundAmt in order to specify roundErr. If you do not specify roundErr, Essbase discards rounding errors. To specify roundErr, choose from one of the following:
|
Description
This function allocates values from a member, from a cross-dimensional member, or from a value across multiple dimensions. The allocation is based on a variety of criteria.
This function allocates values that are input at an upper level to lower-level members in multiple dimensions. The allocation is based upon a specified share or spread of another variable. You can specify a rounding parameter for allocated values and account for rounding errors.
Notes
When you use @MDALLOCATE in a calculation script, use it within a FIX statement; for example, FIX on the member to which the allocation amount is loaded. Although FIX is not required, using it may decrease calculation time.
For a more complex example using the @MDALLOCATE function, see the Hyperion Essbase — System 9 Database Administrator’s Guide.
If you have very large allocationRange lists, Essbase may return error messages during the calculation. If you receive error messages, you may need to raise the number for CALCLOCKBLOCK DEFAULT or use CALCLOCKBLOCK HIGH in your calculation script.
Example
Consider the following example from the Sample Basic database. A data value of 500 is loaded to Budget->Total Expenses->East for Jan and Colas. (For this example, assume that Total Expenses is not a Dynamic Calc member.)
You need to allocate the amount across each expense category for each child of East. The allocation for each child of East is based on the child’s share of Total Expenses->Actual:
FIX(«Total Expenses»)
Budget = @MDALLOCATE(Budget->»Total Expenses»->East,2,
@CHILDREN(East),@CHILDREN(«Total Expenses»),Actual,,share);
ENDFIX
This example produces the following report:
Jan Colas
Marketing Payroll Misc Total Expenses
========= ======= ==== ==============
Actual New York 94 51 0 145
Massachusetts 23 31 1 55
Florida 53 54 0 107
Connecticut 40 31 0 71
New Hampshire 27 53 2 82
East 237 220 3 460
Budget New York 102.174 55.435 0 #MI
Massachusetts 25 33.696 1.087 #MI
Florida 57.609 58.696 0 #MI
Connecticut 43.478 33.696 0 #MI
New Hampshire 29.348 57.609 2.173 #MI
East #MI #MI #MI 500
Forecasting
@MOVAVG
@MOVMED
@MOVMAX
@MOVMIN
@MOVSUM
@MOVSUMX
@SPLINE
@TREND
Statistical
@CORRELATION
@COUNT
@MEDIAN
@MODE
@RANK
@STDEV
@STDEVP
@STDEVRANGE
@VARIANCE
@VARIANCEP
Date & Time
@DATEDIFF
@DATEPART
@DATEROLL
@FORMATDATE
@TODATE
@TODATEEX
@TODAY
Miscellaneous
@CALCMODE
@CONCATENATE
@SUBSTRING