Removing empty rows

We have addressed some specific issues with parameterized MDX queries in the first four recipes. This recipe is written with one specific reporting issue in mind, that is, when a calculated measure is added to the column, NON EMPTY no longer works.

Adding query-scoped calculated measures to the COLUMN axis is one way to place more data points and more headers on the report. However, report writers often find that empty rows start to pop out in the report as soon as the calculated measure is placed on the COLUMN axis.

Let's start with a query to see the problem first.

Getting ready

Let's start from this query with these two filters: the subcategory socks and a date range:

[Product].[Product Categories].[Subcategory].[Socks]   
[Date].[Calendar].[Date].&[20130723]:[Date].[Calendar].[Date].&[20130731] 

The following query is displaying three profits on the columns, as well as the subcategory and the date on the rows.

WITH SET [Profit] AS{ [Measures].[Internet...