MDX中的通用计算和选择

MDX中的通用计算和选择

一： mdx中的元数据引用函数

CurrentMember,Parent,Ancestor(),PrevMember,NextMember,Lag(),Lead(),PeriodsToDate(),ParallelPeriod(),

LastPeriods(),OpeningPeriod(),CloseingPeriod(),Iif()

 

二：同一层次结构中级别之间的简单比率

 

1、值相对于全部[All]数值或维度总额成员数据的比率: 查询三级品类占全部的比率

with member [Measures].[Rate] as [Measures].[缺货库存] / ([Measures].[缺货库存],[商品品类.品类Sku].[AllSkus])

SELECT

NON EMPTY {[Measures].[Rate]} ON COLUMNS,

NON EMPTY {[商品品类.品类Sku].[三级品类].Members} ON Rows

FROM [Repertory_Examination]

 

2、值相对于父对象数值的比率:查询三级品类占二级品类的比率

with member [Measures].[Rate] as [Measures].[缺货库存] / ([Measures].[缺货库存],[商品品类.品类Sku].currentmember.parent)

SELECT

NON EMPTY {[Measures].[Rate]} ON COLUMNS,

NON EMPTY {[商品品类.品类Sku].[三级品类].Members} ON Rows

FROM [Repertory_Examination]

 

3、值相对于高于父对象的祖先对象的数值的比率

with member [Measures].[Rate] as [Measures].[缺货库存] / ([Measures].[缺货库存],Ancestor([商品品类.品类Sku].currentmember,[商品品类.品类Sku].[三级品类])) 

 

三、均值

 

1、简单均值：求三个品类的平均值

With Set [商品品类] AS {[商品品类.商品Sku].[737].[738].[749], [商品品类.商品Sku].[737].[738].[750], [商品品类.商品Sku].[737].[738].[751]}

Member [商品品类.商品Sku].[pinlei] AS 'avg( [商品品类] )'

Member [Measures].[差值] as '[Measures].[现货库存]-[Measures].[缺货库存]'

SELECT

{[Measures].[缺货库存],[Measures].[现货库存],[Measures].[差值]} on columns

 

FROM [Repertory_Examination] where { [商品品类.商品Sku].[pinlei]}

 

四、基于时间的引用和时间序列的计算

 

1、各时期的引用和计算 : 计算与上一个时间成员的增长，不受时间成员属于哪个级别的影响

with member [Measures].[increase] as 'iif([Measures].[现货库存]-([时间.年月日].currentMember.prevmember,[Measures].[现货库存])=null,0,[Measures].[现货库存]-([时间.年月日].currentMember.prevmember,[Measures].[现货库存]))' 

select 

{

    [Measures].[increase]

} 

on columns,

{

{[时间.年月日].[2015].[11].[16], [时间.年月日].[2015].[11].[17]}

}

on rows

FROM [Repertory_Examination]

 

2、去年同期的引用和计算（同比去年）

with member [Measures].[increase] as 

'[Measures].[现货库存] - ([Measures].[现货库存] - 

ParallelPeriod([时间.年月日].[Year],1,[时间.年月日].CurrentMember))' 

select 

{

    [Measures].[increase]

} 

on columns,

{

{[时间.年月日].[2015].[11].[16], [时间.年月日].[2015].[11].[17]}

}

on rows

FROM [Repertory_Examination]

 

3 本年至今的聚合值

with member [Measures].[ytd] as 'Sum(periodsToDate([时间.年月日].[年],[时间.年月日].CurrentMember),[Measures].[库存量])'

SELECT

{[Measures].[ytd]} on columns,

{[时间.年月日].[2015].[11].[14], [时间.年月日].[2015].[11].[15], [时间.年月日].[2015].[11].[16]} on rows

FROM [Repertory_Health_Day]

 

 

4 使用lastperiods()选择基于目标成员的时间范围

SELECT

{ [Measures].[库存量]} on columns,

{

  LastPeriods (-4,[时间.年月日].[2015].[11].[14])

} on rows

FROM [Repertory_Health_Day]