Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Null and the colon operator

with 2 comments

I spotted another minor, but interesting new bit of functionality in AS2005 MDX while reading the new article on time intelligence that Mosha links to here – when using the colon operator to get a range of members, you can now use null on one side to denote either the first member or last member on the same level as the member explicity mentioned. So, for instance:

select [Measures].[Internet Sales Amount] on 0,
null : [Date].[Day of Week].[Day of Week].&[3]
on 1
from
[Adventure Works]

returns the members from Day 1 up to and including Day 3 on the Day of Week attribute; similarly:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Day of Week].[Day of Week].&[3] : null
on 1
from
[Adventure Works]

returns the members from Day 3 up to and including Day 7. This will definitely make many calcs a lot more readable.

 

Written by Chris Webb

June 9, 2005 at 3:21 pm

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  1. Wow, I didn\’t even realize it was new functionality. I implemented it like that because I thought AS2K worked that way. But now I rechecked – in AS2K it was possible to use NULL members on either side of the range, but only if they were obtained through some MDX functions, but not for explicit NULL.

    Mosha

    June 9, 2005 at 5:50 pm

  2. […] for more information on how NULL and the colon operator is used here, see this post. This gives us the set expression:    NONEMPTY( NONEMPTY( […]


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

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers

%d bloggers like this: