An MDX query that contains an Aggregate function returns error for the cell values in SQL Server Analysis Services
This article describes a problem that occurs if the set in the Aggregate
function contains a calculated member.
Original product version: SQL Server
Original KB number: 942981
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 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 a calculated member contains the Aggregate
function, and this function has a set of non-aggregatable members.
For example, consider the MDX query that is mentioned in the Steps to reproduce the problem section. In the [Adventure works DW] sample database, the [Scenario].[Scenario] member is non-aggregatable. The property IsAggregatable
for this dimension attribute is set to False. If you run this MDX query, you will receive the error message that is mentioned in the Symptoms 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, see AdventureWorks sample databases.
Deploy the sample project to an instance of SQL Server 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]
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for