Skip to main content
O
otmaneoirda
Newcomer
March 5, 2025
Solved

Member expansion on the "Remove" Function Alternative

  • March 5, 2025
  • 4 replies
  • 0 views

Hello Community,

I'm writing a rule to copy data from one scenario to another using api.data.calculate.

However, I would like to exclude some accounts from being copied. Something like the following:

api.Data.Calculate("V#YTD:S#ACTUAL=RemoveZeros(V#YTD:S#ACTUAL_TEST)","A#Root.Base.remove(A#CAPEX_ACCOUNTS.Base)")

 


I understand that the Remove function will not accept a member expansion. Members to be removed must be listed in the function as specified in this post:

"What is the best approach to “subtract” members in a hierarchy, the following member filter is not returning the expected results. E#Tot_Mgmt.Remove(Houston) | OneStream Community"

But is there any alternative to do it without listing accounts to remove one by one ?

Any help is appreciated!

Thank you

Best answer by MarcusH

You can remove more than one by separating them with a comma. Eg .Remove(Acc1, Acc2)

4 replies

M
MarcusHAnswer
Expert
March 6, 2025

You can remove more than one by separating them with a comma. Eg .Remove(Acc1, Acc2)

S
OneStream Employee
sameburnOneStream Employee
OneStream Employee
March 6, 2025

Hi otmaneoirda 

You can pass your expansion e.g. A#Root.Base into this api e.g. api.Members.GetMembersUsingFilter(dimPk, memberFilter, dimDisplayOptions) to return a list of members, based on your filter.

You can then create a comma separated list of these members and pass them into your ADC e.g. A#Root.Base.Remove(A1,A2,A3, etc), similar to MarcusH  suggestion

Hope this helps

Sam

R
rhankey
Newcomer
March 6, 2025

You can pre-filter or post-filter data.

Here is an example of post-filtering, where the filter is applied to the destination buffer just before it is written back to the cube:

api.Data.Calculate("S#POV = RemoveZeros(S#SrcScenario)","A#Root.Base.Remove(A1,A2,A3))")

Here is an example of pre-filtering, where the filter can be applied to any one or all of the source buffers as desired:

api.Data.Calculate("S#POV = FilterMembers(RemoveZeros(S#SrcScenario),[A#Root.Base.Remove(A1,A2,A3)]))")

It usually more performance efficient to pre-filter the data, otherwise you are having OS calculate way more rows than are needed, only to toss them out after the fact.

Of course, with more advanced data buffer handlers, it is possible to filter or manipulate any data buffer way beyond the above two simple filtering methods.

M
markolainio
Newcomer
March 28, 2025

One option is also you can use the RemoveMembers() filter in your api.Data.Calculate:

api.Data.Calculate("V#YTD:S#ACTUAL = RemoveZeros(RemoveMembers(V#YTD:S#ACTUAL_TEST, A#Accounts1ToBeRemoved.Base , A#Accounts2ToBeRemoved.Base))", "A#Root.Base")

Terms & ConditionsAccessibility statement