User-Defined Functions

User-defined functions are MDX functions that users create and register with the system. You can use the user-defined functions provided with Planning and Consolidation, or you can define your own.

Features

User-defined functions supplied by Planning and Consolidation include the following:

MON — month
Returns the MONTH member from the time dimension

*function MON

descendants(%Time_Dim%.CurrentMember,%Time_Dim%.[MONTH])

*endfunction
PRO — property value
Returns the value of a property of the current member of a dimension

*function PRO(%DIMENSIONNAME%,%PROPERTYNAME%)

%DIMENSIONNAME%.Properties("%PROPERTYNAME%")

*endfunction
MOVEMENT — month to month movement
Returns the movement on an account from one month to the next month

*function MOVEMENT(%ACCOUNT%)

iif(COUNT(%Time_Dim%.Children) =0,

%ACCOUNT%-(%ACCOUNT%,%Time_Dim%.PrevMember),

SUM(Descendants(%Time_Dim%.CurrentMember,99,LEAVES))

)

*endFunction
MOVEMENT2 — month to month movement on two accounts
Returns the movement on two accounts combined from one month to the next month

*function MOVEMENT2(%ACC1%,%ACC2%)

iif(COUNT(%Time_Dim%.Children)=0,

%ACC1%+%ACC2%-(%ACC1%,%Time_Dim%.PrevMember)-(%ACC2%,%Time_Dim%.PrevMember),

SUM(Descendants(%Time_Dim%.CurrentMember,99,LEAVES))

)

*endFunction
FIRSTPREV — opening balance, first month
Returns the opening balance, but only in first month of year

*function FIRSTPREV(%ACCOUNT%)

iif(COUNT(%Time_Dim%.Children=0,

iif(%time_Dim%.currentmember is openingperiod(month,ancestor(%Time_Dim%.currentmember,year)),

(%ACCOUNT%,%Time_Dim%.PrevMember),

null

),

SUM(Descendants(%Time_Dim%.CurrentMember,99,LEAVES))

)

*endFunction
FXDIFFNetIncome — difference between balance sheet profit and profit/loss profit
Returns the difference between Balance Sheet profit and Profit/Loss profit. This difference occurs because balance sheet profit is calculated based on the currency exchange rate on a specific day (last day of the month), but PL profit is calculated on the average currency rate over a month.

If the difference is zero in local currency, then the function returns the FX difference in Report currency.

*function FXDiffNetIncome(%CYNI%,%NETINC%)

%CYNI%-%NETINC%-

iif([measures].currentmember.name="YTD",null,

iif(%time_Dim%.currentmember is openingperiod(month,ancestor(%Time_Dim%.currentmember,year)),null,

(%Time_Dim%.prevmember,%CYNI%))),solve_order=5

*endFunction
FUNCALLOC — allocates the full value of an account
Allocates the full value from an account based on a property value of a member in a dimension

*function FUNCALLOC(%PROPERTY%, %PROPVALUE%,%DIMENSION%, %SOURCEACCT%)

iif(COUNT(%DIMENSION%.children)=0),iif(PRO(%DIMENSION%,%PROPERTY%)="%PROPVALUE%",%SOURCEACCT%,Null),SUM1(%DIMENSION%))

*endfunction
ENTITYALLOC — allocates value of account/entity based on ratio of account over total account
Allocates the value of an account/entity based on the ratio of account over total account with automatic offset (based on Entity/Function)

*function ENTITYALLOC(%ENTITYSRC%,%ACCOUNTSRC%,%ACCOUNTWHT%,%ENTITYTOP%)

iif(EntityDim.Children.Count=0,iif(EntityDim.Properties("Function")="%ENTITYSRC%",-%ACCOUNTSRC%,(%ACCOUNTSRC%,%ENTITYSRC%)*%ACCOUNTWHT%/(%ENTITYTOP%,%ACCOUNTWHT%)),sum(descendants(EntityDim.CurrentMember,EntityDim.[LEV1],LEAVES)))

*endfunction
OPENBALANCE — current period account balance
Returns the current period account balance

*Function OpenBalance(%ACCOUNTSRC%)

(closingperiod(month,ancestor(%Time_Dim%.currentmember,year).prevmember),%ACCOUNTSRC%,[Measures].[YTD])

*endfunction
PREVBALANCE — previous period account balance
Returns the previous period account balance

*Function PrevBalance(%ACCOUNTSRC%)

