Writing Formulas
The following section provide information on writing formulas.
Writing Dynamic Cell Calculations
When writing a formula for a Dynamic Calc Account, Flow, or User Defined Member in either a Member Formula or a Business Rule (via FinanceFunctionType.GetDataCell), the goal is to return an amount for a single data cell. In that case, the system knows the full 18 Dimensions of the data cell it needs to display. Therefore, use any of the api methods that refer to a specific Account, Flow, Intercompany, User Defined Dimension, etc.
For example, a user selected a Cube View to view some numbers. The full 18 Dimensions for each cell the Cube View needs to display is determine that it needs to run a custom formula and it initializes the api, so it knows about all 18 Dimensions. The Member Formula then displays an amount on the Cube View.
Dynamic Calc Formula Examples
Return a Constant
The simplest Dynamic Calc formula is to return a constant (i.e., the same number regardless of the intersection). For example, select an Account (or a Flow or User Defined Member) in the Dimension Library, set the Formula Type property to DynamicCalc, and then enter the following line in the Member’s Formula property for the Default Scenario Type and Default Time Member. After typing the code, press the Compile button on the Formula Editor’s toolbar to make sure the VB.NET syntax is correct, press OK to close the dialogs, and then click Save.
Return 123.4
If a Cube View is used to display the result of the above calculation for the Dynamic Calc account for any numeric intersection (any Entity, Scenario, Time, UD, etc. for View set to YTD), the Cube View will always display 123.4. If the account’s value is displayed for any text intersection (any Entity, Scenario, Time, UD, etc. for View set to Annotation), the Cube View will show an empty cell because the formula returned a numeric value, not a text value. To make the formula work for both numeric and text View Members, use an If statement to check which type of View Member is currently being requested. Then return a text value surrounded by double quotes when the View Member is an annotation type. After saving, run the Cube View using multiple View Members (YTD, Periodic, Annotation, Assumptions, etc.). The corresponding cell will display either of the two constants specified.
objViewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberId) If objViewMember.IsAnnotationType Then Return "My first OneStream Member Formula" Else Return 123.4 End If
Return Types for Dynamic Calc Formulas
As shown in the example above, the return value for a numeric data cell can be a number such as a constant or a decimal variable, and the return type for a text intersection can be a text value such as words within double quotes or a String variable. However, when those types are returned, the underlying engine converts them to a DataCell or a DataCellEx object automatically. Therefore, if a DataCell or DataCellEx object was already contained by calling api.Data.GetDataCell, then it is recommended to return the full object instead of just the number contained within the DataCell object (i.e., which could be accessed using objDataCell.CellAmount). In order to specify status such as whether the cell is NoData, return a DataCell or DataCellEx and use its CellStatus property for those types of settings.
A DataCell object is a wrapper for a DataCellPk object that defines the cell’s 18 Dimensional intersection, a decimal accessed using the CellAmount property to store the number, and a CellStatus containing other information about the cell such as NoData and Invalid Status.
A DataCellEx object is a wrapper for a DataCell object and a text property called DataCellAnnotation which is used for setting a string for an Annotation type View Member. It also contains some additional properties for CurrencyId and AccountTypeId filled in and can be ignored when creating a DataCellEx object in a Dynamic Calc Member Formula.
The following example accomplishes the exact same result as the example above, except this uses DataCell and DataCellEx objects to illustrate what to do if a return value containing cell status is needed.
Dim objViewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberId) If objViewMember.IsAnnotationType Then Dim objDataCellEx As DataCellEx = New DataCellEx() objDataCellEx.DataCell.CellStatus = New DataCellStatus(True) objDataCellEx.DataCellAnnotation = "My first OneStream Member Formula" Return objDataCellEx Else Dim objDataCell As DataCell = New DataCell() objDataCell.CellStatus = New DataCellStatus(True) objDataCell.CellAmount = 123.5 Return objDataCell End If
Using Math in Dynamic Calc Formulas
OneStream’s api.Data.GetDataCell function supports a powerful script parser that allows math equations to be written and operate on one or more data cells or constant amounts to calculate the values for a new data cell. For example, if a Dynamic Calc account needs to display Cash plus AcctsRec increased by 10%, this one-line Member Formula can do it.
Return api.Data.GetDataCell("(A#Cash + A#AcctsRec) * 1.10")
Notice the api.Data.GetDataCell function accepts one string Parameter in double quotes which represents a Member Script equation. Each operand of the equation (e.g., A#Cash) takes the unspecified Dimensions using the data cell currently being calculated. Therefore, every operand points to a specific data cell identified by using an 18 Dimension intersection.
If the current data cell being calculated is:
“E#CT:C#USD:S#Actual:#2013M1:V#YTD:A#NewAccount:F#None:O#Top:I#None:U1#None, …”,
then the first operand is:
“E#CT:C#USD:S#Actual:#2013M1:V#YTD:A#Cash:F#None:O#Top:I#None:U1#None, …”
even though only “A#Cash” was specified.
When writing Dynamic Calc formulas, specify any or all the 18 Dimensions if necessary.
(e.g., “A#Cash:U1#AllProducts + A#AcctsRec:U1#None”)
Division in Dynamic Calc Formulas
Using Member Script equations in api.Data.GetDataCell as shown above is powerful for Dynamic Calc formulas, but there are occasions when all the math cannot be performed reliably in one line of script. For example, use this formula to divide by a data cell when the data cell being used as the denominator is zero or NoData (i.e., a number was never entered). Since dividing by zero results in infinity and is an invalid operation for computers, the formula needs some extra checking. To illustrate additional concepts, here are three different examples of performing division in a Dynamic Calc formula.
This first example uses the division operator (/) to calculate a data cell from one account divided by a data cell from another account. If the denominator (A#AcctsRec) is zero or NoData, it will automatically return a very large number (e.g., 9999999999999999999.0) as the result. This is because dividing by zero in mathematics results in infinity, and the large number to approximate infinity which allows subsequent functions or math operators that refer to the result to continue to be processed is used.
Return api.Data.GetDataCell("A#Cash / A#AcctsRec")
Although an extremely large number is the best mathematical approximation for infinity, it is typically not what administrators want to display in their financial system when source numbers are not available. A Divide function that produces a NoData cell if either the numerator or the denominator is NoData is available.
Return api.Data.GetDataCell("Divide(A#Cash, A#AcctsRec)")
The built-in Divide function is typically used when performing division in Dynamic Calc formulas. However, for completeness and to provide some insight about how to create more complex formulas, the following is an example of how to implement a formula that performs safe division. Notice that the Member Formula is performing division using two DataCell objects to create a resulting DataCell (Return numeratorDataCell / denominatorDataCell). This is a powerful capability that allows any type of math to be performed using any number of DataCell objects.
Dim numeratorDataCell As DataCell = api.Data.GetDataCell("A#Cash")
Dim denominatorDataCell As DataCell = api.Data.GetDataCell("A#AcctsRec")
If ((Not numeratorDataCell Is Nothing) And (Not denominatorDataCell Is Nothing)) Then
If ((Not numeratorDataCell.CellStatus.IsNoData) And _
(Not denominatorDataCell.CellStatus.IsNoData) And _
(denominatorDataCell.CellAmount <> 0.0)) Then
Return numeratorDataCell / denominatorDataCell
End If
End If
Return Nothing
TIP: When using VB.NET, use the underscore character at the end of a line in order to continue a statement on the next line. For example, refer to the underscore after And in the code above. This was done here because the full statement did not fit on one line in this document. However, in the Member Formula editor, the If statement would be kept on one long line.
Days Sales Outstanding
Days Sales Outstanding (DSO) is a common formula that is a required calculation for many applications. Consequently, OneStream has provided a pre-built function to encapsulate the logic required for this function.
Return api.Functions.GetDSODataCell("AcctsRec", "Sales")
The example below demonstrates a possible customized version of the DSO calculation.
Dim numDaysSum as Integer = 0
Dim currTimeId as Integer = api.Pov.Time.MemberPk.MemberId
Dim acctsRec as decimal = api.Data.GetDataCell("A#AcctsRec - A#SaleTax").CellAmount
If (acctsRec > 0.0) Then
Dim salesSum as Decimal = 0.0
Dim numPeriodsInYear = api.Time.GetNumPeriodsInYearForSameFrequency(currTimeId)
For (numPeriodsToSubtract As Integer = 0 To numPeriodsInYear)
Dim timeId as Integer
If numPeriodsToSubtract = 0 Then
timeId = currTimeId
Else
timeId = api.Time.AddTimePeriods(-1 * numPeriodsToSubtract, True)
End If
Dim timeName As String = api.Time.GetNameFromId(timeId)
Dim numDaysInTimePeriod As Integer = api.Time.GetNumDaysInTimePeriod(timeId)
Dim MemberscriptBldr = New MemberscriptBuilder("A#Sales:V#Periodic").SetTime(timeName)
Dim Memberscript As String = MemberscriptBldr.GetMemberscript()
Dim salesForTimePeriod as Decimal = api.Data.GetDataCell(Memberscript).CellAmount
If (salesForTimePeriod + salesSum >= acctsRec) Then
Dim ratio As Decimal = (acctsRec - salesSum) / salesForTimePeriod
numDaysSum = numDaysSum + (ratio * numDaysInTimePeriod)
'We are done
Exit For
Else
numDaysSum = numDaysSum + numDaysInTimePeriod
salesSum = salesSum + salesForTimePeriod
End If
Next
End If
Return api.Data.CreateDataCellObject(numDaysSum, False, False)
Dynamic Simulation of Consolidation: GetEntityAggregationDataCell
Use a function called GetEntityAggregationDataCell for pseudo/approximate consolidation of a data cell with a Cube View when requested for display. The intention is not to produce a value that would tie to a formally consolidated number if custom Business Rules for ownership or translation are in play for such a financial model, but this on-the-fly dynamic value is presented to the user instantly as a convenience, typically during data entry.
This function employs standard currency translation using the Entity in the cell’s POV’s local currency as the source and a parent Entity’s target and standard Percent Consolidation on the Relationship Properties to calculate Share. It does not take intercompany elimination into account unless that value was already consolidated and stored.
api.Functions.GetEntityAggregationDataCell(memberScript as string,
Optional useStoredAmountsWhereCalcStatusIsOK as Boolean, Optional
fxRateTypeRevenueExpOverride as String, Optional
fxRuleTypeRevenueExpOverride as FxRuleType, Optional
fxRateTypeAssetLiabOverride as String, Optional
fxRuleTypeAssetLiabOverride as FxRuleType)
When useStoredAmountsWhereCalcStatusIsOK is set to True (the default setting), the algorithm checks if CalcStatus is OK for the intersection and uses numbers that are already calculated, translated, ownership share calculated, eliminated, consolidated and stored. This setting is also useful when displaying multiple translated currencies for consolidated parent Entities dynamically. If the parent Entity’s Local amount is OK and fully consolidated, the only dynamic calculation would be the last translation step.
The last four optional settings let you specify alternate named FX Rate Types (e.g. “AverageRate”) and FX Rule Types (i.e. FxRuleType.Direct or FxRuleType.Periodic) to perform what-if simulations.
If any of these four are specified, useStoredAmountsWhereCalcStatusIsOK is ignored and is treated as False because consolidated amounts would not have been stored using the alternate FX rates.
Examples when used with a Dynamic Calc member (e.g. UD8):
Return api.Functions.GetEntityAggregationDataCell("A#NetIncome:S#Budget:UD8#None")
Return api.Functions.GetEntityAggregationDataCell("UD8#None", True, “AverageRate”, FxRuleType.Periodic)
Return api.Functions.GetEntityAggregationDataCell("UD8#None", True, “AverageRate”, FxRuleType.Periodic, “HistoricalRate”, FxRuleType.Direct)
Example use of GetEntityAggregationDataCell to enter inputs and instantly see aggregated results upon saving a Form:
Create a UD8 Member named EntityAggregation and set its Formula Type to Dynamic Calc. Use this as the UD8 member's Formula:
Return api.Functions.GetEntityAggregationDataCell("UD8#None")
Create a Cube View
Set Cube View POV tab to have Scenario set to Budget or whatever is desired on the Form and UD8 Member of EntityAggregation.
Define rows that display a hierarchy of Entities.
Define columns that display a few key Accounts. Include a base-level Account that supports input for both UD8#EntityAggregation and for input. E.g. A#Sales, A#Sales:UD8#None:Name(“Sales Input”)
Change Cube View settings for General Settings / Common with Can Modify Data and Can Calculate set to True.
Associate this Cube View with a Form Template which is assigned to a Workflow Profile.
The user enters a number on the Form in the Account that accepts input and clicks Save. The user will see the dynamically aggregated Entity results without having to run a Consolidation.
Dynamic Calc Member Formula
This formula returns the name and description of the Entity’s UD1 Default Member. Other UD8 formulas can be created for the Entity’s UD2 Default, etc. After adding the UD8 Member, the Entity’s Default UD1 Member name can be displayed using XFGetCell or in a Cube View by accessing the cell: E#MyEntity:V#Annotation:U8#EntityDefaultUD2Name.
This assumes that the UD8 Member with the formula is named EntityDefaultUD2Name. If XFGetCell is being used in Excel, use None or any valid Member for all the other Dimensions.
'Display the Member name of the entity's DefaultUD1 Member using the Annotation View Members.
If api.View.IsAnnotationType(api.Pov.View.MemberId) Then
Dim text As String = String.Empty
Dim udId As Integer = api.Entity.GetDefaultUDMemberId(api.Pov.Entity.MemberId, DimType.UD1.Id)
If udId <> DimConstants.Unknown Then
Dim udMember As Member = api.Members.GetMember(DimType.UD1.Id, udId)
If Not udMember Is Nothing Then
text = udMember.NameAndDescription
End If
End If
Return text
End If
'If this is a numeric View Member (e.g., Periodic, YTD), display the number from the U8#None Member.
Return api.Data.GetDataCell("U8#None")
Annotations on Dynamic Calc Members
To support the ability to calculate text or pull it from an external system to display in a Cube View, DynamicCalc or DynamicCalcTextInput formulas can be used. If the user wants the formula to calculate the annotations, use a DynamicCalc formula to display text such as Pass and Fail.
The DynamicCalcTextInput Formula Type works the same as a DynamicCalc formula, but it allows users to input annotations on Cube View cells without having to use the Data Attachment Dialog. When this formula is used, the user can make annotations on Dynamic Calc Members following the same method as a non-calculated Member.