Where text field is not blank (data buffer)

[Richard_Mayo]

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 following filter relating to the Text6 field. This syntax works in a cube view (I've tested this) but it doesn't seem to work in the vb.net rule.

Can anyone tell me what I should use here instead to return only when text 6 is not blank?

Many thanks,
Richard

[Richard_Mayo]

[RobbSalzmann]

Hi @Richard_Mayo ,

Where(Text6 <> '') should work.  I can't tell if you have a space between your quotes, you shouldn't. 

[JackLacava]

Doesn't work for me in 7.4.2, generally i've not seen Where used with FilterMembers.

[RobbSalzmann]

Hmm,  this gets pretty confusing.  Screen shot below is 7.3.1.  This is what leads me to use XFBR instead of the less-than-fully functional member filter builder.  When I write code I have full access to the language and its operators.

[JackLacava]

I mean, the syntax is valid (as he says,  in Cube Views it's fine), it just doesn't work for me inside FilterMembers. It's not the first time that I see complex filtering not working within FilterMembers, it might be just bugs but I simply tend to work around it. GetMembersUsingFilter tends to be more robust.

[RobbSalzmann]

This is good to know regarding the contrast with Cube Views, thanks for the clarification @JackLacava .  

[JackLacava]

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.)

[Richard_Mayo]

Thanks so much Jack for putting that example in, I really appreciate it. Also Robb, cheers for the input. (Weirdly the screenshot looks like a gap between apostrophes but actually there wasn't. In any case sounds like that filtering option won't work, but thanks for the suggestion.)

Jack - I'll try your idea. In the meantime I came up with a workable solution though I'm sure it's not as efficient as yours!

Basically I created a bigger source data buffer to start with (as I can't filter on it directly) (STEP 1)

Then for each cell in the data buffer I pull back the Text6 value (STEP 2)

Then I use a simple if statement to say if it's blank, do nothing, if it holds something then proceed to the calc and result buffer (STEP 3).

Like I say, probably not as efficient as your version but it's a similar logic so at least I'm on the right lines...!

FYI - the reason I'm doing it like this is because I need to pull data in the source buffer but then use the dimensionality (plus text fields) in order to drive the result cells. I didn't think I could get this working with a standard api.data.calculate.

Anyway, thanks again for the time and support, much appreciated.

[JackLacava · Accepted solution]

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.

[Richard_Mayo]

Thanks again Jack - much appreciated (sorry been out of office hence slow response but I appreciate your example and explanation which I'll work with.)