(%Time_Dim%.prevmember,%ACCOUNTSRC%,[Measures].[YTD])

*endfunction
DynamicOpenBalance — previous period or previous year account balance
Previous period or previous year account balance. This function requires a solve_order=5. Any account derived from this one must use the same or higher solve_order. This function is only suitable for dimension rule formulas; it does not work when used in advanced rule formulas.

*Function DynamicOpenBalance(%ACCOUNTSRC%)

iif([measures].currentmember.name="YTD",

(closingperiod(month,ancestor(%Time_Dim%.currentmember,year).prevmember),%ACCOUNTSRC%,[Measures].[YTD]),

(%Time_Dim%.prevmember,%ACCOUNTSRC%)

),solve_order=5

*endfunction
Hir1_P
*Function Hir1_P(%Arg%)

iif(([Account].[H1].CurrentMember.Level.Ordinal>0,%Arg%, rollupchildren(%ACCOUNT_DIM%.H1.currentMember,"+"))

*endfunction
Hir2_P
*Function Hir2_P(%Arg%)

iif([Account].[H2].CurrentMember.Level.Ordinal>0,%Arg%, rollupchildren(%ACCOUNT_DIM%.H2.currentMember,"+"))

*endfunction
Hir3_P
*Function Hir3_P(%Arg%)

iif([Account].[H3].CurrentMember.Level.Ordinal>0,%Arg%, rollupchildren(%ACCOUNT_DIM%.H3.currentMember,"+"))

*endfunction
Hir1
*Function Hir1(%Arg%)

iif([Account].[H1].CurrentMember.Level.Ordinal>0,%Arg%,0)

*endfunction
Hir2
*Function Hir2(%Arg%)

iif([Account].[H2].CurrentMember.Level.Ordinal>0,%Arg%,0)

*endfunction
Hir3
*Function Hir3(%Arg%)

iif([Account].[H3].CurrentMember.Level.Ordinal>0,%Arg%,0)

*endfunction

Activities

You can perform the following activities with user-defined functions:

Building user-defined functions

In the context of the rules module, a user-defined function acts as a placeholder. Using the user-defined function syntax (explained below) you can assign a name to your rule formulas. When you want to use the formula in a rules file, you need to enter only the user-defined function name, and the rules module uses the formula it references when it creates the LGF file. Using user-defined functions makes maintenance of your rules formulas easier because the actual formula resides in only one location, so when you make a change to a rule formula you need to do it in only one location. It also improves the readability of your rules files.

The definition for a user-defined function can be inserted anywhere in a rules file or in an included file.

Note Note

A good practice is to maintain a library of rules functions in a central file, then use an INCLUDE statement to include the library file in your logic files. This way you only have to maintain one file. Planning and Consolidation comes with a predefined library of rules functions, called LogicFunctions.lgf, where you can add your own user-defined functions to this file.

End of the note.

For single line functions:

*FUNCTION {functionname}({Param1}[,{Param2}…]) = {Function Text}

Parameter	Description
`Function Name`	The name of the function, which you define
`Param1`, `Param2`, and so on	(Optional) These parameters are used to dynamically modify the corresponding `MDX` string (the function formula). Parameters can be hard-coded (for example, `*function FXRate(USD)`) or variable (for example, `FXRate (%CURR%)`). That is, the value of the parameter is determined by the formula syntax itself.
Function Text	The function (in `MDX` syntax)

If you want to spread the formula across multiple lines you must follow this syntax:

*FUNCTION {functionname}({Param1}[,{Param2}…])

{Function text}

*ENDFUNCTION

Referring to a user-defined function

After you define your user-defined functions, you can refer to the name of the function in your rule formulas. If the definition of the user-defined function is in another file (such as a library file) you must use an INCLUDE statement to include that file in the LGF file you are working in. The following example refers to the user-defined function CTS. The reference to the user-defined function CTS and the INCLUDE statement where the user-defined function is stored are highlighted.

For example, if the following function is defined in MdxLib.lgf:

//calculate CTS(Cost of goods sold To Sales)

*function CTS(%COSTOFGOODS%,%REVENUE%)

iif(%REVENUE%=0,Null,round(%COSTOFGOODS%/(-%REVENUE%),2))

*endfunction

This function can then be used in another rules file, such as FinStdAccount.lgf, as follows:

*include mdxlib.lgf

// Financial Standard Key Performance Indicators (KPI)

#KPI130 = CTS(FSA220,FSA100),SOLVE_ORDER=100