MDX 数据操作 - SELECTMDX Data Manipulation - SELECT

从指定多维数据集中检索数据。Retrieves data from a specified cube.

语法Syntax

  
[ WITH <SELECT WITH clause>   
   [ , <SELECT WITH clause>...n ]   
]   
SELECT   
     [ *   
    | ( <SELECT query axis clause>   
                  [ , <SELECT query axis clause>,...n ]   
            )   
            ]  
FROM   
   <SELECT subcube clause>   
      [ <SELECT slicer axis clause> ]  
      [ <SELECT cell property list clause> ]  
  
<SELECT WITH clause> ::=  
     ( CELL CALCULATION <CREATE CELL CALCULATION body clause> )   
   | ( [ CALCULATED ] MEMBER <CREATE MEMBER body clause>)   
   | ( SET <CREATE SET body clause>)  
   | ( MEASURE = <measure body clause> )  
  
<SELECT query axis clause> ::=  
   [ NON EMPTY ] Set_Expression  
   [ <SELECT dimension property list clause> ]   
      ON   
            Integer_Expression   
       | AXIS(Integer)   
       | COLUMNS   
       | ROWS   
       | PAGES   
       | SECTIONS   
       | CHAPTERS   
  
<SELECT subcube clause> ::=  
      Cube_Name   
   | [NON VISUAL] (SELECT   
                  [ *   
       | ( <SELECT query axis clause> [ ,   
           <SELECT query axis clause>,...n ] )   
         ]   
            FROM   
         <SELECT subcube clause>   
         <SELECT slicer axis clause> )  
  
<SELECT slicer axis clause> ::=   
      WHERE Tuple_Expression  
  
<SELECT cell property list clause> ::=   
   [ CELL ] PROPERTIES CellProperty_Name   
      [ , CellProperty_Name,...n ]  
  
<SELECT dimension property list clause> ::=  
   [DIMENSION] PROPERTIES   
      (DimensionProperty_Name   
         [,DimensionProperty_Name,...n ] )   
    | (LevelProperty_Name   
         [, LevelProperty_Name,...n ] )   
    | (MemberProperty_Name   
         [, MemberProperty_Name,...n ] )  

参数Arguments

Set_ExpressionSet_Expression
返回集的有效多维表达式 (MDX)。A valid Multidimensional Expressions (MDX) expression that returns a set.

整数Integer
一个介于 0 和 127 之间的整数。An integer between 0 and 127.

Cube_NameCube_Name
提供多维数据集名称的有效字符串。A valid string that provides a cube name.

Tuple_ExpressionTuple_Expression
返回元组的有效多维表达式 (MDX)。A valid Multidimensional Expressions (MDX) expression that returns a tuple.

CellProperty_NameCellProperty_Name
表示单元属性的有效字符串。A valid string that represents a cell property.

DimensionProperty_NameDimensionProperty_Name
表示维度属性的有效字符串。A valid string that represents a dimension property.

LevelProperty_NameLevelProperty_Name
表示级别属性的有效字符串。A valid string that represents a level property.

MemberProperty_NameMemberProperty_Name
表示成员属性的有效字符串。A valid string that represents a member property.

备注Remarks

<SELECT slicer axis clause> 表达式必须包含维度和层次结构中的成员,而不是包含指定的 <SELECT query axis clause> 表达式中所引用的成员。The <SELECT slicer axis clause> expression must contain members in dimensions and hierarchies other than those referenced in the specified <SELECT query axis clause> expressions.

如果指定的 <SELECT query axis clause> 表达式和 <SELECT slicer axis clause> 值中省略了多维数据集中的某个属性,则该属性的默认成员将隐式添加到切片器轴中。If an attribute in the cube is omitted from the specified <SELECT query axis clause> expressions and the <SELECT slicer axis clause> value, the attribute's default member is implicitly added to the slicer axis.

使用 subselect 语句中的 NON VISUAL 选项,可以筛选出成员,同时保留实际总数,而不是筛选出的总数。The NON VISUAL option in the subselect statement enables you to filter out members while keeping the true totals instead of filtered totals. 这样,您可以查询前十位的销售(人员/产品/地区),并获取所有查询成员的实际销售总数,而不是返回的前十位的销售总数。This enables you to query for the top ten sales (persons/products/regions) and obtain the true total of sales for all queried members, instead of the total value of sales for the top ten returned. 有关详细信息,请参阅下面的示例。See the examples below for more information.

