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] ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.