cancel
Showing results for 
Search instead for 
Did you mean: 

Rounding the result of a formula

ChristianW
Contributor III

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?

1 ACCEPTED SOLUTION

ChristianW
Contributor III

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:

ChristianW_0-1636386810338.png

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_1-1636386938674.png

 

View solution in original post

3 REPLIES 3

MarcR
VIP

Hi Christian, i don't think there is a 'checkbox' that does this. It depends most on where you want to use it (in formulas or e.g. in reporting). If in reporting you could use a dynamic UD8 member that rounds these specific Account:UD2 combinations. If you need them in the database i would build a custom finance function that gets the databuffer with the intersections that you need and writes them back rounded.

Marc Roest
OneStream consultant @Finext

Thank you, I was a little too slow with my answer. Cheers

ChristianW
Contributor III

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:

ChristianW_0-1636386810338.png

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_1-1636386938674.png