Using Memberlists in Finance BR

[vmanojrc30]

Hi 

I have created a member list on Account dimension members to pull accounts with Account type "Balance Recurring". Now I need to use this member list in Finance BR  - Calculate Function Type to loop through this member list and copy data from December to Jan.

I would like assistance on the syntax to filter by this member list on a data buffer function to pull the December data. Also I would like to know whether the member list will be executed one time and stored in the memory and used across all entities OR will it be executed for each entity when it runs through the consolidation process?

I want to make sure using member list to achieve this data copy is performance efficient than directly filtering the members within the Finance Calculate BR using 

api.Data.GetDataBufferUsingFormula("RemoveNoData(FilterMembers(A#Root.Base.Where(AccountType = 'BalanceRecurring')

[EricOsmanski · Accepted solution]

It would be executed for each Entity unless you store it as a global:

https://community.onestreamsoftware.com/t5/Rules/What-is-the-globals-object-good-for/m-p/1839

[ChristianW · Accepted solution]

The data buffer function looks like this:

api.Data.GetDataBufferUsingFormula("RemoveNoData(FilterMembers(A#All, A#Root.Base.Where(AccountType = 'BalanceRecurring')))")

The first parameter of the FilterMembers function creates the query and the following once the restrictions. The first pov script doesn't need to be of the same dimensionality than the others.

This would work as well:

api.Data.GetDataBufferUsingFormula("RemoveNoData(FilterMembers(U1#None, A#Root.Base.Where(AccountType = 'BalanceRecurring')))")

[ChristianW · Accepted solution]

You don‘t need to run it in the global entity. You just need to make sure, that it run only once.

This is how it works:

SyncLock Globals.LockObjectForInitialization
Dim BR_AccountList As List(Of String) = Globals.GetObject("BR_AccountList_Global")
Dim BR_AccountMember_Name As String

If BR_AccountList is nothing Then
    
    Dim BR_AccountList_Info As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Dimensions.GetDim("RevPlan_Accounts").DimPk, "A#Root.Base.Where(AccountType = 'BalanceRecurring')", Nothing)
   
    If (Not BR_AccountList_Info Is Nothing ) Then
        BR_AccountList   = New List(Of String)
        For Each BR_AccountMember As MemberInfo In BR_AccountList_Info
            BR_AccountMember_Name = BR_AccountMember.Member.Name
            BR_AccountList.Add(BR_AccountMember_Name)
        Next
    End If
    Globals.SetObject("BR_AccountList_Global" , BR_AccountList)

End If
End SyncLock

If POVENTITY = "S0001" Then
 For Each GlobalMember As String In BR_AccountList
api.LogMessage("Member :" & GlobalMember)
 Next
End If

I‘m not at my desk to test the code, it might has some spelling issue, but it should work. 

[EricOsmanski · Accepted solution]

It would be executed for each Entity unless you store it as a global:

https://community.onestreamsoftware.com/t5/Rules/What-is-the-globals-object-good-for/m-p/1839

[ChristianW · Accepted solution]

The data buffer function looks like this:

api.Data.GetDataBufferUsingFormula("RemoveNoData(FilterMembers(A#All, A#Root.Base.Where(AccountType = 'BalanceRecurring')))")

The first parameter of the FilterMembers function creates the query and the following once the restrictions. The first pov script doesn't need to be of the same dimensionality than the others.

This would work as well:

api.Data.GetDataBufferUsingFormula("RemoveNoData(FilterMembers(U1#None, A#Root.Base.Where(AccountType = 'BalanceRecurring')))")

[vmanojrc30]

I built the below code to get the list of Balance Recurring Accounts and Store it to the Globals.I am running this rule for a single entity and assigning the Globals.SetObject within the scope of "Global" Entity.

Now when I run the Globals.GetObject on other entities it doesn't pull the list of members. It didn't work when I moved the Globals.SetObject outside of the scope of "Global" Entity as well.

It is retrieving the members only when it is run for "Global" Entity.

Any pointers would be appreciated if I am missing something.

'**********************************************************************************************************************

Dim BR_AccountList As List(Of String)

Dim BR_AccountMember_Name As String

