Finance Function Types
Use Finance Function Types, except for Custom Calculate, to run logic in certain calculation sequences during the calculation sequence while a Function Type is in process. There are thirteen Function Types:
Calculate
Additional logic during calculation of Entity, Consolidation Scenario and Time. This sets the value of one or more values (left side of Formula) equal to another (right side). It then executes a calculation for a specifically qualified Point of View. This is the most common function used.
There are situations where the Entity being processed must access another Entity’s data. In situations involving pulling Consolidation dimension Elimination results from other Entities as siblings, the multi-thread processing of the calculations requires an additional function to ensure the calculations are complete. Below is an example of this sibling relationship. The Entities Base1, Base2, etc. are siblings that would be calculated simultaneously during a consolidation:
For this purpose, the Calculate Finance Function Type supports the argument IsSecondPassEliminationCalc.
During the Calculate process, this function allows Business Rules to execute after the Sibling Entities have calculated results to the Consolidation Elimination member. Below is a reference to the Consolidation dimension:
Once all the sibling Entity members are calculated to Elimination, the Business Rules within the IsSecondPassEliminationCalc will be executed.
Translate
Additional logic that uses custom translation.
FXRate
Custom logic used to determine Foreign Exchange rates for any intersection.
Consolidate Share
Additional logic used during the custom calculation of the Share Member.
Consolidate Elimination
Additional logic used during the custom calculation of the Elimination Member.
Custom Calculate
A CustomCalculate Finance Function Type can be used in order to execute a single year custom calculation via a Dashboard Parameter Component Server Task Action. This is considered a partial calculation and does not store the calculated data or run the calculation during a consolidation. Running a custom calculation from a Dashboard will impact calculation status for the affected data unit even if the data does not change. See Parameter Components in Presenting Data With Books, Cube Views and Dashboards for more details on how to assign this type of Finance Rule to a Dashboard. See Data Management for details on creating this type of Data Management Step. See Parameter Components for more information on passing arguments to a Custom Calculate function.
Example:
Select Case api.FunctionType
Case Is = FinanceFunctionType.CustomCalculate
If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("FunctionName") Then
Me.CalcTest(si, globals, api, args)
api.Data.Calculate("A#TFS2903 = A#10000 + A#69000)
End If
End Select
Calculation Drill Down Member Formula
Provides custom drill down results.
Conditional Input Rule
Conditional Input Rules make data cells read-only. While the settings for this can be done directly on the Cube, using a Conditional Input Business Rule offers more flexibility and still allows the use of the Cube settings. This rule can return the following: ConditionalInputResultType.Default, ConditionalInputResultType.NoInput, ConditionalInputResultType.NoInputAllowCellDetail, and ConditionalInputResultType.NoCellDetailAllowInput.
The following Business Rule example will make all cells for the Account 6000 read-only. This should be added to a Business Rule attached to a Cube.
Case Is = FinanceFunctionType.ConditionalInput
If api.Pov.Account.Name.XFEqualsIgnoreCase("6000") Then
Return ConditionalInputResultType.NoInput
End If
Return ConditionalInputResultType.Default
Confirmation Rule
Special logic that runs with Confirmation Rules.
Data Cell
Named GetDataCell calculations that can be reused such as a Better/Worse calculation in Cube Views.
Dynamic Calc Account
Special logic to use in Dynamic Calc members.
Member List and Member List Headers
A custom list of members for use in Cube Views and other areas. See Commonly Used Member Filter Functions in Cubes for more details on using custom lists in a Cube View.
Select Case api.FunctionType is the expression used when a certain process needs to be isolated and run special logic. See examples below:
Select Case api.FunctionType Case Is = FinanceFunctionType.Calculate
‘ Additional logic to run with every calculation. api.Data.Calculate("A#DACash1= A#Cash4 + 1") api.Data.Calculate("A#2150:F#Movement = V#Periodic:A#5750:F#None") if api.metaData.Cons.name ="USD" then api.Data.Calculate("A#Cash1=A#[Restricted Cash]") End If Case Is = FinanceFunctionType.Translate
‘ Additional logic to run with every translation. If api.ExecuteDefaultTranslation is not included, then the standard translation process will not run. api.ExecuteDefaultTranslation dim Cubeid as integer = api.Cubes.GetCubeInfo("GolfStream").Cube.Cubeid
dim fxRule as fxRuleType=api.Cubes.GetFxRuleTypeForAssetLiability(CubeId)
api.Data.Translate("A#[Restricted Cash]:C#[USD]","A#[Restricted Cash]",fxRule,.5)
Case Is = FinanceFunctionType.ConsolidateShare then
‘ Additional logic to run when the Share Member of the Consolidation Dimension is being calculated.
api.ExecuteDefaultShare()
api.data.calculate("A#[Restricted Cash]=A#[Restricted Cash]*.8")
Case Is = FinanceFunctionType.ConsolidateElimination then
‘ Additional logic to run while the Elimination Member of the Consolidation Dimension is being
calculated.
api.ExecuteDefaultElimination
api.Data.Calculate("A#[IC AR]=A#[IC AR] * 1.1")
Case Is = FinanceFunctionType.MemberListHeaders
‘ Additional logic that defines the names of custom Member Lists
Dim myMemberListHeaders = New List(Of MemberListHeader)myMemberListHeaders.Add(new MemberListHeader
("Sample Member List"))Return myMemberListHeaders Case Is = FinanceFunctionType.MemberList ‘ Additional logic that defines the Members within custom Member List that are included in Business Rules.
See the “MemberList Functions” section in the proceeding pages. 'Example: "A#Root.[Sample MemberList]" If args.MemberListArgs.MemberListName = "Sample Member List" Then Dim myMemberListHeader =
new MemberListHeader(args.MemberListArgs.MemberListName) Dim myMembers = new List(Of Member)() Dim myMemberList = New MemberList(myMemberListHeader, myMembers)myMembers.AddRange(api.Members. GetChildren (args.MemberListArgs.DimPk,
args.MemberListArgs.TopMember.MemberPk.MemberId,args.MemberListArgs.DimDisplayOptions)) Return myMemberList End If End Select
Ultimate Ownership
This function calculates and stores Ultimate Ownership results for every ancestor/child Entity relationship. The current Entity being calculated is the ancestor Entity, and when storing the results, the Members in the Intercompany Dimension are used to represent each descendant Entity. This function assumes the source Direct Ownership numbers are weights and are typically entered by a user (e.g., numbers between 0.0 and 1.0).
C#OwnerPreAdj:O#AdjInput is using an account that does not consolidate and accepts manual entry for adjustments in those Parent/Child relationships. It cascades the user-entered amounts up the Entity tree and multiplies them when a Parent owns part of a Child and that Child owns part of a Grandchild.
sourceDirectOwnershipMemberscript represents the data cells where the user-entered Direct Ownership amounts are found for Parent/Child Entity combinations.
destUltimateOwnershipMemberscript represents the intersection for the resulting calculations. All results are stored using the current Data Unit being calculated.
This function is intended to run only for the Entity's Local Consolidation Member, and it uses IC Members to store the results for each descendant Entity.
Example: Use the following as a Member Formula in the UltimateOwnership account:
If api.Cons.IsLocalCurrencyForEntity() Then
api.Data.CalculateUltimateOwnership("C#OwnerPreAdj:A#DirectOwnership:O#AdjInput",
"A#UltimateOwnership:O#Forms", 1.0)
End If
Here are the steps to set up Ownership entry and calculations in an application. This functionality is intended to be used in a separate Control Cube accessed via the main Financial Cube:
Create Two Accounts
DirectOwnership
This is for the user to enter Direct Ownership for each Parent/Child Entity combination.
AccountType = BalanceRecurring, IsConsolidated = False, Adjustment Type = Data Entry
UltimateOwnership
This stores the calculated results in Parent Entities using IC Members for every descendant Entity.
AccountType = BalanceRecurring, FormulaType = Formula Pass 1, Allow Input = False,
IsConsolidated = False, Is IC Account = Conditional
Formula:
If api.Cons.IsLocalCurrencyForEntity() Then
api.Data.CalculateUltimateOwnership("C#OwnerPreAdj:A#DirectOwnership:O#AdjInput",
"A#UltimateOwnership:O#Forms", 1.0)
End If
Create a Cube View for entering Direct Ownership Amounts
Cube View POV
Consolidation = OwnerPreAdj, Origin = AdjInput, Flow, IC and UDs = None
Column
A#DirectOwnership
Row
E#Root.TreeDescendants
Create a Cube View for Running a Consolidation and Viewing the Ultimate Ownership Results
Cube View POV
Consolidation = Local, Account = UltimateOwnership, Origin = Forms, Flow IC and UDs = None
Column
IC#ICEntities.Base
Row
E#Root.TreeDescendants