MDX查询几个经典示例

1说明：查询在所有州都销售的品牌。

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

 

2说明：查询销售前名的产品类别

select {[Unit Sales]} on COLUMNS,

TopCount( [Product].[Product Category].Members, 10, ([Unit Sales]) ) on ROWS

from Sales

 

3说明：在过去三个季度里都存在销售量的商品销售记录

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

 

4说明：查出最近个月销售趋势最好的前个商品及其各自销售量

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

 

5说明：找出组成销售额%的商品销售及其记录；

select {[Unit Sales]} on COLUMNS,

TopPercent([Product].[Brand Name].Members, 80, [Unit Sales]) on ROWS

from Sales

 

6说明：按销售量排序，找出组成%销售量的商品销售记录

select {[Unit Sales]} on COLUMNS,

Non Empty BottomPercent([Product].[Brand Name].Members, 20, [Unit Sales]) on ROWS

from Sales

 

7说明：查出销售量最好的前名店和每个店的前个顾客及其销售记录

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

 

8说明：查出每种品牌前名产品的销售记录，以及各自分别占所在品牌的百分比

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

 

9说明：查出个季度中，每个时期销售量在后%的产品销售量，并显示为粗体

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

 

10说明：比较具有相同相对位置的时间点的销售量，例如今年月和去年月；

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]

 

11说明：查出利润率在%以上的产品及销售记录

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

 

12说明：找出最近一季度比前一季度销售量增长幅度大于%的产品销售记录

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

 

13说明：找出销售额在前、后名的产品销售记录，并列出总排名，就是找出销售情况最好和最坏的产品

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

 

14说明：比较一下产品销售趋势，没什么用

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

 

15说明：查出一定条件下的前名产品的销售记录，例如销售量在到之间的

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

member [measures].[abc] as '[Product].CurrentMember.uniquename'

select [Last4Quarters] on COLUMNS,

{ [Unit Sales], [GroupAvg], [AllAvg],[measures].[abc] } on ROWS

from Sales

where ([Ebony Plums])

 

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

member [measures].[abc] as '[Product].CurrentMember.uniquename'

member [measures].[abcd] as 'lookupcube("Trained Cube","MemberToStr([Customers].[All Customers].[Canada])")'

select [Last4Quarters] on COLUMNS,

{ [Unit Sales], [GroupAvg], [AllAvg],[measures].[abc] ,[measures].[abcd] } on ROWS

from Sales

where ([Ebony Plums])