Skip to main content
K
Kashinath
July 21, 2023

Get all Revenue Accounts using XFBR

  • July 21, 2023
  • 3 replies
  • 0 views

Guys , Any idea on how to write a business rule that retrieves a list of all Revenue Accounts .Thanks 

3 replies

O
Omkareshwar
July 21, 2023

Hi Kashinath,

This will give you a list of all the Revenue Accounts make sure you update the code as per your Metadata

//'Edit this line as per your Metadata					
Dim objList As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "EnterTheDimensionNameHere", "EnterYourAccounFilterHere", False)
					For Each MemberInfo As MemberInfo In objList 
						Dim nMemberId As Integer = BRApi.Finance.Members.GetMemberId(si, Dimtype.Account.Id, Memberinfo.Member.Name)
						Dim AccountType As AccountType = BRApi.Finance.Account.GetAccountType(si, nMemberId)
						If AccountType.Name.XFEqualsIgnoreCase("Revenue") Then
							RevenueAccounts.Add(MemberInfo.Member.Name)
						End If 
					Next
					//'This section is to print the Members from the list 
					Dim sEmptyString As String = ""
					For Each Member As String In RevenueAccounts
						sEmptyString = sEmptyString + " " + Member
					Next
						Brapi.ErrorLog.LogMessage(si, sEmptyString, "")

 

R
RobbSalzmann
July 21, 2023

This is tested in an XFBR rule.  Update accountDimName to your account dimension.

 

Dim accountDimName As String = "CorpAccounts"
Dim mFilter As String = "A#Root.Descendants" 

'get all accounts
Dim lstAccounts As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, accountDimName, mFilter, False)

'filter on revenue accounts
Dim lstRevenueAccounts As List(Of MemberInfo) = lstAccounts.Where(Function(x) BRApi.Finance.Account.GetAccountType(si, x.Member.MemberId).Equals(AccountType.Revenue)).ToList()

' show the Revenue Accounts in the log
Dim revAccounts as String = String.Join(Environment.NewLine, lstRevenueAccounts .Select(Function(x) x.Member.Name))
BRApi.ErrorLog.LogMessage(si, $"Accounts: {revAccounts}")

 

 

 

 

 

 

J
JackLacava
July 24, 2023

Do you really need a rule for this...? It can be done with a simple expansion: A#Root.Base.Where(AccountType Contains Revenue). If you really want a rule, just wrap that in a Brapi.Finance.Metadata.GetMembersUsingFilter call.

R
RobbSalzmann
July 24, 2023

RobbSalzmann_0-1690203338225.png

When the documentation fails to provide enough information, yes.  

3 people from my team worked on this and were unable to come up with working syntax for a member filter. 3 more experienced people in this thread failed to find your solution.  How many more experienced people in this forum read this and said "I don't know"?  

The syntax you provided follows no standard and looks like a sentence.  Who would guess that?

We don't want to write rules to do simple tasks.  We have no choice in situations like this.  Documentation and examples are at the top of my toolbag.  If I coded it where code isn't necessary, its because I couldn't find an example nor documentation that covers the specific requirement closely enough to be interpolated into an answer.

J
JackLacava
July 24, 2023

Hey Robb,

Sorry, my post didn't mean to criticize your approach, and I apologize unreservedly if it came off like that in any way.

I agree wholeheartedly that the docs are all but complete. The company has been focused for so long on ruthlessly delivering features, release after release, that docs have often struggled to keep up; we try to fix that every day (here, in Navigator, on the blog, in the books, etc) but there is always more to be done. Also, the platform is so big and flexible, I typically joke that "in OneStream, there are always 2 ways to do anything, and often way more"; so people come to similar problems from multiple angles, and that's totally fine.

Going back to the problem at hand: as you might have realized, I love coding. My original background is in webdev, I used to run a Python user group, and my first instinct when trying to solve a problem is to reach for a text editor. OneStream is great for that; as you know, it's really hackable in so many ways, so chances are you can solve everything with a sprinkle of code.

However, a large amount of our users are not coders, and would rather stay that way. We try hard to cater to them, making things easier and more maintainable by non-coders. This is why the team built Member Filters that are as powerful as they are: they knew that older products forced accountants to write VBScript simply to define fairly straightforward lists, and that was a pain point for a lot of people. In an ideal world, users should not need to write code just to build a list and look at some numbers in a CubeView; and where we fall short of that vision, there is work to be done.

This is why we try to promote Member Filters where possible. They are easier for non-geeks to grok, tend to be more maintainable by admins, and their performance is more predictable than most custom code. They also read almost like English, yes; for non-coders out there, that's a feature, even if it can be a bit surprising for the likes of us.

Unfortunately, as you say they are clearly not as extensibly documented, in their advanced configurations, as they should be. There are a few examples in the Sample tab of Member Filter Builder but that's just scratching the surface. This thread is an indication that there is space for better communication of these features, and I'll take that on board; keep an eye on our channels (blog and Navigator) for something related to this, sometime in the future.

I hope this makes sense. All the best,
Jack

Terms & ConditionsAccessibility statement