<SELECT query axis clause>使用连接字符串参数子查询 = 1时,可以将计算成员包含在中,请参阅(XMLA)支持的 xmla 属性ConnectionString 参数用法。Calculated members can be included in <SELECT query axis clause> whenever the connection was opened using the connection string parameter subqueries=1; see Supported XMLA Properties (XMLA) and ConnectionString for parameter usage. 下面是有关嵌套 select 语句中的计算成员的示例。An example is provided on calculated members in subselects.

AutoexistsAutoexists

在 SELECT 语句中使用维度的两个或更多属性时,Analysis Services 会计算这些属性的表达式,以确保这些属性的成员得到适当限制,使它们满足所有其他属性的条件。When two or more attributes of the dimension are used in a SELECT statement, Analysis Services evaluates the attributes' expressions to make sure that the members of those attributes are properly confined to meet the criteria of all other attributes. 例如,假定您在处理来自 Geography 维度的属性。For example, suppose you are working with attributes from the Geography dimension. 如果您有一个表达式返回 City 属性中的所有成员,另一个表达式将 Country 属性中的成员限定为欧洲的所有国家/地区,则这将导致 City 成员仅限于属于欧洲国家的城市。If you have one expression that returns all members from the City attribute, and another expression that confines members from the Country attribute to all countries in Europe, then this will result in the City members being confined to only those cities that belong to countries in Europe. Analysis Services 的这一特性称为 Autoexists,并且仅适用于同一维度内的属性。This characteristic of Analysis Services is called Autoexists and applies only to attributes in the same dimension. Autoexists 仅适用于同一维度中的属性,因为它试图阻止在一个属性表达式中排除的维度记录被包括在其他属性表达式中。Autoexists only applies to attributes from the same dimension because it tries to prevent the dimension records excluded in one attribute expression from being included by the other attribute expressions. 也可以将 Autoexists 理解为不同的属性表达式产生的维度记录的交集。Autoexists can also be understood as the resulting intersection of the different attributes expressions over the dimension records. 请参阅下面的以下示例:See the following examples below:

//Obtain the Top 10 best reseller selling products by Name

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

获得的结果集如下:The obtained result set is:

模型名称 + 度量值Model Name + Measures Reseller Sales AmountReseller Sales Amount Discount AmountDiscount Amount PCT DiscountPCT Discount
Mountain-200Mountain-200 $14,356,699.36$14,356,699.36 $19,012.71$19,012.71 0.13%0.13%
Road-250Road-250 $9,377,457.68$9,377,457.68 $4,032.47$4,032.47 0.04%0.04%
Mountain-100Mountain-100 $8,568,958.27$8,568,958.27 $139,393.27$139,393.27 1.63%1.63%
Road-650Road-650 $7,442,141.81$7,442,141.81 $39,698.30$39,698.30 0.53%0.53%
Touring-1000Touring-1000 $6,723,794.29$6,723,794.29 $166,144.17$166,144.17 2.47%2.47%
Road-550-WRoad-550-W $3,668,383.88$3,668,383.88 $1,901.97$1,901.97 0.05%0.05%
Road-350-WRoad-350-W $3,665,932.31$3,665,932.31 $20,946.50$20,946.50 0.57%0.57%
HL Mountain FrameHL Mountain Frame $3,365,069.27$3,365,069.27 $174.11$174.11 0.01%0.01%
Road-150Road-150 $2,363,805.16$2,363,805.16 $0.00$0.00 0.00%0.00%
Touring-3000Touring-3000 $2,046,508.26$2,046,508.26 $79,582.15$79,582.15 3.89%3.89%

获得的产品集似乎与 Preferred10Products 相同;因此,请验证 Preferred10Products 集:The obtained set of products seems to be the same as Preferred10Products; so, verifying the Preferred10Products set:

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Preferred10Products on 1

from [Adventure Works]

根据以下结果,两个产品集(Top10SellingProducts 和 Preferred10Products)是相同的As per the following results, both sets (Top10SellingProducts, Preferred10Products) are the same

