Create a Named Set with all but one Member in MDX – SSAS

I was trying to create a Named set in SSAS 2008 using the MDX calculations. I wanted to include all of the members of a hierarchy except one of them. I started with trying to use the filter expression and this worked but was very cumbersome to get the syntax right. I finally switched over to the MDX function “Except”.

To create a Named Set in Analysis Services Click on the Calculation tab and then the Named Set button across the top.

clip_image002

The Code in the Named Set was:

Except

(

{[Referral Type].[Referral Type Desc].Children},

{[Referral Type].[Referral Type Desc].&[ER TO IP]}

)

Here is an image of the Hierarchy. The arrow points to the member I want to remove from the Named Set.

clip_image004

To Test it I dragged over the hierarchy into the cube browser and dragged the named set up top. You can see the ER to IP member is missing.

clip_image006

About these ads
This entry was posted in mdx, SSAS, Syndication and tagged , . Bookmark the permalink.

One Response to Create a Named Set with all but one Member in MDX – SSAS

  1. Kenny says:

    Thanks for the helpful tip Mike. MDX is a very powerful language but it is easy to get confused on which approach to take.

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