The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Member expansion on the "Remove" Function Alternative
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
You can remove more than one by separating them with a comma. Eg .Remove(Acc1, Acc2)
4 Replies
You can remove more than one by separating them with a comma. Eg .Remove(Acc1, Acc2)
sameburnOneStream Employee
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
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.
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")
