Forum Discussion

otmaneoirda's avatar
otmaneoirda
New Contributor
4 days ago

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

Finance
  • MarcusH's avatar
    MarcusH
    Valued Contributor
    3 days ago

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

  • sameburn's avatar
    sameburn
    Contributor III
    3 days ago

    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

  • rhankey's avatar
    rhankey
    Contributor
    3 days ago

    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.