Tuesday, December 15, 2015

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.