Removing members from a hierarchy using MDX

Question

In my SSAS solution, there is a 'Location' hierarchy consisting of the levels Company -> Region -> Area

-All
  -Company1
    -Region11
    -Region12
  -Company2
    -Region21
    -Region22
  -Company3
    -Region31
    -Region32

I have report filter that uses this dimension hierarchy as data source. I have a requirement to exclude a particular Company, say "Company2", from the report filter.

I am using the EXCEPT and DESCENDANTS MDX functions to exclude this particular member and its children from the set. However, I lose the ALL level (highest level in the hierarchy) and the returned set looks like this:

  -Company1
    -Region11
    -Region12
  -Company3
    -Region31
    -Region32

How do I retain the ALL member? I need Company1 and Company3 to be grouped under a higher level.

Answer 1

The Except function should work. what is the MDX you're using ?

Answer 2

Maybe something to do with the use of DESCENDANTS. If I remember correctly All is effectively a level as well as a member - sort of a single member level - so it may well make sense that if you are using DESCENDANTS the higher All level is being excluded.

On Adventure works if I exclude the single member set Australia then it is excluded from the following result set without losing the All Customers member:

SELECT 
    {} ON 0,
    EXCEPT(
        [Customer].[Customer Geography].ALLMEMBERS,
        {[Customer].[Customer Geography].[Country].&[Australia]}
    ) ON 1
FROM [Adventure Works]

Following up on the comment by ic3

descendants( except() ) instead of except ( descendants () )

The following example retains the member All Geographies but Canada and it's descendants are excluded:

SELECT 
    {} on 0,
    EXCEPT(
        DESCENDANTS(
            [Geography].[Geography].[All Geographies],
            [Geography].[Geography].[State-Province],
            SELF_AND_BEFORE
        ),
        DESCENDANTS(
            [Geography].[Geography].[Country].[Canada],
            [Geography].[Geography].[State-Province],
            SELF_AND_BEFORE
        )
    )
ON 1
FROM [Adventure Works]