06-17-2024 12:08 PM
I created a USD Override form, however the translated value and the override value are both getting to the Top member. I was expected the override value to be getting to Top.
Here is the formula for USD Override Input in the Flow dimension:
And the difference in POV between the translated and override for account 399500:
Here is the formula for A#399500/settings:
06-17-2024 12:30 PM - edited 06-17-2024 12:34 PM
Hi, the first thing you might want to do is to set the aggregation weight of your override member to 0, this way this does not roll up into the top member (assuming a standard setup):
And you might want to clear the translated data on your closing member when you execute the override translation for USD. There are different ways, but I guess in your case this seems easiest. E.g.
Api.Data.ClearCalculatedData("F#EndBal_Input", False, True, False)
And I do not follow what you describe around A#399500. I hope the above helps, otherwise I would need some more information.
06-17-2024 03:33 PM
To add to what Henning said, your F#USD_Override_Input member normally has an agg weight of 0. The only formula (aka FormulaPass1 or something) on this member is typically just to carry the input override forward from period to period until there is a change as shown in the member formula below:
If (Not api.Entity.HasChildren()) AndAlso api.Cons.IsLocalCurrencyforEntity() Then
Dim firstPeriod As Boolean = api.Time.IsFirstPeriodInYear(api.Pov.Time.MemberId) 'First period in Year
If firstPeriod Then
api.Data.Calculate("F#USDOverrideEndBal:O#Import = RemoveNoData(F#USDOverrideEndBal:T#POVPrior1:O#BeforeAdj)","A#Root.Base.Where((Text8 = 'Override') And (Text2 = ''))")
Else
api.Data.Calculate("F#USDOverrideEndBal:O#Import = RemoveNoData(F#USDOverrideEndBal:T#POVPrior1:O#BeforeAdj)","A#Root.Base.Where(Text8 = 'Override')")
End If
End If
Then typically it is on your F#EndBal_Input member where you put the formula (FormulaPass2 or higher) that will look at the override and if there is one, apply that in translation rather than the standard translated amount. Here is a formula example from the BluePrint app that goes on your F#EndBal_Input member:
If (api.Cons.IsForeignCurrencyForEntity()) Then 'If foreign currency
If (Not api.Entity.HasChildren()) Then 'If base Entity
api.data.Calculate("F#EndBalLoad = Eval(F#EndBalLoad:C#Local)", AddressOf OnEvalDataBuffer) 'Override the translated value
Else 'Else If parent Entity
Dim mainEntitiesDimPk As DimPk = api.Dimensions.GetDim("MainEntities").DimPk 'Entity DimPk
Me.OverrideCons(si, globals, api, args, mainEntitiesDimPk) 'Consolidate the override accounts to accomodate Auto Translation Currencies
End If
End If
'End XFFormula
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Sub
'XFHelperFunctions
Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
Try
'Start with an empty list of result cells.
eventArgs.DataBufferResult.DataBufferCells.Clear()
Dim dataUnitPk As DataUnitPk = api.Pov.GetDataUnitPk() 'Get the current Data Unit Pk
Dim viewId As Integer = ViewMember.YTD.Id 'Get the YTD View member ID
Dim endBalMemberId = api.Members.GetMemberId(DimType.Flow.Id, "EndBalLoad") 'EndBal Member Id
Dim overrideMemberId = api.Members.GetMemberId(DimType.Flow.Id, api.Pov.Cons.Name & "OverrideEndBal") 'Override Member Id ("CurrentConsMember"OverrideEndBal)
Dim resultOrigin As String
For Each lcCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values 'Loop over the source cells.
If (Not lcCell.CellStatus.IsNoData) Then 'Only include cells that have data
If api.Account.Text(lcCell.DataBufferCellPk.AccountId,8).XFEqualsIgnoreCase("Override") Then 'Only allow this logic to happen for accounts that are tagged as "Overide" in text 8 on account
'Read the initial DataCell for C#Local:O#Top:F#EndBalLoad
Dim tempDataCellPk1 As New DataCellPk(dataUnitPk, viewId, lcCell.DataBufferCellPk)
tempDataCellPk1.ConsId = DimConstants.Local
tempDataCellPk1.OriginId = DimConstants.Top
tempDataCellPk1.FlowId = endBalMemberId
Dim lcTopCell As DataCell = api.Data.GetDataCell(tempDataCellPk1)
'Read the initial DataCell for C#Local:O#Top:F#"CurrentConsMember"OverrideEndBal
Dim tempDataCellPk As New DataCellPk(dataUnitPk, viewId, lcCell.DataBufferCellPk)
tempDataCellPk.ConsId = DimConstants.Local
tempDataCellPk.OriginId = DimConstants.Top
tempDataCellPk.FlowId = overrideMemberId
Dim overrideTopCell As DataCell = api.Data.GetDataCell(tempDataCellPk)
If lcTopCell.CellAmount <> 0.00 And overrideTopCell.CellStatus.IsRealData Then 'If the cell has a local currency value and the override has data
'Get the override rate by dividing the total override by the total local currency
Dim overrideRate As Decimal
If overrideTopCell.CellAmount = 0.00 Then
overrideRate = 0.00
Else
overrideRate = (overrideTopCell.CellAmount / lcTopCell.CellAmount)
End If
'If we are translating a AdjInput cell then the translated value should go to AdjConsolidated
If lcCell.DataBufferCellPk.GetOriginName(api).XFEqualsIgnoreCase("AdjInput") Then
resultOrigin = "AdjConsolidated"
Else 'Write to the same origin
resultOrigin = lcCell.DataBufferCellPk.GetOriginName(api)
End If
'Multiply the local currency value by the derived override rate to allocate across origins
Dim resultCell As New DataBufferCell(lcCell)
resultCell.DataBufferCellPk.OriginId = api.Members.GetMemberId(DimType.Origin.Id, resultOrigin)
resultCell.CellAmount = overrideRate * lcCell.CellAmount
eventArgs.DataBufferResult.SetCell(api.SI, resultCell, True)
End If
End If
End If
Next
Catch ex As Exception
Throw ErrorHandler.LogWrite(api.SI, New XFException(api.SI, ex))
End Try
End Sub
Private Sub OverrideCons(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs, ByVal mainEntitiesDimPk As DimPk)
Try
Dim resultDataBuffer As New DataBuffer()
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
'Get the Children in a list
Dim objList As List(Of Member) = api.Members.GetChildren(mainEntitiesDimPk, api.Pov.Entity.MemberId)
'Loop through the children
For Each entity As Member In objList
'Get a data buffer of all override account values in the currency we are translating to
Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(E#" & entity.Name & ":F#EndBalLoad, [A#BalSheetAccts.Base.Where(Text8 = 'Override')])",,False)
If sourceDataBuffer.DataBufferCells.Count > 0 Then 'If the data buffer is not empty
For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values 'Loop over the cells
If (Not sourceCell.CellStatus.IsNoData) Then 'if the source cell is not zero and is not no data
'Add the cell to the data buffer
resultDataBuffer.SetCell(si, sourceCell, True)
End If
Next
End If
Next
'Set the data buffer
api.Data.SetDataBuffer(resultDataBuffer, destinationInfo)
Catch ex As Exception
Throw ErrorHandler.LogWrite(api.SI, New XFException(api.SI, ex))
End Try
End Sub
Lastly, normally there is not a formula on your account member. The text field on the account will drive whether this rule in the Flow dim is applied.
I hope this helps. Again, the Blueprint app available for download on the Solution Exchange has a good example of flow member formulas.