05-31-2023 10:21 AM
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)
Solved! Go to Solution.
06-01-2023 04:58 AM
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).
05-31-2023 10:37 AM
I have attached the Cube for better understanding
05-31-2023 10:52 AM
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.
05-31-2023 01:38 PM
Thanks. I hard coded the time parameter for testing.
05-31-2023 11:13 AM
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.
05-31-2023 01:08 PM
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."
06-01-2023 04:58 AM
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).
06-01-2023 07:57 AM
Thanks, Jack, for the detail response and it make more sense. I have a complex math and I will be re-using the variables in my code. The code I uploaded was just to test the functionality.