A valid Multidimensional Expressions (MDX) expression that returns a set.

A valid Multidimensional Expressions (MDX) expression that returns a set.

In this example, getting all Countries Reseller Sales Amount for year 2005, in output you can observer I am also getting countries with Reseller Sales Amount value as Null

SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
,{[Geography].[Country].Children} ON ROWS
FROM [Adventure Works]
WHERE
  [Date].[Calendar Year].&[2005];

What changes I need to incorporate to a query so that I will get result something like this:

Here is the query with nonempty, query is returning set of tuples with non null values.

SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
,{NonEmpty([Geography].[Country].Children)} ON ROWS
FROM [Adventure Works]
WHERE
  [Date].[Calendar Year].&[2005];

! Non-empty is a characteristic of the cells references by the tuples, not the tuples themselves.

Non Empty

If we want to use Non Empty to get the same output we need to make below mentioned modifications:

SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
,Non Empty{([Geography].[Country].Children)} ON ROWS
FROM [Adventure Works]
WHERE
  [Date].[Calendar Year].&[2005];

Non Empty operator works on the top level of the query, which means sets defined at axis are generated first and then the tuple having NULL values are removed.

Can be easily proved with below mentioned query:

SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
,{
    Filter
    (
   ([Geography].[City].[City].members
      * 
[Date].[Calendar].[Date].members)
     ,
      [Measures].[Internet Sales Amount] >4000
    )
  } ON ROWS
FROM
[Adventure Works]

Which  means without NonEmpty or Non Empty, 697356 cells are calculated, now lets using Non Empty

Lets try with Non Empty

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,Non Empty {
Filter
(
([Geography].[City].[City].members
*
[Date].[Calendar].[Date].members)
,
[Measures].[Internet Sales Amount] >4000
)
} ON ROWS
FROM
[Adventure Works]

No change in Cell Calculated because internally as explained earlier Non Empty work on Top Level of Query and after getting complete output it shows cells with Non Empty Value.

But If we execute the query with nonempty() we will get the difference, no of cell calculated are only 3211 which mean unlike Non Empty its returning set of tuples that are not empty from the specified set.

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
, {
Filter
(
nonempty([Geography].[City].[City].members
*
[Date].[Calendar].[Date].members)
,
[Measures].[Internet Sales Amount] >4000
)
} ON ROWS
FROM
[Adventure Works]