Using a Where Clause for the Member Filter Tree - Is this Possible?
Is it possible to use the where clause for the member expansion ".Tree", specifically filtering on the AccountDim. There is a top-level reporting cube and a few merged cubes sitting beneath (using entity extensibility). There are also a number of extensible Account Dimensions utilized...
i.e., Level 1 (Highest - Forecast Accounts)
Level 2 (Consolidated Accounts)
Level 3 - #1 (Lowest - linked to lower-level reporting subsidiary cubes using base GL Accounts - the account dimension names for these cubes always include 'Base')
Level 3 - #2 (base GL Accounts)
Level 3 - #3 (base GL Accounts)
Level 3 - #4 (base GL Accounts)
I am looking to build a report using the ".tree" member filter to allow for expandable/collapsible rows (accounts as rows), however I do not want to include the Level 3 Account Dimensions. When building out a consolidating report (pulling from the top-level reporting cube) with the entities running across the columns I am having trouble filtering out the Level 3 Account Dimensions. When I try A#Revenue.Tree.Where(MemberDim DOESNOTCONTAIN 'Base') or something of the similar the report is only displaying the Revenue account.
I have tried using the options function such as, A#Revenue.tree.options(Cube=[Top Level Cube], ScenarioType=Actual, MergeMembersFromReferencedCubes=FALSE), however when the merged entities are included in the columns the report pulls in the level 3 account dimensions..
Ultimately the goal is to create a report that allows for expandable/collapsible account rows but filters out the level 3 account dimensions. Any ideas how to achieve this?