Using a Where Clause for the Member Filter Tree - Is this Possible?

OSPhilly88
New Contributor II

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?  

 

 

 

 

 

 

 

 

0 REPLIES 0