11-07-2022 06:03 AM - last edited on 05-02-2023 10:40 AM by JackLacava
Hi all,
we have a databuffer with values to be cumulated in a specifi destination intersection.
The issue we have is that api.Data.SetDataBuffer always wirte the last value.
Is there a standard parameter (or workaround) we can use?
At the moment the only valid solution we have found is to read the current value with a specific "api.Data.GetDataCell" and then sum to the databuffer amount.
But I am sure there must be a smarter solution.
Thanks,
Marcello
Solved! Go to Solution.
11-08-2022 08:57 AM
Hi Giacomo,
we had a call with a Onestream consultant
The final outcome is that we agreed that the best way to handle cumulation is to use the "evergreen" api.Data.GetDataCell and "api.Data.Calculate" (while looping records in the databuffer).
This give us 2 advantages:
- we can instruct the calculation with element names rather than the IDs
- we can debug the calculation line by line
Thanks a lot for the help.
Marcello
11-07-2022 06:06 AM
Hi Marcello, in the SetDataBuffer statement there is an additional option (just enter a , at the end) where you can choose to append the current value. Pretty straight forward once you know where it is 😉
11-07-2022 06:25 AM
Hi Marc,
sorry how many commas do I need to put? Is it the "isDurableCalculatedData" that makes the cumulate?
Thanks,
Marcello
11-07-2022 06:35 AM - edited 11-07-2022 06:48 AM
I think Marc is talking about dataBufferObj.setCell(newValue, accumulateIfCellAlreadyExists) - that second parameter is a boolean. That method allows you to accumulate values in a single cell of the buffer.
So you will still have to loop through cells, but you can avoid having to retrieve existing numbers.
Note that you can also simply sum two buffer objects together, e.g.
Dim resultBuf as DataBuffer = originalBuffer + newValuesBuffer
11-07-2022 08:58 AM
Hi Giacomo,
thanks for the clarification but I don't think this can work.
We need to cumulate in the destination intersection, not the databuffer cell.
We are looking for the "accumulateIfCellAlreadyExists" to be applied to the SetDataBuffer, not the setCell.
Because every time we loop on a new record of databuffer the cumulated value in the destination cell has gone away.
Thanks,
Marcello
11-07-2022 09:45 AM - edited 11-07-2022 10:14 AM
I'm slightly confused by what you mean "in the destination intersection, not the databuffer cell" - cell and intersection are equivalent terms to indicate a single number. Buffers contain cells.
Hopefully this clarifies the two approaches:
Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#TargetAccount:O#Import")
Dim bufOrig As DataBuffer = api.Data.GetDataBufferUsingFormula("A#SourceAccount:O#Import")
Dim bufNew As DataBuffer = api.Data.GetDataBufferUsingFormula("A#SomeOtherAccount:O#Forms")
' sum up all cells - simple, fast, the best
api.Data.SetDataBuffer((bufOrig + bufNew), destInfo)
' or loop by record - slower, might be more flexible
' for each cell in the new set...
For Each newCell As DataBufferCell In bufNew.DataBufferCells.Values
'... copy the cell ...
Dim resultCell As New DataBufferCell(newCell)
' ... add it to the existing buffer, accumulating ...
bufOrig.SetCell(si, resultCell, True)
Next
'... then save back the original buffer, now with accumulated values
api.Data.SetDataBuffer(bufOrig, destInfo)
Note that Target and Source account don't have to be different, they can be the same.
11-07-2022 10:16 AM
Hi Giacomo,
thanks for the help but I think you are not 100% right when you say that cell and intersection are equivalent.
From what I understood of Onestream rule they are the same only in the instant in which you open the databuffer (regardless if bufOrig or bufNew ).
But after an api.Data.SetDataBuffer on the resultCell they are different !
You example works fine because you define the ExpressionDestinationInfo at the beginning.
In our example the destination account is dynamically got from an attribute of the account in the databuffer.
So it can happen that 2 different "cells" have to be cumulated on the same destination account.
At the moment, due to lack of official rule documentation and mostly lack of knowledge from our side, I think that dynamic cumulate is almost impossible (unless a "accumulateIfCellAlreadyExists" is available in the "api.Data.SetDataBuffer")
Hope this clears where we got stuck in our developments.
Ciao
Marcello
11-07-2022 11:23 AM - edited 11-07-2022 11:24 AM
The ExpressionDestinationInfo object can be created at any time, it can even be blank. That doesn't really matter.
@Marcello wrote:
But after an api.Data.SetDataBuffer on the resultCell they are different
When you create a buffer object, you are retrieving the current values from the dabase and holding them in memory. All the operations on that object's cells happen in memory.
SetDataBuffer simply pushes the buffer object you have in memory, back to the database. Whatever change you've made to the object's cells, gets saved back into the db.
If you have a buffer object with values, and you want to save them to the database - adding values if necessary - you could retrieve the current status and sum it up with something like :
Dim bufOrig as DataBuffer = api.Data.GetDataBufferUsingFormula(newBuf.CommonDataBufferCellPk.GetMemberScript(api))
api.Data.SetDataBuffer((bufOrig + newBuf), destInfo)
There are other approaches. If there is one thing I've learned, is that pretty much everything is possible with OS 😅 so it's just a matter of clarifying what you need.
11-08-2022 08:57 AM
Hi Giacomo,
we had a call with a Onestream consultant
The final outcome is that we agreed that the best way to handle cumulation is to use the "evergreen" api.Data.GetDataCell and "api.Data.Calculate" (while looping records in the databuffer).
This give us 2 advantages:
- we can instruct the calculation with element names rather than the IDs
- we can debug the calculation line by line
Thanks a lot for the help.
Marcello