MDX 데이터 조작 선택MDX Data Manipulation - SELECT

적용 대상:예SQL Server Analysis Services아니요Azure Analysis ServicesAPPLIES TO:yesSQL Server Analysis ServicesnoAzure Analysis Services

지정한 큐브에서 데이터를 검색합니다.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> 값에 큐브의 특성이 생략되어 있으면 특성의 기본 멤버가 slicer 축에 암시적으로 추가됩니다.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.

하위 SELECT 문에 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. 즉, 상위 10개 매출 정보(사람/제품/지역)를 쿼리한 후 상위 10개의 매출 합계 값 대신 쿼리한 모든 멤버의 매출 순 합계를 반환 결과로 얻을 수 있습니다.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.

계산된 멤버에 포함 될 수 <t query axis > 때마다 연결 문자열 매개 변수를 사용 하 여 해당 연결을 연 하위 쿼리 = 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:

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

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. 예에서 [Mountain] 그룹의 Top10SellingProducts를 [Product].[Product Line] 특성별로 필터링합니다.In the example we are filtering Top10SellingProducts by [Product].[Product Line] attribute for those in [Mountain] group. 두 특성(slicer 및 축)이 동일한 차원인 [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:

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 목록에 대한 7개의 새 항목이 목록 맨 위로 이동했습니다.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. 이전 결과 집합에서는 이러한 3개의 값이 섞였습니다.In the previous result set those three values were interspersed

Top10SellingProducts 집합이 쿼리의 조각화 조건을 충족하도록 계산되기 때문에 이를 전체 Autoexists라고 합니다.This is called Deep Autoexists, because the Top10SellingProducts set is evaluated to meet the slicing conditions of the query. 전체 Autoexists는 slicer 식, 축의 하위 선택 식 등을 적용한 후 가능한 가장 범위가 큰 공간에 맞게 모든 식이 계산됨을 의미합니다.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.

그러나 다음 예와 같이 Top10SellingProducts를 Preferred10Products와 동등하게 분석하기를 원할 수 있습니다.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:

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]

다음 결과 집합은 이제 AUTOEXIST의 부분 동작을 보여 줍니다.The following result set now shows the shallow behavior of Autoexists.

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 동작은 AUTOEXISTS를 사용 하 여 수정할 수 있습니다 = [1 | 2 | 3]; 연결 문자열 매개 변수 참조 지원 XMLA 속성 ( XMLA ) ConnectionString 매개 변수 사용 합니다.Autoexists 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] 멤버에 포함 된 2003 년의 첫 8 개월 동안 집계는 Date 차원에서의 Adventure Works 큐브.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]  

이해 하기 보이지 않는 다음 예제에서는 [Adventure Works]의 쿼리를 있는 제품 범주 데이터 열은 열이 고 대리점 업종이 행 테이블에서 [Reseller Sales Amount] 수치를 가져올 수는 있습니다.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:

All ProductsAll Products AccessoriesAccessories BikesBikes ClothingClothing ComponentsComponents
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
WarehouseWarehouse $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

Accessories 및 Clothing 제품과 Value Added Reseller 및 Warehouse 대리점에 대한 데이터만 포함된 테이블을 만들고 전체 합계를 유지하려면 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:

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
WarehouseWarehouse $38,726,913.48$38,726,913.48 $331,169.64$331,169.64 $932,521.23$932,521.23

열에는 보이는 값 합계만 포함하고 행 합계에는 모든 [Category]의 순 합계를 표시하는 테이블을 만들려면 다음과 같은 쿼리를 실행해야 합니다.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:

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 $79,980,114.38$79,980,114.38 $470,482.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)
쿼리 및 Slicer 축 (으로 쿼리 제한 Mdx)Restricting the Query with Query and Slicer Axes (MDX)