O'Reilly logo

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition by Sherry Li, Tomislav Piasevoli

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required