Manipolazione dei dati MDX - SELECTMDX Data Manipulation - SELECT

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)noDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Recupera dati da un cubo specificato.Retrieves data from a specified cube.

SintassiSyntax


[ 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 ] )  

ArgomentiArguments

Set_ExpressionSet_Expression
Espressione MDX (Multidimensional Expression) valida che restituisce un set.A valid Multidimensional Expressions (MDX) expression that returns a set.

IntegerInteger
Valore integer compreso tra 0 e 127.An integer between 0 and 127.

Cube_NameCube_Name
Stringa valida che specifica il nome di un cubo.A valid string that provides a cube name.

Tuple_ExpressionTuple_Expression
Espressione MDX (Multidimensional Expression) valida che restituisce una tupla.A valid Multidimensional Expressions (MDX) expression that returns a tuple.

CellProperty_NameCellProperty_Name
Stringa valida che rappresenta la proprietà di una cella.A valid string that represents a cell property.

DimensionProperty_NameDimensionProperty_Name
Stringa valida che rappresenta la proprietà di una dimensione.A valid string that represents a dimension property.

LevelProperty_NameLevelProperty_Name
Stringa valida che rappresenta la proprietà di un livello.A valid string that represents a level property.

MemberProperty_NameMemberProperty_Name
Stringa valida che rappresenta la proprietà di un membro.A valid string that represents a member property.

OsservazioniRemarks

L'espressione <SELECT slicer axis clause> deve contenere membri di dimensioni e gerarchie diverse da quelle a cui fanno riferimento le espressioni <SELECT query axis clause> specificate.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.

Se nelle espressioni <SELECT query axis clause> specificate e nel valore <SELECT slicer axis clause> viene omesso un attributo del cubo, il membro predefinito dell'attributo viene aggiunto in modo implicito all'asse di sezionamento.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.

L'opzione NON VISUAL nell'istruzione sub-SELECT consente di filtrare i membri mentre e mantenere i totali veri anziché i totali filtrati.The NON VISUAL option in the subselect statement enables you to filter out members while keeping the true totals instead of filtered totals. In tal modo è possibile eseguire una query per le prime dieci vendite (persone/prodotti/aree) e ottenere il totale vero delle vendite per tutti i membri inclusi nella query, anziché il valore totale delle vendite dei primi dieci restituiti.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. Per ulteriori informazioni, vedere gli esempi seguenti.See the examples below for more information.

I membri calcolati possono essere inclusi in <clausola dell'asse di query SELECT > ogni volta che l'apertura della connessione utilizzando il parametro di stringa di connessione sottoquery = 1; vedere supportate proprietà XMLA ( XMLA ) e ConnectionString per l'utilizzo del parametro.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. Un esempio è fornito sui membri calcolati nelle sub-SELECT.An example is provided on calculated members in subselects.

Auto ExistAutoexists

Quando in un'istruzione SELECT vengono utilizzati due o più attributi della dimensione, Analysis Services valuta le espressioni degli attributi per verificare che i relativi membri siano correttamente limitati per soddisfare i criteri di tutti gli altri attributi.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. Si supponga ad esempio di utilizzare gli attributi della dimensione Geografia.For example, suppose you are working with attributes from the Geography dimension. Se una delle espressioni restituisce tutti i membri dell'attributo Città e un'altra limita i membri dell'attributo Paese a tutti i paesi europei, allora i membri di Città saranno limitati alle sole città che appartengono a paesi europei.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. Questa caratteristica di Analysis Services, denominata Auto Exist, si applica solo agli attributi della stessa dimensione,This characteristic of Analysis Services is called Autoexists and applies only to attributes in the same dimension. un quanto tenta di impedire che i record di dimensioni esclusi nell'espressione di un attributo vengano inclusi dalle espressioni di altri attributi.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. La caratteristica Auto Exist può inoltre essere interpretata come l'intersezione risultante da diverse espressioni di attributi su record di dimensioni.Autoexists can also be understood as the resulting intersection of the different attributes expressions over the dimension records. Vedere gli esempi seguenti: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]

Il set di risultati che si ottiene è il seguente: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%

Il set di prodotti ottenuto sembra uguale a Preferred10Products, riportato di seguito per verifica: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]

In base ai risultati seguenti, i due set (Top10SellingProducts, Preferred10Products) sono identiciAs 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%

Negli esempi precedenti sono stati creati due set, uno come espressione calcolata e l'altro come espressione costante.In the previous examples we created two sets: one as a calculated expression and the other as a constant expression. In questi esempi vengono illustrate le versioni diverse di Auto Exist.These examples illustrate the different flavors of Autoexists.

La caratteristica Auto Exist può essere applicata in modo completo o superficiale alle espressioni.Autoexists can be applied deep or shallow to the expressions. Per impostazione predefinita viene applicata in modo completo,The default setting is deep. come illustrato nell'esempio seguente.The following example will illustrate the concept of deep Autoexists. Nell'esempio si filtra Top10SellingProducts in base all'attributo [Product].[Product Line] per i membri del gruppo [Mountain].In the example we are filtering Top10SellingProducts by [Product].[Product Line] attribute for those in [Mountain] group. Si noti che entrambi gli attributi (sezionamento e asse) appartengono alla stessa dimensione, [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]

