Data Buffer in Stored Calc is not working during Condolidation

Krishna
Contributor III

Hi All - I am trying to copy Base accounts from Actual to Plan Scenario but it is not working when I try to consolidate but it is working when I perform force consolidate. Below is the code and any help would be appreciated.

 

If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then
If api.Pov.Scenario.Name = "Plan" Then
api.Data.Calculate("S#Plan = Eval(S#Actual:O#Top:I#Top:U1#Total_CostCenter:U2#Total_ProfitCenter)","A#[Corp_US_Sales].Base",,,,,,,,,,,,AddressOf OnEvalDataBufferScrFilter)
Brapi.ErrorLog.LogMessage(si,"APIDATA")

End If
End If

Private Sub OnEvalDataBufferScrFilter(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
Try
eventArgs.DataBufferResult.DataBufferCells.Clear()
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
'Only process cells that have data
If (Not sourceCell.CellStatus.IsNoData) Then
'Apply filtered source cells with data as the new Data Buffer Cells
Dim resultCell As New DataBufferCell(sourceCell)

'Set cells for Data Buffer
eventArgs.DataBufferResult.SetCell(api.SI,resultCell,False)
End If
Next

Catch es As Exception
Throw ErrorHandler.LogWrite(api.SI, New XFException(api.SI,es))
End Try

End Sub

 

Thanks

 

2 ACCEPTED SOLUTIONS

EricOsmanski
Valued Contributor

You can modify the formula like this so you do not need the Eval. RemoveNoData will remove all no data cells from the source. In this formula though, you should specify a destination (left side) Origin, IC, U1, and U2.

If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then
If api.Pov.Scenario.Name = "Plan" Then
api.Data.Calculate("S#Plan = RemoveNoData(S#Actual:O#Top:I#Top:U1#Total_CostCenter:U2#Total_ProfitCenter)","A#[Corp_US_Sales].Base")
'Brapi.ErrorLog.LogMessage(si,"APIDATA")

End If
End If

View solution in original post

BTW. I rewrite the code and below is working and still wondering why my EVAL is not working anyways thanks.

 

'Dim GlobalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim POVTime As String = api.Pov.Time.Name
Dim VallTots As String =
"S#Actual:O#Top:F#EndBalInput:I#Top:U1#Total_CostCenter:U2#Total_ProfitCenter:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
Dim vAllNones As String =
":S#Plan:O#Import:F#EndBalInput:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(" & VallTots & ",[A#Corp_US_Sales.base]))")
Dim vTotal As Decimal = 0

If api.Entity.HasChildren=False And api.POV.Scenario.Name="Plan" Then

If Not sourceBuffer Is Nothing Then
Dim resultDataBuffer As DataBuffer = New DataBuffer()
For Each Cell As DataBufferCell In sourceBuffer.DataBufferCells.Values
Dim acctMemberName As String = Cell.DataBufferCellPk.GetAccountName(api)
Dim amount As Decimal = Cell.CellAmount

If amount<>0 Then
vTotal = vTotal + amount
End If
Next
End If

'If GlobalTime = POVTime And vTotal <> 0 Then
api.Data.Calculate("A#ACT_Copy" & vAllNones & _
" = " & vTotal,True)

'End If
End If

View solution in original post

6 REPLIES 6

EricOsmanski
Valued Contributor

You can modify the formula like this so you do not need the Eval. RemoveNoData will remove all no data cells from the source. In this formula though, you should specify a destination (left side) Origin, IC, U1, and U2.

If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then
If api.Pov.Scenario.Name = "Plan" Then
api.Data.Calculate("S#Plan = RemoveNoData(S#Actual:O#Top:I#Top:U1#Total_CostCenter:U2#Total_ProfitCenter)","A#[Corp_US_Sales].Base")
'Brapi.ErrorLog.LogMessage(si,"APIDATA")

End If
End If

Thanks EricOsmanski. Appreciate your quick response and I have done this through the above method and I am trying to achieve the same using the EVAL. Do you know what am doing wrong ? 

 

Thanks 

Krishna

Your Eval looks fine - just unnecessary. I believe once you put in a destination Origin, IC, U1, and U2 it will solve your formula issue. 

Also, Force Consolidate and Consolidate are different actions. A consolidate will look at the Entity's CalcStatus before performing the action. If the CalcStatus is OK, it skips the Entity. A Force Consolidate will perform the action regardless of CalcStatus, so its something else to be aware of.

Yes I get it and I know it will work. As I said earlier why the EVAL is not working trying to understand. I also have a CALC STATUS CV and when I try to consolidate the status of those LHS (PLAN)entities goes to OK,NA. Not sure what it is wrong.

 

Thanks

See below 

KS_0-1650891120835.png

KS_1-1650891165166.png

 

BTW. I rewrite the code and below is working and still wondering why my EVAL is not working anyways thanks.

 

'Dim GlobalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim POVTime As String = api.Pov.Time.Name
Dim VallTots As String =
"S#Actual:O#Top:F#EndBalInput:I#Top:U1#Total_CostCenter:U2#Total_ProfitCenter:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
Dim vAllNones As String =
":S#Plan:O#Import:F#EndBalInput:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(" & VallTots & ",[A#Corp_US_Sales.base]))")
Dim vTotal As Decimal = 0

If api.Entity.HasChildren=False And api.POV.Scenario.Name="Plan" Then

If Not sourceBuffer Is Nothing Then
Dim resultDataBuffer As DataBuffer = New DataBuffer()
For Each Cell As DataBufferCell In sourceBuffer.DataBufferCells.Values
Dim acctMemberName As String = Cell.DataBufferCellPk.GetAccountName(api)
Dim amount As Decimal = Cell.CellAmount

If amount<>0 Then
vTotal = vTotal + amount
End If
Next
End If

'If GlobalTime = POVTime And vTotal <> 0 Then
api.Data.Calculate("A#ACT_Copy" & vAllNones & _
" = " & vTotal,True)

'End If
End If