Select the product you need help with

An MDX query returns additional cell data when you use the IsEmpty function on the query axis in Analysis Services with SQL Server 2005 Service Pack 2

Article ID: 942839 - View products that this article applies to.

SYMPTOMS

Consider the following scenario. In a Multidimensional Expressions (MDX) query, you specify a hierarchy of a dimension on the slicer axis. You also specify a different hierarchy of the dimension on a query axis. Additionally, you use the IsEmpty function on the query axis. Then, you run the MDX query in Microsoft SQL Server 2005 Analysis Services with Microsoft SQL Server 2005 Service Pack 2 (SP2). In this scenario, the MDX query returns additional cell data.

This problem does not occur in versions that are earlier than SQL Server 2005 SP2.

Back to the top | Give Feedback

CAUSE

This problem occurs because the slicer axis uses the members from the query axis instead of the members that you specify on the slicer axis.

Back to the top | Give Feedback

WORKAROUND

To work around this problem, use the NonEmpty function instead of the IsEmpty function.

For example, update the MDX query in step 5 from the "Steps to reproduce the problem" section as follows.

Select
  [Measures].[Internet Sales Amount] On 0,
  NonEmpty([Product].[Product Categories].[Product].Members, [Measures].[Internet Sales Amount]) On 1
From [Adventure Works]
Where [Product].[Product Model Lines].[Product Line].&[R]

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

MORE INFORMATION

Steps to reproduce the problem

In Business Intelligence Development Studio, open the AdventureWorks DW Standard Edition sample project.
Deploy the sample project to an instance of Analysis Services with SQL Server 2005 SP2.
Open SQL Server Management Studio, and then connect to the instance of Analysis Services with SQL Server 2005 SP2.
Open a new query window.

Run the following MDX query.

Select
  [Measures].[Internet Sales Amount] On 0,
  Filter([Product].[Product Categories].[Product].Members, Not IsEmpty([Measures].[Internet Sales Amount])) On 1
From [Adventure Works]
Where [Product].[Product Model Lines].[Product Line].&[R]

The MDX query returns 159 cells. The following member is not used to restrict the cells on the query axis:

[Product].[Product Model Lines].[Product Line].&[R]

Note When you deploy the sample project to an instance of Analysis Services or of Analysis Services with SQL Server 2005 SP1 and then you run the MDX query, the MDX query returns 68 cells. The following member is used to restrict the cells on the query axis:

[Product].[Product Model Lines].[Product Line].&[R]

Back to the top | Give Feedback