Forum Discussion

Mike_Sabourin's avatar
Mike_Sabourin
Contributor II
5 months ago

Member formula not working to copy from Import to Forms

Client has a need to do USD overrides for specific accounts in the Balance Sheet and the entire P&L. The below code was working fine in prior periods for historical data from a flat file. Now we are importing from SAP, and it works for the Balance Sheet no problem, but the code to populate the Forms member in the P&L doesn't work, and even though I'm calling all base members, the Override calculation only works on some accounts. I should mention the account dimension is extended. Not sure why it worked before but does not any longer.

Here is the relevant code. You'll notice both sections are set up similarly.

		'Move the entered Local Override amount To the USD consolidation member To override the prior period's roll forward
		api.Data.Calculate("F#USD_HO_Input:C#USD:O#Forms:U1#Input_HO" & udFix & " = Eval(F#USD_HO_Input:C#Local:O#BeforeAdj:U1#Input_HO" & udFix & ")", "A#BAL8A8B.Base.Where(text2 contains FX_Fixed)",,,,,,,,,,,, AddressOf OnEvalDataBuffer )
			'Calculate the difference between the override amount and the original translated amount in the HO_Calc UD1 member 
			api.Data.Calculate("F#End_Bal:C#USD:O#Import:U1#HO_Calc" & udFix & " =  Divide(F#USD_HO_Input:C#USD:O#Forms:U1#Input_HO" & udFix & " + .00001, F#USD_HO_Input:C#USD:O#Forms:U1#Input_HO" & udFix & " + .00001) *
																						((F#USD_HO_Input:C#USD:O#Forms:U1#Input_HO" & udFix & ") - (F#End_Bal:C#USD:O#BeforeAdj:U1#Tot_Input" & RSF & "))", "A#BAL8A8B.Base.Where(text2 contains FX_Fixed)")

		'Move the entered Local Override amount To the USD consolidation member To override the prior period's roll forward
		api.Data.Calculate("F#USD_HO_Input:C#USD:O#Forms:U1#Input_HO" & udFix & " = Eval(F#USD_HO_Input:C#Local:O#Import:U1#Input_HO" & udFix & ")", "A#PL17QFS12PL.Base",,,,,,,,,,,,AddressOf OnEvalDataBuffer)
'			'Calculate the difference between the override amount and the original translated amount in the HO_Calc UD1 member 
			api.Data.Calculate("F#End_Bal:C#USD:O#Import:U1#HO_Calc" & udFix & " =  Divide(F#USD_HO_Input:C#USD:O#import:U1#Input_HO" & udFix & " + .00001, F#USD_HO_Input:C#USD:O#import:U1#Input_HO" & udFix & " + .00001) *
																						((F#USD_HO_Input:C#USD:O#import:U1#Input_HO" & udFix & ") - (F#End_Bal:C#USD:O#BeforeAdj:U1#Tot_Input" & RSF & "))", "A#PL17QFS12PL.Base")

And here is the eval code

Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
	Dim debugSwitch As Boolean = False	'<----- Turn on the debug switch To Log debug messages
    Dim si As New SessionInfo
    Dim filteredCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
    For Each cell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values	
'		Cell.DataBufferCellPk.FlowId = api.Members.GetMemberId(dimtypeid.Flow, "USD_HO_Input")		
		If cell.CellStatus.IsRealData And api.Pov.Scenario.Name.XFEqualsIgnoreCase("Actuals")                                   
        	filteredCells(cell.DataBufferCellPk) = cell
			'********* debug info *********	
			If debugSwitch = True Then brapi.ErrorLog.LogMessage(si, "OnEvalDataBuffer Actuals Real Data: " & vbCrLf & 
																		"Time: " & api.Pov.Time.Name & vbCrLf &
																		"Entity: " & api.Pov.Entity.Name & vbCrLf &
																		"Acct: " & cell.GetAccountName(api) & vbCrLf & 
																		"cell.CSVString: " & cell.GetCSVString(api,False) & vbCrLf &
																		"Cell Value: " & Cell.CellAmount)
			'****************************** 			
			
		Else If cell.CellStatus.IsRealData And cell.CellAmount<>0 And Not api.Pov.Scenario.Name.XFEqualsIgnoreCase("Actuals")  
			filteredCells(cell.DataBufferCellPk) = cell
			'********* debug info *********	
			If debugSwitch = True Then brapi.ErrorLog.LogMessage(si, "OnEvalDataBuffer Actuals Calculated " & vbCrLf & 
																		"Time: " & api.Pov.Time.Name & vbCrLf &
																		"Entity: " & api.Pov.Entity.Name & vbCrLf &
																		"Acct: " & cell.GetAccountName(api) & vbCrLf & 
																		"cell.CSVString: " & cell.GetCSVString(api,False) & vbCrLf &
																		"Cell Value: " & Cell.CellAmount)
			'****************************** 						
        End If
    Next
    
    'Assign the filtered list of dataCells to the result that will be used in the api.Data.Calculate function.
    eventArgs.DataBufferResult.DataBufferCells = filteredCells
End Sub

When i run the debug, it shows it basically skipping the P&L accounts. Welcome any thoughts

Finance
  • Henning's avatar
    Henning
    Valued Contributor II
    5 months ago

    Hi,

    Without commenting on anything else that one might have questions about in this setup, I think it does not work for you P&L accounts because you are still using O#Import as the source for the last calculation, even though you save the data in O#Forms in the calculation right above. In the BS calculation, it is all set to O#Forms which works according to your explanation.

    If you just use O#BeforeAdj in the source definition, this will cover import and manual input (as has been done in the first calculation).

    • Mike_Sabourin's avatar
      Mike_Sabourin
      Contributor II
      5 months ago

      Good catch. I had tried that previously with no joy. I ended up putting it in a separate business rule that i call from the process stage via a data management job.