With Member [Sales Territory].[Sales Territory].[Europe@] As [Sales Territory].[Sales Territory].[Country].&[Germany]+[Sales Territory].[Sales Territory].[Country].&[United Kingdom]
Select
{
{[Sales Territory].[Sales Territory].[Europe@]},
{[Sales Territory].[Sales Territory].[Group].&[NA]},
{[Sales Territory].[Sales Territory].[Group].&[North America]},
{[Sales Territory].[Sales Territory].[Group].&[Pacific]}
}
 ON COLUMNS
,
{
{[Measures].[Order Count]}
}
 ON ROWS
 From [Adventure Works]

My hierarchy is

All Territories

--Europe

    --France

    --Germany

    -- United Kingdom

-- NA

--North America

--Pacific

I am firing this query to get data except from france under Europe.

The query works fine only that my hierarchy name changes from [Sales Territory].[Sales Territory].[Group].&[Europe]

to [Sales Territory].[Sales Territory].[Europe@].

Can I in some way preserve the hierarchyName

Pls help

Hi,

As you want to eliminate one member from hierarchy without renaming (controlling)

This can be achieved using  SCOPE calculations

Try this, 

SCOPE([Sales Territory].[Sales Territory].[Country].&[France],[Measures].[Order Count]);
This = NULL;
END SCOPE;

Idea is to make the NULL value against France member with Order Count measure so this will automatically eliminates

Caution: If you are looking for ignoring France and it's children from Europe then need to apply DESCENDANTS ON France member

If you want to do a bit of play with logic using EXCEPT, then try this

Member [Sales Territory].[Sales Territory].[Europe@1] As 
Aggregate(
EXCEPT(
DESCENDANTS( [Sales Territory].[Sales Territory].[Group].&[Europe]
, [Sales Territory].[Sales Territory].[Country]
, SELF
)
, [Sales Territory].[Sales Territory].[Country].&[France]
)
)

let me know your results

Hey I am new to mdx. I dont know how to execute a scope statement. These queries are created dynamically at runtime from my application. How should I form/change the query to see how the scope works

Hi

We need to apply this SCOPE at Cube calculations then your application can select Europe directly i.e. you can get only Europe without France values.

Please refer to 

http://technet.microsoft.com/en-us/library/ms145515.aspx