LISTING 11: Determining Sales That Exceed Store Cost by 160 Percent
with member [Measures].[SalesRatio] as '([Store Sales] - [Store Cost]) / [Store Cost]',
FORMAT_STRING = '##%'
select { [Store Sales], [Store Cost], [SalesRatio] } on COLUMNS,
Filter( [Product].[Brand Name].Members, [SalesRatio] > 1.60 ) on  ROWS
from Sales

LISTING 12: Determining Brands That Have Grown by More Than 50 Percent
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not
IsEmpty([Time].CurrentMember)))'
  member [Measures].[CurrQSales] as '([LastQuarter].item(0).item(0), [Unit Sales])'
  member [Measures].[PrevQSales] as '([LastQuarter].item(0).item(0).PrevMember, [Unit Sales])'
  member [Measures].[Growth] as ' ([CurrQSales] - [PrevQSales]) / [PrevQSales]',
FORMAT_STRING='##%'
select { [PrevQSales], [CurrQSales], [Growth] } on COLUMNS,
  Filter( [Product].[Brand Name].Members, [Growth] > 0.5 ) on ROWS
from Sales

LISTING 13: Determining the Top 10 and Bottom 10 Product Brands
with set [OrderedBrands] as 'Order( [Product].[Brand Name].Members, [Unit Sales], BDESC )'
  member [Measures].[Brand Rank] as 'Rank( [Product].CurrentMember, [OrderedBrands] )'
select {[Brand Rank], [Unit Sales]} on COLUMNS,
  Union( Head( [OrderedBrands], 10 ), Tail( [OrderedBrands], 10 ) ) on ROWS
from Sales

LISTING 14: Comparing Product Trends
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not
IsEmpty([Time].CurrentMember)))'
set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'
member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'
member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'
select [Last4Quarters] on COLUMNS,
{ [Unit Sales], [GroupAvg], [AllAvg] } on ROWS
from Sales
where ([Ebony Plums])

LISTING 15: Determining the Top 10 Middle-Tier Brands
with set [LastMonth] as 'Tail(Filter([Time].[Month].Members, Not
IsEmpty([Time].CurrentMember)))'
set [Last12Months] as ' [LastMonth].item(0).item(0).Lag(11) : [LastMonth].item(0).item(0)'
member [Measures].[RollingSum] as 'Sum( [Last12Months], [Unit Sales] )'
set [MiddleTierBrands] as ' Filter( [Product].[Brand Name].Members, ([RollingSum] > 500) and
([RollingSum] < 3000))'
select [Last12Months] on COLUMNS,
TopCount( [MiddleTierBrands], 10, [Unit Sales] ) on ROWS
from Sales

下载地址：http://www.dnnme.cn/upload/15个MDX语句.zip