MDX, EXCEPT function and Filtering Blank Records

Ideally your datamart/data warehouse does not contain null values in your dimension attributes.  This will create some ill feeling in the presentation layer when for example the user gets a blank value in a list of dimension members in a parameter list.
The following MDX demonstrates how to filter members that are blank using the EXCEPT function.


WITH MEMBER Measures.X as 1

SELECT
 Measures.x on COLUMNS,
 EXCEPT(
 [Dimension].[Attribute].Children,
 {[Dimension].[Attribute].&[]}
 ) ON ROWS
 FROM
 [YOURCUBE]

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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