Viene prodotto il set di risultati seguente: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%

Nel set di risultati precedente si registrano sette nuove presenze nell'elenco Top10SellingProducts, mentre Mountain-200, Mountain-100 e HL Mountain Frame sono stati spostati all'inizio dell'elenco.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. Questi tre valori sono frammisti.In the previous result set those three values were interspersed

Questa caratteristica viene denominata Auto Exist completa perché il set Top10SellingProducts viene valutato per soddisfare le condizioni di sezionamento della query.This is called Deep Autoexists, because the Top10SellingProducts set is evaluated to meet the slicing conditions of the query. Auto Exist completo significa che tutte le espressioni verranno valutate per soddisfare lo spazio più completo possibile dopo l'applicazioni delle espressioni di sezionamento, delle espressioni sub-SELECT nell'asse e così via.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.

Tuttavia, è possibile decidere di eseguire l'analisi su Top10SellingProducts, in quanto equivalente a Preferred10Products, come nell'esempio seguente: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]

Viene prodotto il set di risultati seguente: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%

Nei risultati precedenti il sezionamento fornisce un risultato che contiene solo i prodotti dell'elenco Preferred10Products che fanno parte del gruppo [Mountain] in [Product].[Product Line], come previsto, in quanto Preferred10Products è un'espressione costante.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.

Questo set di risultati viene interpretato anche come Auto Exist superficiale,This result set is also understood as shallow Autoexists. in quanto l'espressione viene valutata prima della clausola di sezionamento.This is because the expression is evaluated before the slicing clause. Nell'esempio precedente, l'espressione è un'espressione costante a scopo illustrativo, al fine di presentare il concetto.In the previous example, the expression was a constant expression for illustration purposes in order to introduce the concept.

Il comportamento di Auto Exist può essere modificato a livello di sessione usando la proprietà della stringa di connessione Autoexists .Autoexists behavior can be modified at the session level using the Autoexists connection string property. L'esempio seguente inizia con l'apertura di una nuova sessione e l'aggiunta della proprietà Autoexists=3 alla stringa di connessione.The following example begins by opening a new session and adding the Autoexists=3 property to the connection string. Per eseguire l'esempio, è necessario aprire una nuova connessione.You must open a new connection in order to do the example. Una volta stabilita la connessione con l'impostazione Auto Exist, quest'ultima rimarrà effettiva fino al termine della connessione.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]

Nel set di risultati seguente è illustrato il comportamento superficiale di Auto Exist.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%

Comportamento di Auto Exist può essere modificato tramite la caratteristica Auto Exist = [1 | 2 | 3]. parametro nella stringa di connessione. vedere supportate proprietà XMLA ( XMLA ) e ConnectionString per l'utilizzo del parametro.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.

EsempiExamples

Nell'esempio seguente restituisce la somma del Measures.[Order Quantity] membro, aggregato sui primi otto mesi dell'anno di calendario 2003 contenuti nel Date dimensione, dal Adventure Works cubo.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]  

Per comprendere NON VISUAL l'esempio seguente è una query di [Adventure Works] per ottenere le cifre di [Reseller Sales Amount] in una tabella in cui le categorie di prodotti sono le colonne e i tipi business dei rivenditori sono le righe.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. I totali vengono forniti per sia i prodotti che per i rivenditori.Note that totals are given for both products and resellers.

L'istruzione SELECT seguente:The following SELECT statement:

select [Category].members on 0,

[Business Type].members on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

Produce i risultati seguenti: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

Per produrre una tabella solo con i dati per i prodotti Accessories e Clothing, i rivenditori Value Added Reseller e Warehouse, conservando tuttavia i totali complessivi, si potrebbe scrivere una query come la seguente utilizzando 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]

Produce i risultati seguenti: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

Per produrre una tabella che visivamente somma le colonne, ma per i totali delle righe riporta il totale vero di [Category], immettere la query seguente: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]

Notare come NON VISUAL è applicato solo a [Category].Note how NON VISUAL is only applied to [Category].

La query precedente produce i seguenti risultati: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

Se si confrontano i risultati precedenti, è possibile osservare che la riga [All Resellers] si aggiunge ai valori visualizzati di [Value Added Reseller] e [Warehouse] ma che la colonna [All Products] mostra il valore totale per tutti i prodotti, compresi quelli non visualizzati.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.

Nell'esempio seguente viene dimostrato come si utilizzano i membri calcolati nelle sub-SELECT per filtrarli.The following example demonstrates how to use calculated members in subselects to filter on them. Per essere in grado di riprodurre questo esempio, è necessario stabilire la connessione utilizzando il parametro di stringa di connessione sottoquery = 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]

)

La query precedente produce i seguenti risultati: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%

Vedere ancheSee Also

Concetti chiave di MDX ( Analysis Services ) Key Concepts in MDX (Analysis Services)
Le istruzioni di manipolazione dei dati MDX ( MDX ) MDX Data Manipulation Statements (MDX)
Restrizione della Query con Query e assi di sezionamento ( MDX )Restricting the Query with Query and Slicer Axes (MDX)