Microsoft KB Archive/942839

= 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

Article Last Modified on 10/23/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services, when used with:
 * Microsoft SQL Server 2005 Service Pack 2

-



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.



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.



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 &quot;Steps to reproduce the problem&quot; 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]



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



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]

</ol>

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]

<div class="references_section">