Microsoft KB Archive/942981

= An MDX query that contains an Aggregate function returns #Error for the cell values in SQL Server 2005 Analysis Services =

Article ID: 942981

Article Last Modified on 10/29/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



SYMPTOMS
You have a Multidimensional Expressions (MDX) query that uses the Aggregate function. The set that is specified in the Aggregate function contains a calculated member. When you run the MDX query against an instance of Microsoft SQL Server 2005 Analysis Services, the query returns #Error for the cells values. If you click a cell, you receive the following error message in the Cell Properties dialog box:

A set has been encountered that cannot contain calculated members

Note You receive the error message on the Value column of the VALUE property and of the FORMATTED_VALUE property.



CAUSE
This problem occurs because the set in the Aggregate function cannot contain a calculated member.



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 SQL Server Business Intelligence Development Studio, open the Adventure Works DW Enterprise Edition sample project.

Note The Adventure Works DW Enterprise Edition sample project is included in the Analysis Services database project. To download the Analysis Services database project, visit the following Microsoft Web site:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

 Deploy the sample project to an instance of SQL Server 2005 Analysis Services. Open SQL Server Management Studio, and then connect to the instance of Analysis Services. Click New Query.  In the query window, run the following MDX query: WITH MEMBER [Scenario].[Scenario].[MyMember] AS AGGREGATE( {[Scenario].[Scenario].&[1], [Scenario].[Scenario].&[2], [Scenario].[Scenario].&[3], [Scenario].[Scenario].[Budget Variance] })

SELECT {[Measures].[Amount]} ON AXIS(0) FROM [Adventure Works] WHERE [Scenario].[Scenario].[MyMember] 

Keywords: kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942981

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.