模型名称 + 度量值Model Name + Measures Reseller Sales AmountReseller Sales Amount Discount AmountDiscount Amount PCT DiscountPCT Discount
Mountain-200Mountain-200 $14,356,699.36$14,356,699.36 $19,012.71$19,012.71 0.13%0.13%
Road-250Road-250 $9,377,457.68$9,377,457.68 $4,032.47$4,032.47 0.04%0.04%
Mountain-100Mountain-100 $8,568,958.27$8,568,958.27 $139,393.27$139,393.27 1.63%1.63%
Road-650Road-650 $7,442,141.81$7,442,141.81 $39,698.30$39,698.30 0.53%0.53%
Touring-1000Touring-1000 $6,723,794.29$6,723,794.29 $166,144.17$166,144.17 2.47%2.47%
Road-550-WRoad-550-W $3,668,383.88$3,668,383.88 $1,901.97$1,901.97 0.05%0.05%
Road-350-WRoad-350-W $3,665,932.31$3,665,932.31 $20,946.50$20,946.50 0.57%0.57%
HL Mountain FrameHL Mountain Frame $3,365,069.27$3,365,069.27 $174.11$174.11 0.01%0.01%
Road-150Road-150 $2,363,805.16$2,363,805.16 $0.00$0.00 0.00%0.00%
Touring-3000Touring-3000 $2,046,508.26$2,046,508.26 $79,582.15$79,582.15 3.89%3.89%

在上面的示例中,我们创建了两个集:一个作为计算表达式,另一个作为常量表达式。In the previous examples we created two sets: one as a calculated expression and the other as a constant expression. 这些示例阐释了 Autoexists 的不同风格。These examples illustrate the different flavors of Autoexists.

可以对表达式进行 Autoexists 深度或浅表应用。Autoexists can be applied deep or shallow to the expressions. 默认设置为深度应用。The default setting is deep. 下面的示例将阐释深度 Autoexists 的概念。The following example will illustrate the concept of deep Autoexists. 在该示例中,我们将依据 [Product].[Product Line] 属性对 [Mountain] 组中的产品筛选 Top10SellingProducts。In the example we are filtering Top10SellingProducts by [Product].[Product Line] attribute for those in [Mountain] group. 请注意两个属性(slicer 和 axis)均属于同一维度 [Product]。Note that both attributes (slicer and axis) belong to the same dimension, [Product].

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

// Preferred10Products set removed for clarity

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

产生以下结果集:Produces the following result set:

模型名称 + 度量值Model Name + Measures Reseller Sales AmountReseller Sales Amount Discount AmountDiscount Amount PCT DiscountPCT Discount
Mountain-200Mountain-200 $14,356,699.36$14,356,699.36 $19,012.71$19,012.71 0.13%0.13%
Mountain-100Mountain-100 $8,568,958.27$8,568,958.27 $139,393.27$139,393.27 1.63%1.63%
HL Mountain FrameHL Mountain Frame $3,365,069.27$3,365,069.27 $174.11$174.11 0.01%0.01%
Mountain-300Mountain-300 $1,907,249.38$1,907,249.38 $876.95$876.95 0.05%0.05%
Mountain-500Mountain-500 $1,067,327.31$1,067,327.31 $17,266.09$17,266.09 1.62%1.62%
Mountain-400-WMountain-400-W $592,450.05$592,450.05 $303.49$303.49 0.05%0.05%
LL Mountain FrameLL Mountain Frame $521,864.42$521,864.42 $252.41$252.41 0.05%0.05%
ML Mountain Frame-WML Mountain Frame-W $482,953.16$482,953.16 $206.95$206.95 0.04%0.04%
ML Mountain FrameML Mountain Frame $343,785.29$343,785.29 $161.82$161.82 0.05%0.05%
Women's Mountain ShortsWomen's Mountain Shorts $260,304.09$260,304.09 $6,675.56$6,675.56 2.56%2.56%

在前面的结果集中,我们看到 Top10SellingProducts 列表中有七种新产品,并且 Mountain-200、Mountain-100 和 HL Mountain Frame 移到了列表顶部。In the previous result set we have seven newcomers to the list of Top10SellingProducts and Mountain-200, Mountain-100 and HL Mountain Frame have moved to the top of the list. 在前面的结果集中,这三个值是混杂的。In the previous result set those three values were interspersed

这称为深度 Autoexists,因为对 Top10SellingProducts 集进行计算以符合查询的切片条件。This is called Deep Autoexists, because the Top10SellingProducts set is evaluated to meet the slicing conditions of the query. 深度 Autoexists 意味着将对所有表达式进行计算,以便在应用了切片器表达式、轴中的嵌套 select 表达式等后到达可能的最深空间。Deep Autoexists means that all expressions will be evaluated to meet the deepest possible space after applying the slicer expressions, the sub select expressions in the axis, and so on.

不过,有人可能希望能够对与 Preferred10Products 等效的 Top10SellingProducts 进行分析,如下面的示例所示:However, one might want to be able to do the analysis over the Top10SellingProducts as equivalent to Preferred10Products, as in the following example:

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Preferred10Products on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

产生以下结果集:Produces the following result set:

模型名称 + 度量值Model Name + Measures Reseller Sales AmountReseller Sales Amount Discount AmountDiscount Amount PCT DiscountPCT Discount
Mountain-200Mountain-200 $14,356,699.36$14,356,699.36 $19,012.71$19,012.71 0.13%0.13%
Mountain-100Mountain-100 $8,568,958.27$8,568,958.27 $139,393.27$139,393.27 1.63%1.63%
HL Mountain FrameHL Mountain Frame $3,365,069.27$3,365,069.27 $174.11$174.11 0.01%0.01%

在上面的示例中,与预期的一样,切片生成的结果仅包含 Preferred10Products 中属于 [Product].[Product Line] 中的 [Mountain] 组的那些产品,因为 Preferred10Products 是一个常量表达式。In the above results, the slicing gives a result that contains only those products from Preferred10Products that are part of the [Mountain] group in [Product].[Product Line]; as expected, because Preferred10Products is a constant expression.

此结果集还可以理解为浅表 Autoexists。This result set is also understood as shallow Autoexists. 这是因为该表达式是在切片子句之前计算的。This is because the expression is evaluated before the slicing clause. 在上面的示例中,出于演示目的以介绍这一概念,该表达式是一个常量表达式。In the previous example, the expression was a constant expression for illustration purposes in order to introduce the concept.

可以使用 Autoexists 连接字符串属性在会话级修改 Autoexists 的行为。Autoexists behavior can be modified at the session level using the Autoexists connection string property. 下面的示例先打开一个新会话,然后将 Autoexists=3 属性添加到连接字符串。The following example begins by opening a new session and adding the Autoexists=3 property to the connection string. 必须打开新连接才能执行该示例。You must open a new connection in order to do the example. 在与 Autoexist 设置建立连接后,设置将一直有效,直到该连接结束。Once the connection is established with the Autoexist setting it will remain in effect until that connection is finished.

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

//Preferred10Products set removed for clarity

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

下面的结果集现在显示 Autoexists 的浅表行为。The following result set now shows the shallow behavior of Autoexists.

模型名称 + 度量值Model Name + Measures Reseller Sales AmountReseller Sales Amount Discount AmountDiscount Amount PCT DiscountPCT Discount
Mountain-200Mountain-200 $14,356,699.36$14,356,699.36 $19,012.71$19,012.71 0.13%0.13%
Mountain-100Mountain-100 $8,568,958.27$8,568,958.27 $139,393.27$139,393.27 1.63%1.63%
HL Mountain FrameHL Mountain Frame $3,365,069.27$3,365,069.27 $174.11$174.11 0.01%0.01%

使用连接字符串中的 AUTOEXISTS = [1 | 2 | 3] 参数可以修改 Autoexists 行为; (xmla) 和参数用法,请参阅支持的 xmla 属性 ConnectionStringAutoexists behavior can be modified by using the AUTOEXISTS=[1|2|3] parameter in the connection string; see Supported XMLA Properties (XMLA) and ConnectionString for parameter usage.

示例Examples

下面的示例返回 Measures.[Order Quantity] Date艾德工作 多维数据集中包含在维度中的前八个月的日历年2003的成员的总和。The following example returns the sum of the Measures.[Order Quantity] member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.

WITH MEMBER [Date].[Calendar].[First8Months2003] AS  
    Aggregate(  
        PeriodsToDate(  
            [Date].[Calendar].[Calendar Year],   
            [Date].[Calendar].[Month].[August 2003]  
        )  
    )  
SELECT   
    [Date].[Calendar].[First8Months2003] ON COLUMNS,  
    [Product].[Category].Children ON ROWS  
FROM  
    [Adventure Works]  
WHERE  
    [Measures].[Order Quantity]  

若要理解 非视觉对象, 以下示例为 [艾德作品] 的查询,以获取表中产品类别为列,分销商业务类型为行的表中的 "分销商销售额" 数字。To understand NON VISUAL, the following example is a query of [Adventure Works] to obtain [Reseller Sales Amount] figures in a table where the product categories are the columns and the reseller business types are the rows. 请注意,会给出产品和分销商的总数。Note that totals are given for both products and resellers.

以下 SELECT 语句:The following SELECT statement:

select [Category].members on 0,

[Business Type].members on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

产生以下结果:Produces the following results:

