Forum Discussion

Krishna's avatar
Krishna
Valued Contributor
2 years ago
Solved

Data Buffer

Hi - I am trying to execute the below code but it is not taking the prior month actual data. Could you please let me know what am doing wrong? It is copying only the current month. 

 

 

				Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#YTD:F#EndBalInput:S#Actual:T#2026M11,[A#[BalanceSheet].Base],[U1#[Total_CostCenter].Base])")
								
				
				srcdataBuffer.LogDataBuffer(api, "Results",10)
				
				Dim Acct As String = String.Empty
				Dim UD1 As String = String.Empty
				Dim UD2 As String = String.Empty
				Dim UD3 As String = String.Empty
				Dim UD4 As String = String.Empty	
				Dim UD5 As String = String.Empty
				Dim UD6 As String = String.Empty
				Dim UD7 As String = String.Empty
				Dim UD8 As String = String.Empty
				Dim f As String = String.Empty
				Dim Inc As String = String.Empty
				Dim Org As String = String.Empty
				
				Dim ResultDb As New DataBuffer
				
				For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
					
					If Cell.CellAmount > 0 Then 
					'If Cell.CellStatus.IsNoData = True Then
					
						Acct  = Cell.DataBufferCellPk.GetAccountName(api)
						UD1 = Cell.DataBufferCellPk.GetUD1Name(api)
						UD2 = Cell.DataBufferCellPk.GetUD2Name(api)
						UD3 = Cell.DataBufferCellPk.GetUD3Name(api)
						UD4 = Cell.DataBufferCellPk.GetUD4Name(api)
						UD5 = Cell.DataBufferCellPk.GetUD5Name(api)
						UD6 = Cell.DataBufferCellPk.GetUD6Name(api)
						UD7 = Cell.DataBufferCellPk.GetUD7Name(api)
						UD8 = Cell.DataBufferCellPk.GetUD8Name(api)
						f = Cell.DataBufferCellPk.GetFlowName(api)
						Org = Cell.DataBufferCellPk.GetOriginName(api)
						Inc = Cell.DataBufferCellPk.GetICName(api)
						
					
							Dim ResultsCell As DataCell
							ResultsCell = api.Data.GetDataCell("V#YTD:S#Actual:T#2026M10:" & "F#" & f & ":" & "I#"& Inc & ":" & "O#" & Org & ":" & "A#" & Acct & ":" & "U1#" & UD1 & ":" & "U2#" & UD2 & ":" & "U3#" & UD3 & ":" & "U4#" & UD4 & ":" & "U5#" & UD5 & ":" & "U6#" & UD6 & ":" & "U7#" & UD7 & ":" & "U8#" & UD8)

							
							Brapi.ErrorLog.LogMessage(si, "flow" & f & "Orgin" & Org & "IC" &  Inc & "Account" & Acct & "U1" & UD1 & "U2" & UD2 & "U3" & UD3 & "U4" & UD4 & "U5" & UD5 & "U6" & UD6 & "U7" & UD7 & "U8" & UD8 & ResultsCell.CellAmountAsText)
							
							Dim ResultsCellf As New DataBufferCell(cell)
							ResultsCellf.CellAmount = Cell.CellAmount + ResultsCell.CellAmount
							ResultDb.SetCell(api.SI,ResultsCellf,False)
					
					End If
					
				Next
				
				Dim d As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("V#YTD:F#EndBalInput:A#" & Acct & ":U1#" & UD1)
				'Dim d As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#" & Acct & ":U1#" & UD1)
				api.Data.SetDataBuffer(ResultDb,d)

 

Finance
  • JackLacava's avatar
    JackLacava
    2 years ago

    That's better. Note that you don't even need to assign buffers to variables for simple math, you can just do:

     

    Dim buf1 as DataBuffer = api.data.GetDataBufferUsingFormula(...)
Dim buf2 as DataBuffer = api.data.GetDataBufferUsingFormula(...)

Dim result as DataBuffer = buf1 + buf2
api.Data.SetDataBuffer(si, result, targetDestExpressionInfo)

     

    As for the last bit: you know how, in your first code, you retrieved a buffer then looped through its cells? You can simply retrieve two buffers, then manipulate their cells directly. A buffer is effectively a dictionary, where keys are DataBufferCellPK and values are DataBufferCell; so you can get the PK of cells from buf1 and look up if there is an equivalent one with the same PK in buf2. Because this would be done in memory, rather than retrieving from db every time (which is what GetDataCell would do), it's still fast enough (even though working at the buffer level is likely to be faster).

