Update: Using Filter() in ASO Allocations and Procedural Calcs

I frequently use the Filter() command in my ASO Allocation and Procedural Calc POVs. It's a very powerful and useful tool. Unfortunately it stopped working at some point -- probably in 11.1.2.3.x. Well, I'm very happy to announce that I've tested the command in 11.1.2.4.006 and it is working again.

As a quick refresher, here are couple of reasons you might want to use Filter() in an ASO allocation/procedural calculation POV. Both of these issues can cause your calculation to fail and resort to painful workarounds.

You have shared members in a hierarchy that originally were at an upper level.

This code will give you a set that consists of all Level 0 Markets that excludes any shared members.
Filter(Descendants([Market],Levels([Market],0)), Not Market.CurrentMember.Shared_Flag)

You have some members in a hierarchy that have formulas.

This code will give you a set that consists of all Level 0 Measures that excludes any dynamic members:

Filter([Measures].Levels(0).Members, [Measures].CurrentMember.Member_Type <> 2)

Thanks for the prompt fix, Oracle!


NOTE: Until further notice and unless specified otherwise, all future posts will pertain to tests performed on Essbase 11.1.2.4.006. If there's something you like me to test out on this release, please leave a comment below.

ASO Procedural Calcs -- Handling Shared Members

ASO allocations and procedural calculations do not allow you to include any dynamic members in your POV. If you include a shared member you get the following warning:

WARNING - 1300005 - The argument [POV] ignores the duplicate member [100-20].

If the prototype of the shared member is not level 0, it will fail your calculation with the following error:

ERROR - 1300033 - Upper-level members, for example [East], are not allowed in argument [POV]. Select a level-0 member. 

In order to remove any shared members from your POV, you can sometimes use the Filter() function to remove them as illustrated below. I say sometimes because this works in some versions and not in others. I currently have an open SR with Oracle that they are investigating. Hopefully it will be resolved soon. What would be even better is if there were calculation options allowing you to ignore things like dynamic members.

In this example, I'm taking all level 0 members under the Market dimension. But this might include some upper level members that are shared in alternate hierarchies. One strategy would be to only take the descendants of the original hierarchy. Another would be to filter out all shared members as shown here.

 {Filter(Descendants([Market],Levels([Market],0)), NOT Market.CurrentMember.Shared_Flag)}