If POVENTITY = "Global" Then

    Dim BR_AccountList_Info As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Dimensions.GetDim("RevPlan_Accounts").DimPk, "A#Root.Base.Where(AccountType = 'BalanceRecurring')", Nothing)

    If (Not BR_AccountList_Info Is Nothing ) Then

        BR_AccountList   = New List(Of String)

        For Each BR_AccountMember As MemberInfo In BR_AccountList_Info

            BR_AccountMember_Name = BR_AccountMember.Member.Name

            BR_AccountList.Add(BR_AccountMember_Name)

        Next

    End If

    Globals.SetObject("BR_AccountList_Global" , BR_AccountList)

End If

Dim BR_AccountList_Global As List(Of String) = Globals.GetObject("BR_AccountList_Global")

If POVENTITY = "S0001" Then

 For Each GlobalMember As String In BR_AccountList_Global

api.LogMessage("Member :" & GlobalMember)

 Next

End If

[vmanojrc30]

I got this working.I had to create a DM Sequence which runs the "Global" Entity calc in first step and then the remaining entities rollup Parent in the next step.

So it appears in order for this solution to work we need to calculate the Global Entity every time at first place. Even if I want to calculate another single entity I need to start with calculating the Global Entity first and then the entity I want. Please clarify my understanding.

[ChristianW · Accepted solution]

You don‘t need to run it in the global entity. You just need to make sure, that it run only once.

This is how it works:

SyncLock Globals.LockObjectForInitialization
Dim BR_AccountList As List(Of String) = Globals.GetObject("BR_AccountList_Global")
Dim BR_AccountMember_Name As String

If BR_AccountList is nothing Then
    
    Dim BR_AccountList_Info As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Dimensions.GetDim("RevPlan_Accounts").DimPk, "A#Root.Base.Where(AccountType = 'BalanceRecurring')", Nothing)
   
    If (Not BR_AccountList_Info Is Nothing ) Then
        BR_AccountList   = New List(Of String)
        For Each BR_AccountMember As MemberInfo In BR_AccountList_Info
            BR_AccountMember_Name = BR_AccountMember.Member.Name
            BR_AccountList.Add(BR_AccountMember_Name)
        Next
    End If
    Globals.SetObject("BR_AccountList_Global" , BR_AccountList)

End If
End SyncLock

If POVENTITY = "S0001" Then
 For Each GlobalMember As String In BR_AccountList
api.LogMessage("Member :" & GlobalMember)
 Next
End If

I‘m not at my desk to test the code, it might has some spelling issue, but it should work. 

[vmanojrc30]

Hi Christian

Thanks very much. This is very helpful. I have a few follow-up questions on how the Global Variables works and I hope you can help clarify.

1.So If I run a consolidation on a Parent Entity for 12 Periods, will the Global Variable be locked for ALL 12 periods throughout the Consolidation Process OR would it be re initialized every Period?I understand it is locked for ALL Entities within a period but want to know if its locked for the entire periods that we run a consolidation process.

2. If it is locked for entire periods of a Consolidation, is there a way to re assign the variables every Period? I have some Global Factors that's same across ALL entities but different across periods.

3. I am storing a SQL query output from an External DB table in the Global Variable.Ex:

VAR_dtElasticity_Rental  = Globals.GetObject("GLOBAL_dtElasticity_Rental_Table")

Within the calcs, I have to further query this table against each entities calculated value. Now is the  only option to get the records are to pull  by rows and columns like VAR_dtElasticity_Rental.Rows.Item(0).Item("ColumnName") OR Is it possible to use select statments  with the Global variables as table name? Like Select * from VAR_dtElasticity_Rental

[ChristianW · Accepted solution]

Hi

I hope you will have success with the globals, they are mighty.

1. To answer your questions, yes, the globals survive multiple periods, this can be very helpful, if the information doesn't change over time.
2. If it changes, just add the time member (or any other dimension) to the globals name: VAR_dtElasticity_Rental = Globals.GetObject("GLOBAL_dtElasticity_Rental_Table" & api.pov.time.name)
3. Yes, you can further query the datatable object. You can do so many things, I can't explain everything here, but Datatable is a Microsoft object (learn.microsoft.com), and the internet is full of tutorials and helps. You can use LINQ to access the information from a datatable's rows. In fact, it is recommended to reduce queries to the sql database as much as possible; globals are an effective way to do so.

I hope this helps and cheers

Christian

[vmanojrc30]

Thanks for providing some insights, Christian! I appreciate it.