9 Replies

Sort By
  • JackLacava's avatar
    JackLacava
    Community Manager
    2 years ago

    You're filtering on Flow at the very beginning, which means the Flow member you find in cells is XFCommon. Then you use that to try and locate data elsewhere; because the cell definition is valid, there is no error, but the cell will always be empty because XFCommon is not a valid Flow. At the very minimum, you will have to specify a valid Flow member in that GetDataCell call.

    However, I would add that this is not the best approach - you'll be forcing a lot of lookups and a lot of database calls, it's going to be super slow. You should simply use buffers: get a buffer for M11, get a buffer for M10, sum them up, save results to database. If you really want to go cell by cell, you should still pull the two buffers in memory and then work with cells from those objects.

    • Krishna's avatar
      Krishna
      Valued Contributor
      2 years ago

      Thanks Jack you are spot on the XFCommon because when I try to print the logbuffer it is has F#XFCommon.

      A#A1000:F#XFCommon:O#Import:I#None:U1#C100:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 3,000.00, IsRealData, Input
      A#A1000:F#XFCommon:O#Import:I#None:U1#C200:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 0.00, IsDerivedData, StoredButNoActivity
      A#A3000:F#XFCommon:O#Import:I#None:U1#C200:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 3,000.00, IsRealData, Input
      A#A1001:F#XFCommon:O#Import:I#None:U1#C100:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 0.00, IsDerivedData, StoredButNoActivity
      A#A1001:F#XFCommon:O#Import:I#None:U1#C200:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, 3,000.00, IsRealData, Input

       

      1. Are you refering to use 2 databuffer for M11 & M10 ? ex below

      				Dim M1 As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(V#YTD:F#EndBalInput:S#Actual:T#2026M11),[A#[BalanceSheet].Base],[U1#[Total_CostCenter].Base]")
				api.Data.FormulaVariables.SetDataBufferVariable("M100",M1,False)
				
				Dim M2 As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(V#YTD:F#EndBalInput:S#Actual:T#2026M10),[A#[BalanceSheet].Base],[U1#[Total_CostCenter].Base]")
				api.Data.FormulaVariables.SetDataBufferVariable("M111",M2,False)
				
				api.Data.Calculate("F#EndBalInput= RemoveZeros($M100 + $M111)
				")

       

      Could you please elaborate on  " If you really want to go cell by cell, you should still pull the two buffers in memory and then work with cells from those objects."

       

       

      • JackLacava's avatar
        JackLacava
        Community Manager
        2 years ago

        That's better. Note that you don't even need to assign buffers to variables for simple math, you can just do:

         

        Dim buf1 as DataBuffer = api.data.GetDataBufferUsingFormula(...)
Dim buf2 as DataBuffer = api.data.GetDataBufferUsingFormula(...)

Dim result as DataBuffer = buf1 + buf2
api.Data.SetDataBuffer(si, result, targetDestExpressionInfo)

         

        As for the last bit: you know how, in your first code, you retrieved a buffer then looped through its cells? You can simply retrieve two buffers, then manipulate their cells directly. A buffer is effectively a dictionary, where keys are DataBufferCellPK and values are DataBufferCell; so you can get the PK of cells from buf1 and look up if there is an equivalent one with the same PK in buf2. Because this would be done in memory, rather than retrieving from db every time (which is what GetDataCell would do), it's still fast enough (even though working at the buffer level is likely to be faster).

  • Krishna's avatar
    Krishna
    Valued Contributor
    2 years ago

    I have attached the Cube for better understanding 

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    The Time period in GetDataCell is hard-coded to 2026M10.  The Time period in GetDataBufferUsingFormula is hard-coded to 2026M11.  What do you mean by "Current Month"?

    Also, not likely related but should be fixed, your variable in this bit of code should be upper case to match its declaration.

     

    • Krishna's avatar
      Krishna
      Valued Contributor
      2 years ago

      Thanks. I hard coded the time parameter for testing.