Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to Remove SSAS(Cube) Dimension Members Using Except() Function?

, 16 Jun 2013
Rate this:
Please Sign up or sign in to vote.
Removing SSAS(cube) dimension members using Except() function

Introduction

In this tip, we will learn how functions such as Exists(), NonEmpty() and Filter() must use a numeric expression.

MDX functions such as Exists(), NonEmpty() and Filter() and seem to be easy enough to use, but they may be a hurdle for people with T-SQL background. The common theme among them is that they all claim to return dimension members, but they must use a fact measure as the condition, such as the sales amount must be greater than 250000/.

In many situations, we just simply want to leave out a dimension member, such as one of the Sales Product Categories, no matter what the sales amount is.

Wanna try? Then run this simple query, and you will get the Order Quantity for all the Sales Product Categories for each month. (Here I use Adventure Works DB for demo purposes.)

SELECT 	[Date].[Month of Year].Children On Columns,
                     	[Product].[Product Categories].[All].Children On Rows
FROM
[Adventure Works]
WHERE
([Measures].[Order Quantity])

What if I simply just want to leave out the Components in my query? Components are not the “final” products. Clothing, Bikes and Accessories are. So I have quite a good reason to leave it out of the query.

Now run this query with the help of the Except() function.

SELECT 	[Date].[Month of Year].Children On Columns,
                    	Except(
                                  [Product].[Product Categories].[All].Children,
                                  [Product].[Product Categories].[Components]
                                 ) On Rows
FROM
                        [Adventure Works]
WHERE
                        ([Measures].[Order Quantity])

The two sets must be from the same hierarchy and at the same level.

Syntax of the Except() function is:

Except(Set_Expression1, Set_Expression2)

It returns SET 1, minus the SET 2.

If you want to leave out member such as Brakes, the following query will not work, because the two sets in the Except() function is not at the same level.

The following query would not work because both sets are not at the same level.

SELECT 	[Date].[Month of Year].Children ON COLUMNS,
                     	Except(
                                  [Product].[Product Categories].[All].Children,
                                  [Product].[Product Categories].[Subcategory].[Brakes]
                                 ) ON ROWS
FROM
           		[Adventure Works]
WHERE 
           		([Measures].[Order Quantity])

This query should work because both sets are at the subcategory level.

SELECT 	[Date].[Month of Year].Children ON COLUMNS,
                     	Except(
                             	 [Product].[Product Categories].[Subcategory].members,
                                     [Product].[Product Categories].[Subcategory].[Brakes]
                                    ) ON ROWS
FROM
                      [Adventure Works]
WHERE
                      ([Measures].[Order Quantity])

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bhushan W. Juare
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 16 Jun 2013
Article Copyright 2013 by Bhushan W. Juare
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid