FIX: Incorrect result when you run an MDX query that includes an Aggregate function that is used in a calculated member in SSAS 2008 R2 or SSAS 2012

Article translations Article translations
Article ID: 2800284 - View products that this article applies to.
Microsoft distributes Microsoft SQL Server 2008 R2 Service Pack 2 (SP2) fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 2 fix release.
Expand all | Collapse all

On This Page

Symptoms

Assume that you run a Multidimensional Expressions (MDX) query in Microsoft SQL Server 2008 R2 Analysis Services (SSAS 2008) or Microsoft SQL Server 2012 Analysis Services (SSAS 2012) when the following conditions are true:
  • The MDX query has an Aggregate function that is used in a calculated member in a WHERE clause.
  • The Aggregate function use more than one member of the Dim.Hier hierarchy.
  • There is a scope statement that affects a member of the Dim.Hier hierarchy that is not present in the Aggregate function.
  • The Dim dimension is a parent/child dimension.
In this situation, the scope statement changes the result of the Aggregate function. This causes the MDX query to return an incorrect result.

Notes:
  • This issue can be checked by adding the EXISTING keyword to the parameter of the Aggregate function.
  • This form of MDX query is made frequently by Excel when a PivotTable object is converted to formulas (by using the CUBESET function).


Resolution

The issue was first fixed in the following cumulative update of SQL Server.

Cumulative update 8 for SQL Server 2012

Cumulative update 4 for SQL Server 2012 SP1

Cumulative update 5 for SQL Server 2008 R2 SP2

About cumulative updates for SQL Server

Collapse this imageExpand this image
assets folding start collapsed
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Collapse this imageExpand this image
assets folding end collapsed

Status

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

More information

For more information about the Aggregate function (MDX), go to the following MSDN website:
General information about the Aggregate function

Properties

Article ID: 2800284 - Last Review: August 27, 2013 - Revision: 9.0
Applies to
  • Microsoft SQL Server 2008 R2 Service Pack 2
  • Microsoft SQL Server 2008 R2 Analysis Services
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2012 Analysis Services
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Service Pack 1
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
kbqfe kbfix kbsurveynew kbexpertiseadvanced KB2800284

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com