MDX应用于FoodMart的15个例子

 LISTING 1: Determining Products Sold in Each State

with set [SoldInUSA] as 'Filter([Product].[Brand Name].Members, Not IsEmpty( ([USA], [Unit
Sales]) ))'
 member [Measures].[SoldInState] as 'iif( IsEmpty(([Product].CurrentMember, [Unit Sales],
[Customers].CurrentMember)), "No","Yes" )'
select [USA].children on COLUMNS,
  [SoldInUSA] on ROWS
from Sales
where ([SoldInState])

LISTING 2: Determining Top 10 Product Categories

select {[Unit Sales]} on COLUMNS,
  TopCount( [Product].[Product Category].Members, 10, ([Unit Sales]) ) on ROWS
from Sales

LISTING 3: Determining Brands Sold During the Past Three Quarters

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not
IsEmpty([Time].CurrentMember)),1)'
 set [Last3Quarters] as ' [LastQuarter].item(0).item(0).Lag(2) : [LastQuarter].item(0).item(0)'
select [Last3Quarters] on COLUMNS,
 Non Empty Union(Descendants( [Food], [Product].[Brand Name] ), Descendants( [Drink],
[Product].[Brand Name] )) on ROWS
from Sales

LISTING 4: Determining Recent Trends for Best-Selling Brands

with set [TenBest] as 'TopCount( [Product].[Brand Name].Members, 10, [Unit Sales] )'
  set [LastMonth] as 'Tail(Filter([Time].[Month].Members, Not IsEmpty([Time].CurrentMember)),1)'
 set [Last6Months] as ' [LastMonth].item(0).item(0).Lag(6) : [LastMonth].item(0).item(0)'
select [Last6Months] on COLUMNS,
 [TenBest] on ROWS
from Sales

LISTING 5: Determining Brands That Make Up 80 Percent of Sales

select {[Unit Sales]} on COLUMNS,
TopPercent([Product].[Brand Name].Members, 80, [Unit Sales]) on ROWS
from Sales

LISTING 6: Determining Brands That Make Up the Bottom 20 Percent of Sales

select {[Unit Sales]} on COLUMNS,
 Non Empty BottomPercent([Product].[Brand Name].Members, 20, [Unit Sales]) on ROWS
from Sales

LISTING 7: Determining the Top Five Stores and the Top Five Customers

select {[Unit Sales]} on COLUMNS,
 Generate( TopCount([Store].[Store Name].Members, 5, [Unit Sales]),
 { [Store].CurrentMember } * TopCount( [Customers].[Name].Members, 5, ([Unit Sales],
[Store].CurrentMember) ) ) on ROWS
from Sales

LISTING 8: Determining Two Top-Selling Products

with member [Measures].[PercTotalSales] as
' Sum( TopCount([Product].CurrentMember.Children, 2, [Unit Sales]), [Unit Sales] )
/([Product].CurrentMember, [Unit Sales])',
FORMAT_STRING = '##.0%'
select [Store].[(All)].Members on COLUMNS,
  Generate( [Product].[Brand Name].Members,
   Union(
      TopCount( [Product].CurrentMember.Children, 2, [Unit Sales] ) * {[Unit Sales]},
      { ([Product].CurrentMember, [PercTotalSales]) }
      )
   ) on ROWS
from Sales

LISTING 9: Highlighting Products in the Bottom 10 Percent

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].[HLUnit Sales] as '[Unit Sales]',
  FONT_FLAGS = 'iif( Count(
       Intersect( BottomPercent( [Product].[Brand Name].Members, 10, ([Unit Sales]) ),
{[Product].CurrentMember})
       ) = 0, 0, 1)'
select [Last4Quarters] on COLUMNS,
  [Product].[Brand Name].Members on ROWS
from Sales
where ([HLUnit Sales])
cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS

LISTING 10: Comparing Sales with Those of Parallel Months

with set [PromoMonths] as 'Filter([Time].[Month].Members, Not IsEmpty( ([Unit Sales], [Double
Your Savings]) ) )'
  set [PromoRange] as 'Head( [PromoMonths] ).item(0).item(0) : Tail( [PromoMonths]
).item(0).item(0)'
 member [Measures].[Uplift] as '([Unit Sales], [Double Your Savings])'
 member [Measures].[This Quarter] as '[Unit Sales]'
 member [Measures].[Last Quarter] as '( ParallelPeriod( [Time].[Quarter] ), [Unit Sales] )'
 member [Measures].[Growth] as ' [This Quarter] - [Last Quarter]'
select [PromoRange] on Columns,
 { [This Quarter], [Last Quarter], [Growth], [Uplift] } on Rows
from [Sales]

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