业务类型 + 类别Business Type + Category All ProductsAll Products AccessoriesAccessories BikesBikes 服装Clothing 部件Components
All ResellersAll Resellers $80,450,596.98$80,450,596.98 $571,297.93$571,297.93 $66,302,381.56$66,302,381.56 $1,777,840.84$1,777,840.84 $11,799,076.66$11,799,076.66
Specialty Bike ShopSpecialty Bike Shop $6,756,166.18$6,756,166.18 $65,125.48$65,125.48 $6,080,117.73$6,080,117.73 $252,933.91$252,933.91 $357,989.07$357,989.07
Value Added ResellerValue Added Reseller $34,967,517.33$34,967,517.33 $175,002.81$175,002.81 $30,892,354.33$30,892,354.33 $592,385.71$592,385.71 $3,307,774.48$3,307,774.48
仓库Warehouse $38,726,913.48$38,726,913.48 $331,169.64$331,169.64 $29,329,909.50$29,329,909.50 $932,521.23$932,521.23 $8,133,313.11$8,133,313.11

要生成一个仅具有“附件”和“服装”产品、“增值分销商”和“仓库分销商”数据的表,但仍保留整体总数,则使用 NON VISUAL 编写如下:To produce a table with data only for theAccessories and Clothing products, the Value Added Reseller and Warehouse resellers, yet keeping the overall totals could be written as follows using NON VISUAL:

select [Category].members on 0,

[Business Type].members on 1

from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0,

{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1

from [Adventure Works])

where [Measures].[Reseller Sales Amount]

产生以下结果:Produces the following results:

业务类型 + 类别Business Type + Category All ProductsAll Products AccessoriesAccessories ClothingClothing
All ResellersAll Resellers $80,450,596.98$80,450,596.98 $571,297.93$571,297.93 $1,777,840.84$1,777,840.84
Value Added ResellerValue Added Reseller $34,967,517.33$34,967,517.33 $175,002.81$175,002.81 $592,385.71$592,385.71
仓库Warehouse $38,726,913.48$38,726,913.48 $331,169.64$331,169.64 $932,521.23$932,521.23

要生成一个表,其中列是进行直观加和,对于行总数则是所有 [类别] 的实际总和,应发出以下查询:To produce a table that visually totals the columns but for row totals brings the true total of all [Category], the following query should be issued:

select [Category].members on 0,

[Business Type].members on 1

from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0

from ( Select {[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 0

from [Adventure Works])

)

where [Measures].[Reseller Sales Amount]

请注意,NON VISUAL 仅应用到 [Category]。Note how NON VISUAL is only applied to [Category].

上述查询产生以下结果:The above query produces the following results:

业务类型 + 类别Business Type + Category All ProductsAll Products AccessoriesAccessories ClothingClothing
All ResellersAll Resellers $73,694,430.80$73,694,430.80 $506,172.45$506,172.45 $1,524,906.93$1,524,906.93
Value Added ResellerValue Added Reseller $34,967,517.33$34,967,517.33 $175,002.81$175,002.81 $592,385.71$592,385.71
WarehouseWarehouse $38,726,913.48$38,726,913.48 $331,169.64$331,169.64 $932,521.23$932,521.23

与上述结果比较时,您可以观察到 [All Resellers] 行现在是将 [Value Added Reseller] 和 [Warehouse] 仓库的显示值相加,但 [All Products] 列会显示所有产品的总值,包括那些未显示的值。When compared with the previous results, you can observe that the [All Resellers] row now adds up to the displayed values for [Value Added Reseller] and [Warehouse] but that the [All Products] column shows the total value for all products, including those not displayed.

下面的示例说明如何使用嵌套 select 中的计算成员来筛选值。The following example demonstrates how to use calculated members in subselects to filter on them. 为了能够重现此示例,必须使用连接字符串参数 子查询 = 1建立连接。To be able to reproduce this sample, the connection must be established using the connection string parameter subqueries=1.

select Measures.allmembers on 0

from (

Select { [Measures].[Reseller Sales Amount]

, [Measures].[Reseller Total Product Cost]

, [Measures].[Reseller Gross Profit]

, [Measures].[Reseller Gross Profit Margin]

} on 0

from [Adventure Works]

)

上述查询产生以下结果:The above query produces the following results:

Reseller Sales AmountReseller Sales Amount Reseller Total Product CostReseller Total Product Cost Reseller Gross ProfitReseller Gross Profit Reseller Gross Profit MarginReseller Gross Profit Margin
$80,450,596.98$80,450,596.98 $79980114.38$79,980,114.38 $470482.60$470,482.60 0.58%0.58%

另请参阅See Also

MDX (Analysis Services 中的关键概念) Key Concepts in MDX (Analysis Services)
Mdx 数据操作语句 (MDX) MDX Data Manipulation Statements (MDX)
用查询轴和切片器轴限定查询 (MDX)Restricting the Query with Query and Slicer Axes (MDX)