The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

ChristianW's avatar
ChristianW
Icon for OneStream Employee rankOneStream Employee
4 years ago
Solved

Rounding the result of a formula

I want to round the values for the Revenue account for all UD2 members to whole numbers. Do I have to get the values, round them and write the value, or is there a simpler method?
Finance
  • ChristianW's avatar
    ChristianW
    4 years ago

    There is no explicit rounding function in api.data.calculate, but you can use math.round function together with the eval function, as explained here: Using-the-EVAL-function-to-loop...

    It will look like this:

    Here is the member formula part of it:

     

    api.Data.Calculate("A#60000R = EVAL(A#60000)", AddressOf onEvalDataBuffer)

     

    and here the helper function:

     

    Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)

'Loop over cells
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
            sourceCell.CellAmount = math.round(sourceCell.CellAmount)
Next

eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells
End Sub

     

    The result will look like this:

     

ChristianW's avatar
ChristianW
Icon for OneStream Employee rankOneStream Employee
4 years ago

There is no explicit rounding function in api.data.calculate, but you can use math.round function together with the eval function, as explained here: Using-the-EVAL-function-to-loop...

It will look like this:

Here is the member formula part of it:

 

api.Data.Calculate("A#60000R = EVAL(A#60000)", AddressOf onEvalDataBuffer)

 

and here the helper function:

 

Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)

'Loop over cells
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
            sourceCell.CellAmount = math.round(sourceCell.CellAmount)
Next

eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells
End Sub

 

The result will look like this:

 

Manjunathak's avatar
Manjunathak
New Contributor III
2 years ago

Hi,

api.Data.Calculate("F#TOT = EVAL(F#CALC_END-F#CALC_Activity-F#CALC_BEG)", AddressOf onEvalDataBuffer)

Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)

'Loop over cells
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
sourceCell.CellAmount = math.round(sourceCell.CellAmount)
Next

eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells
End Sub

 

i have the this code and retrieving F#TOT from CV, but it's not working

Thanks

 

  • ChristianW's avatar
    ChristianW
    Icon for OneStream Employee rankOneStream Employee
    2 years ago

    Hi Manunathag

    Is F#Tot a base member?

    Cheers

    Christian