大网数据广告活动
数据库与应用安全

MDX query contains Aggregate returns error

· 52CE 我爱测

This article describes a problem that occurs if the set in the Aggregate function contains a calculated member.

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

  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, see AdventureWorks sample databases.

  2. Deploy the sample project to an instance of SQL Server 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]
    

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 11/10/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。