Except Function - MDX Analysis Service 2005

SQL Server Analysis Service 2005
Except in MDX

Except (Set1, Set2, ALL)

When we supply two sets this function evaluates first set and removes tuples which are in the second

If we specify ALL then the function will retain duplicates from the first set but duplicates from second set will be removed.


SELECT [Measures].[Reseller Sales Amount] ON 0 ,
[Geography].[Country].MEMBERS ON 1
FROM [Adventure Works];

This will return all country with ALL Member "All Geographies".
Now we want to remove this "All Geographies"

We will use Except here.

SELECT [Measures].[Reseller Sales Amount] ON 0 ,
EXCEPT([Geography].[Country].MEMBERS,[Geography].[Country].[All Geographies]) ON 1
FROM [Adventure Works]

This will remove "All Geographies" and display all countries.

No comments:

Post a Comment

Popular Posts