An MDX query that contains an Aggregate function returns #Error for the cell values in 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 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 "More Information" 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.

Status


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

More Information


Steps to reproduce the problem

  1. 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:
  2. Deploy the sample project to an instance of SQL Server 2005 Analysis Services.
  3. Open SQL Server Management Studio, and then connect to the instance of Analysis Services.
  4. Click New Query.
  5. 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]