CODEWITHSUNDEEP
ssasmdx
David542
David542

Reputation: 110257

Usage of SELECT NONEMPTY

I have noticed that the default for grabbing a column/row in MDX (generated from Excel) is:

SELECT
    NON EMPTY { 
        ...
    } ON COLUMNS, 
    NON EMPTY { 
        ...
    } ON ROWS 
FROM [Model]

Why is this the default behavior? When would empty columns or rows come up in actual usage?

Also, it says NONEMPTY is the correct name in the docs, but when I use that I get a syntax error. What's the difference between NONEMPTY and NON EMPTY -- is one deprecated?

Upvotes: 0

Views: 223

Answers (1)

vldmrrdjcc
vldmrrdjcc

Reputation: 2112

As you found in documentation Nonempty is the function so it will need brackets around set expression: NONEMPTY(set_expression). NON EMPTY is the keyword.

The big difference between the NON EMPTY keyword and the NONEMPTY function is when the evaluation occurs in the MDX. The NON EMPTY keyword is the last thing that is evaluated, in other words after all axes have been evaluated then the NON EMPTY keyword is executed to remove any empty space from the final result set. The NONEMPTY function is evaluated when the specific axis is evaluated.

Upvotes: 1

Related Questions