MDX: EXISTING

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?

Advertisements