Forum Discussion

Richard_Mayo's avatar
Richard_Mayo
New Contributor III
2 years ago

Where text field is not blank (data buffer)

Good morning all! I'm hoping somebody can put me on the right path...! I'm writing a rule with a data buffer. I want to filter the data buffer so it only runs where needed. I put in the followi...
Finance
  • JackLacava's avatar
    JackLacava
    2 years ago

    I see. Yeah my version is probably a bit more efficient. The other bit I would try to optimize is the lookup of the Text variable, with some sort of dictionary acting as a cache - so that we do the actual lookup only once. Roughly:

    ' outside the loop.
Dim AccTextCache as new Dictionary(Of Integer, String)

For each cell in yourBuffer
    ' If you've not filtered your buffer already to contain only interesting cells,
    ' do your work here, then...
        ' try to get text from cache
        Dim accText as String = AccTextCache.XFGetValue(cell.DataBufferCellPK.AccountID, Nothing)
        if accText is Nothing then
           ' not found in cache, need to look it up ...
           ' note: prefer api. methods over brapi. ones, they are faster
           accText = api.Account.Text(cell.DataBufferCellPK.AccountID, 6, scenarioTypeId, timeId)
           ' ... then store it in our cache
           AccTextCache.Add(cell.DataBufferCellPK.AccountID, accText)
        end if
        ' rest of the work here
next

    ... but the value of this effort may or may not be significant, only benchmarking would tell.

    I'd also try to get rid of any call to .GetAccountName and similar. Stick to IDs everywhere.

JackLacava's avatar
JackLacava
Honored Contributor
2 years ago

When dealing with GetDataBufferUsingFormula, you must ask yourself: "Could I put this in an Api.Data.Calculate formula, to the right of the = sign ?". If the answer is no, like in this case, you can't use it. Buffer filters are not as powerful as the ones supported in Cube Views.

There are a few possible strategies to deal with this, but they will all involve this call to get the list of acceptable members:

Dim textMembers As List(Of MemberInfo) = api.Members.GetMembersUsingFilter( _
	api.Pov.AccountDim.DimPk, "A#CashEquiv.Descendants.Where(Text6 <> '')")

Once you have that, what you do with it will depend on what you're trying to achieve with the buffer, but the following is a good default strategy.

' convert members to IDs, because it's faster to deal with them
Dim textMemberIDs As List(Of Integer) = textMembers.ConvertAll(Of Integer)( _
	New Converter(Of MemberInfo, Integer) ( _
		Function( m As MemberInfo) m.Member.MemberId ) _
	)
' retrieve a buffer as precise as we can get with FilterMembers
' Note: depending on your DataUnit size and the number of accounts you want to keep,
' this might be less efficient than retrieving a buffer for each account. 
' Can't say without benchmarks, so we keep it simple here.
Dim dbuf1 As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#All, A#CashEquiv.Descendants)")
' prepare a blank result buffer
Dim resultBuf As New DataBuffer()
' filter cells, pushing the ones we like into the filtered buffer
For Each dCell As DataBufferCell In dbuf1.DataBufferCells.Values
	If textMemberIDs.Contains(dCell.DataBufferCellPk.AccountId) Then 
		 resultBuf.SetCell(si, New DataBufferCell(dCell))
	End If
Next

(Edit for the code-golfers out there: yes, there are faster alternatives to Contains(), but if the number of accounts is small it doesn't really matter and this code is easy to read.)