Data Warehousing and Business Intelligence

21 June 2010

MDX: Filter

Filed under: Analysis Services,MDX — Vincent Rainardi @ 10:03 pm
Tags: ,

We can filter an axis based on the members, or based on the measure value.

Based on the members: begins with F or after      Based on the measure value: Sales > $40,000

To filter based on members, we can use string comparison:

select non empty
filter
( {[Customer].[City].[All].Children},
[Customer].[City].CurrentMember.Name > “F”
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Or use string functions:

select non empty
filter
( {[Customer].[City].[All].Children},
left([Customer].[City].CurrentMember.Name,1) = “F”
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

To filter based on measure value, we use numeric comparison:

select non empty
filter
( {[Customer].[City].[All].Children},
[Measures].[Internet Sales Amount] > 40000
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Other examples of string functions:

Second letter is A:
mid([Customer].[City].CurrentMember.Name,2,1) = “A”

Ends with A:
right([Customer].[City].CurrentMember.Name,1) = “A”

Begins with O and ends with A:
left([Customer].[City].CurrentMember.Name,1) = “O” and
right([Customer].[City].CurrentMember.Name,1) = “A”

Advertisements

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

%d bloggers like this: