Aggregating a tagged set of accounts (e.g. Balance Sheet only)

[OSAdmin]

This is an example to obtain values for a Cash Flow formula for all accounts tagged with a certain Text1 field.  For every Account tagged CF_AR (Cash Flow - Accounts Receivable) and evaluating the Flow member Activity Calc, if the Activity Calc and the Account Tag in UD1 match, the number is added to the data buffer. Values are then sign-flipped by multiplying them by -1.

There are two main approaches, depending on the version you're working with. For releases 6.0 and later, we can leverage GetDataBufferUsingFormula to produce performant code that is easy to read. For older releases, we have to use a more complex approach via the Eval function.

GetDataBufferUsingFormula - v6.0 and above

' retrieve the account ID once, outside the loop, to avoid multiple lookups
Dim cfArId as Integer = api.Members.GetMemberId(DimType.Account.Id, "CF_AR")
' create the result buffer
Dim targetBuf As New DataBuffer()
' retrieve source data, limited to tagged accounts with a complex filter
Dim sourceBuf As DataBuffer = api.Data.GetDataBufferUsingFormula( _
	"RemoveNoData(FilterMembers(F#ActivityCalc, [A#BalanceSheet.Base.Where(Text1 = CF_AR)]))")
' loop through each cell
For Each cell As DataBufferCell In sourceBuf.DataBufferCells.Values
	' copy cell into new one, so we can safely tweak it
	Dim newCell As New DataBufferCell(cell)
	' change the account
	newCell.DataBufferCellPk.AccountId = cfArId
	' Stuff cell into target buffer. 
	' The last parameter ensures multiple values are summed up, 
	'   rather than replacing each other
	targetBuf.SetCell(si, newCell, True)
Next

' assign target buffer to a variable we can reference in Calculate
api.Data.FormulaVariables.SetDataBufferVariable("myBuf", targetBuf, False)

' actually save the buffer into the database, against the target destination, while flipping signs.
api.Data.Calculate("A#CF_AR:F#CF_Activity = $myBuf * -1")

Note: this sample aims to strike a balance between code complexity, flexibility, and performance. More performant approaches are possible: you could modify the Flow ID also, and flip sign, inside the loop; then just saving results with api.Data.SetDataBuffer, rather than going through a Calculate call. That would be less flexible to integrate with other requirements though (e.g. performing other operations before saving, which can be easily done in Calculate but not with buffer objects).

Eval - v.5.x and below

Formula

The Calculate call is straightforward. Notice the second parameter; that must be the name of our Helper Function.

api.Data.Calculate("A#CF_AR:F#CF_Activity = EVAL(F#ActivityCalc) * -1", AddressOf onEvalDataBuffer)

Helper Function

This can be reused over and over, so it might be better placed in a shared Business Rule for ease of maintenance.

Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
	
	Dim cfAccountId As Integer = api.Members.GetMemberId(DimType.Account.Id, "CF_AR")
	Dim cfFlowId As Integer = api.Members.GetMemberId(DimType.Flow.Id, "CF_Activity")
	'Retrieve the target accounts, and keep their IDs in a Dictionary
	Dim ms As String = "A#BalanceSheet.Base.Where(Text1 = CF_AR)"
	Dim members As List(Of MemberInfo) = _
		api.Members.GetMembersUsingFilter( _
			api.Pov.AccountDim.DimPk, ms, Nothing)
	Dim memberlookup As New Dictionary(Of Integer, Object)
	If Not members Is Nothing Then
		For Each memInfo As MemberInfo In members
			memberlookup.Add(memInfo.Member.MemberId, Nothing)
		Next
	End If

	'Loop over cells that match our "TEXT1" account filter
	Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
	For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
		' Ignore NoData cells and where the account is not tagged
		If(Not sourceCell.CellStatus.IsNoData) And _
				memberLookup.ContainsKey(sourceCell.DataBufferCellPk.AccountId) Then
			' We don't assign the original cell object to output buffer, 
			'  but an edited copy
			Dim cashflowCell As New DataBufferCell(sourceCell)
			cashflowCell.DataBufferCellPk.AccountId = cfAccountId
			cashflowCell.DataBufferCellPk.FlowId = cfFlowId
			Dim existingCell As DataBufferCell = Nothing
			If resultCells.TryGetValue(cashflowCell.DataBufferCellPk, existingCell) Then
				'Since there is already a cell in the dictionary,
				' replace it with the aggregated amount.
				existingCell.CellAmount = existingCell.CellAmount + sourceCell.CellAmount
			Else
				'Add this dataCell to the new dictionary.
				resultCells.Add(cashflowCell.DataBufferCellPk, cashflowCell)
			End If
		End If
	Next

	'Assign the new list of DataCells to the result.
	eventArgs.DataBufferResult.DataBufferCells = resultCells
End Sub