Data Warehousing and Business Intelligence

25 June 2010

MDX: EXISTING

Filed under: Analysis Services,MDX — Vincent Rainardi @ 2:31 pm
Tags: ,

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

4 Comments »

  1. Does it work with a “subquery” instead of “Where” clause??

    Thanks!

    Comment by Diana Luz — 20 July 2010 @ 4:11 am | Reply

    • 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 | Reply

  2. Excelent!! Thank you so much! =)

    Comment by Diana Luz — 21 July 2010 @ 4:33 am | Reply

  3. Even with out existing keyword the result is same, what does existing do here?

    Comment by Dilli — 23 October 2013 @ 12:31 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: