EXISTING evaluates a set in the current context. It’s like (hierarchy, measure), except that we don’t have to specify the hierarchy.
In this MDX, measure X is the Sales Amount for the current country:
with member [Measures].[x] as
( [Sales Territory].[Sales Territory Country].CurrentMember,
[Measures].[Internet Sales Amount]
)
select [Product].[Category].[All].Children on 1,
[Measures].[x] on 0
from [Adventure Works]
where [Sales Territory].[Sales Territory Country].[United Kingdom];
Output:
Using EXISTING, it’s like this:
with member [Measures].[x] as
sum(existing [Measures].[Internet Sales Amount])
select [Product].[Category].[All].Children on 1,
[Measures].[x] on 0
from [Adventure Works]
where [Sales Territory].[Sales Territory Country].[United Kingdom];
Output:
Measure X is the Sales Amount in the current context i.e. current country.
Simple, isn’t it?
Data Warehousing and Data Science 
Does it work with a “subquery” instead of “Where” clause??
Thanks!
Comment by Diana Luz — 20 July 2010 @ 4:11 am |
Yes it does Diana:
with member [Measures].[x] as
sum
( existing
[Measures].[Internet Sales Amount]
)
select {[Product].[Category].[All].Children} on 1,
{[Measures].[x]} on 0
from
( select {[Sales Territory].[Sales Territory Country].[United Kingdom]} on 1,
{[Measures].[Internet Sales Amount]} on 0
from [Adventure Works]
);
Output is the same as before:
Accessories 76630.04
Bikes 3282842.6609
Clothing 32239.51
Components (null)
Comment by Vincent Rainardi — 20 July 2010 @ 9:44 am |
Excelent!! Thank you so much! =)
Comment by Diana Luz — 21 July 2010 @ 4:33 am |
Even with out existing keyword the result is same, what does existing do here?
Comment by Dilli — 23 October 2013 @ 12:31 pm |