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

mpavan's avatar
mpavan
New Contributor II
3 years ago
Solved

Filter Result DataBuffer based on value in secondary Source Buffer

Hello Experts, 

I have a requirement of calculating Cash_ADJ based a sum value of 2 different buffers. 

If the sum value is Positive I have to post a cash_adj with value from bufSrc2. Is it possible to filter Buffer 1 and write corresponding buffer 2 value to result buffer? 

Or the approach has to be completely changed for this requirement?

 

 

If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
	Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#Cash_ADJ")
	Dim bufSrc1 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Cash")
	Dim bufSrc2 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Loan")
	Dim bufSrc3 As DataBuffer = bufSrc1 - bufSrc2
	Dim resultBuf As New DataBuffer ()

	For Each newCell As DataBufferCell In bufSrc3.DataBufferCells.Values
		If Not NewCell.CellAmount > 0 Then
			Dim resultCell As New DataBufferCell(newCell)
			resultBuf.SetCell(si, resultCell, True)
		End If
	Next
	api.Data.SetDataBuffer(resultBuf * -1, destInfo)
End If

 

 

Any Ideas on how I can further analyze this will be helpful. 

Finance
  • mpavan's avatar
    mpavan
    3 years ago

    Thanks' for the suggestion. I have tried but unable to make it work using member formula. Still a lot of learning  to do. In the meanwhile I had used api.calculate with in the loop and it had worked as expected, 

    If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
    Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#Cash_ADJ")
    Dim bufSrc1 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Cash")
    Dim bufSrc2 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Loan")
    Dim bufSrc3 As DataBuffer = bufSrc1 - bufSrc2
    Dim resultBuf As New DataBuffer ()

    For Each newCell As DataBufferCell In bufSrc3.DataBufferCells.Values
    If Not NewCell.CellAmount > 0 Then
    api.data.calculate("A#Cash_ADJ:F#ENDBAL=A#Loan:F#ENDBAL * -1")
    End If
    Next
    api.Data.SetDataBuffer(resultBuf * -1, destInfo)
    End If

3 Replies

  • ckattookaran's avatar
    ckattookaran
    Valued Contributor
    3 years ago

    You can definitely do it. Now the trick is to be inside the buffer loop check for your value and then if it meets the condition then query the next buffer keys with the ids from your current loop. 

    Doing api.data.calculate can cause performance degradation. Since you are doing it inside a loop and then also calling the data units into memory. 

  • chul's avatar
    chul
    Icon for OneStream Employee rankOneStream Employee
    3 years ago

    Write a member formula on A#Cash_ADJ. If the sum of A#Cash + A#Loan is greater than zero, then A#Cash_ADJ = A#Loan

    • mpavan's avatar
      mpavan
      New Contributor II
      3 years ago

      Thanks' for the suggestion. I have tried but unable to make it work using member formula. Still a lot of learning  to do. In the meanwhile I had used api.calculate with in the loop and it had worked as expected, 

      If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
      Dim destInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#Cash_ADJ")
      Dim bufSrc1 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Cash")
      Dim bufSrc2 As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Loan")
      Dim bufSrc3 As DataBuffer = bufSrc1 - bufSrc2
      Dim resultBuf As New DataBuffer ()

      For Each newCell As DataBufferCell In bufSrc3.DataBufferCells.Values
      If Not NewCell.CellAmount > 0 Then
      api.data.calculate("A#Cash_ADJ:F#ENDBAL=A#Loan:F#ENDBAL * -1")
      End If
      Next
      api.Data.SetDataBuffer(resultBuf * -1, destInfo)
      End If