MDX常用函数

1.ParallelPeriod

--ParallelPeriod
with member [Measures].[a1] As 'ParAllelPeriod(Year,2,[时间].[月].&[2011]&[12])'
select ParAllelPeriod([时间].[月].&[2011]&[10].level,1,[时间].[月].&[2011]&[10]) on columns,
[Measures].[会员人数] on rows
from [BSUSYS_New]

 

2.Topcount

select [Measures].[会员人数] on 0,
topcount([时间].[月].[月],5,[Measures].[会员人数]) on 1
from [BSUSYS_New]

3.bottomcount

select [Measures].[会员人数] on 0,
bottomcount([时间].[月].[月],5,[Measures].[会员人数]) on 1
from [BSUSYS_New]

4.Toppercent(这些元素的累积合计至少为指定的百分比即指定月份的会员人数总和至少是总会员人数的50%)

select [Measures].[会员人数] on 0,
Toppercent([时间].[月].[月],50,[Measures].[会员人数]) on 1
from [BSUSYS_New]

5.bottompercent

select [Measures].[会员人数] on 0,
bottompercent([时间].[月].[月],50,[Measures].[会员人数]) on 1
from [BSUSYS_New]

6.TOPSUM(这些月份的累计会员>=1600000)

select [Measures].[会员人数] on 0,
TOPSUM([时间].[月].[月],1600000,[Measures].[会员人数]) on 1
from [BSUSYS_New]

7.BOTTOMSUM

select [Measures].[会员人数] on 0,
BOTTOMSUM([时间].[月].[月],500,[Measures].[会员人数]) on 1
from [BSUSYS_New]

8.Filter

select [Measures].[会员人数] on 0,
filter([时间].[月].[月],[Measures].[会员人数]>100000) on 1
from [BSUSYS_New]

9.generate(将集合应用到另一集合的每个成员)

--Generate 最常见的实际用法是为一组成员计算复杂集表达式，如 TopCount。
select [Measures].[会员人数] on 0,
generate ([时间].[年].[年].members,
TOPcount([时间].[年].currentmember*[会员 - 注册门店].[省].[省].members,2,[Measures].[会员人数])) on 1
from  [BSUSYS_New]
/*将年份跟会员人数排名前2名的省份的集进行简单交叉联接将
显示所有时间的前2个省份（每年都重复）,而不是每年的前两名省份*/
select [Measures].[会员人数] on 0,
[时间].[年].[年].members*TOPcount([会员 - 注册门店].[省].[省].members,2,[Measures].[会员人数]) on 1
from  [BSUSYS_New]
--以下示例说明如何使用 Generate 返回字符串：
with member MEASURES.GENERATESTRINGDEMO AS
generate ([时间].[年].[年].members,[时间].[年].currentmember.name)
member MEASURES.GENERATEDELIMITEDSTRINGDEMO AS
generate ([时间].[年].[年].members,[时间].[年].currentmember.name," and ")
select {MEASURES.GENERATESTRINGDEMO,MEASURES.GENERATEDELIMITEDSTRINGDEMO }on 0
from [BSUSYS_New]

10.Descendants(Descendants 函数中的成员和级别必须属于同一层次结构)

select [Measures].[订单数] on 0,
descendants([创建时间].[年季月层次].[年].&[2011],[创建时间].[年季月层次].[月],SELF)  on 1
from YHDJBLMDataDW

11.Ancestor（在同一层次结构才行）

WITH MEMBER Measures.x AS [Measures]. [Measures].[商品小票数]/ 
   (
   [Measures].[Measures].[商品小票数],  
      Ancestor 
         (
         [商品].[旧品类层次].CurrentMember,
         [商品].[旧品类层次].[旧大品类]
         )
   ), FORMAT_STRING = '0%'
SELECT {[Measures].[商品小票数], Measures.x} ON 0,
{
   Descendants 
      (
        [商品].[旧品类层次].[旧大品类].&[糖果类],
         [商品].[旧品类层次].[旧小品类], SELF 
      )
} ON 1
FROM [YHD_NEW]

12.RANK

WITH 
MEMBER [Measures].[Rank] AS
Rank(
[商品].[旧品类层次].CurrentMember,
[商品].[旧品类层次].CurrentMember.Siblings,
([Measures].[商品小票数])
)
SELECT
{
[Measures].[商品小票数],
[Measures].[Rank]
} ON COLUMNS,
order ({[商品].[旧品类层次].[旧大品类].Members} ,[Measures].[Rank]
,asc)ON ROWS
FROM [YHD_NEW]

13.IsLeaf

WITH
MEMBER [Measures].[ss] AS
IIF(
IsLeaf([商品].[旧品类层次].CurrentMember),
"N/A",
COUNT(
[商品].[旧品类层次].CurrentMember.Children
)
)
SELECT
{[Measures].[ss]} ON COLUMNS,
{[商品].[旧品类层次].[旧小品类].Members} ON ROWS
FROM [YHD_NEW]

14.OpeningPeriod/ClosingPeriod

SELECT OpeningPeriod
([时间].[日历层次].[月],[时间].[日历层次].[年].&[2010]) ON 0,
[Measures].[商品小票数] on 1
FROM [YHD_NEW]

SELECT ClosingPeriod
([时间].[日历层次].[季度],[时间].[日历层次].[年].&[2010]) ON 0,
[Measures].[商品小票数] on 1
FROM [YHD_NEW]

15.AGGREGATE

with member [商品].[aa]
AS AGGREGATE({[商品].[旧品类层次].[旧大品类].&[NULL], 
[商品].[旧品类层次].[旧大品类].&[N元系列], 
[商品].[旧品类层次].[旧大品类].&[车用产品], 
[商品].[旧品类层次].[旧大品类].&[宠物专用]})
SELECT [商品].[aa] ON COLUMNS,
[Measures].[商品小票数] on ROWS
FROM [YHD_NEW]

16.VisualTotals

with member [aa]
 AS 

        VISUALTOTALS(
                { 
                    [商品].[旧品类层次].[All], 
                    [商品].[旧品类层次].[旧大品类].&[NULL], 
                    [商品].[旧品类层次].[旧大品类].&[N元系列], 
                    [商品].[旧品类层次].[旧大品类].&[车用产品], 
                    [商品].[旧品类层次].[旧大品类].&[宠物专用]
                }
        )
        
SELECT 
{[Measures].[不重复商品小票数]}    ON COLUMNS, 
[aa]    ON ROWS
FROM [YHD_NEW]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR


SELECT [Measures].[商品小票数] ON Columns,
   VisualTotals
   ({[商品].[旧大品类].[ALL]
,[商品].[旧大品类].&[NULL]
,[商品].[旧大品类].&[N元系列]
     }
      , '* - Visual Total'
   ) ON Rows
FROM [YHD_NEW]


SELECT {[Measures].[商品小票数],[Measures].[不重复商品小票数],
[Measures].[不重复渗透率]} ON Columns,
 VisualTotals({[商品].[旧大品类].[ALL]
,[商品].[旧大品类].&[NULL]
,[商品].[旧大品类].&[N元系列]
     })
 ON Rows
FROM [YHD_NEW]

17.DrilldownLevel

SELECT {[Measures].[商品小票数],
[Measures].[渗透率],
[Measures].[不重复商品小票数]} 
ON COLUMNS , 
{DrilldownLevel({[商品].[旧品类层次].[All]})}
ON ROWS  
FROM 
(
SELECT 
({[商品].[旧品类层次].[旧大品类].&[宠物专用], 
[商品].[旧品类层次].[旧大品类].&[车用产品], 
[商品].[旧品类层次].[旧大品类].&[N元系列], 
[商品].[旧品类层次].[旧大品类].&[NULL]}) 
ON COLUMNS  
FROM [YHD_NEW]
) 

18.杂项

/*instr*/
SELECT 
[Measures].[积金币次数] ON 0,
FILTER([产品].[产品名].CHILDREN,
INSTR([产品].[产品名].CURRENTMEMBER.NAME,'西王')>0
and INSTR([产品].[产品名].CURRENTMEMBER.NAME,'玉米')>0) ON 1
FROM [YHDJBLMDataDW]

/*rank*/
with set [aa] 
as filter (order (([产品].[产品名].children,[金币联盟会员].[省].children)
,[Measures].[积金币次数],bdesc),[Measures].[积金币次数]>10000)

member [Measures].[bb] as 
rank(([产品].[产品名].currentmember,[金币联盟会员].[省].currentmember),[aa])

select 
{[Measures].[bb],[Measures].[积金币次数]} on 0,
[aa] on 1 
from [YHDJBLMDataDW]

/*lastperiods和lastchild*/
with set [aa] as filter(order(lastperiods(4,[创建时间].[季度].&[2012]&[1])
,[Measures].[积金币次数],desc)
,[Measures].[积金币次数]>0)

member [Measures].[bb] as 
rank(([创建时间].[季度].currentmember),[aa])

select {[Measures].[bb],[Measures].[积金币次数]} on 0,
[aa] on 1 
from [YHDJBLMDataDW]

/*head 和tail*/
with set [top10province] as 
tail(topcount([产品].[产品名].children*[金币联盟会员].[省].children,10,
[Measures].[积金币次数]),5)

select [Measures].[积金币次数] on 0,
[top10province] on 1
from [YHDJBLMDataDW]

/*children/sibling/parent*/
select [Measures].[积金币次数] on 0,
filter([产品].[产品层次].
[商家].&[shengyuan].&[58优博].
&[08版新优博2段900g听装较大婴儿配方奶粉].siblings,[Measures].[积金币次数]>10000) on 1
from [YHDJBLMDataDW]

select [Measures].[积金币次数] on 0,
[产品].[产品层次].
[商家].&[shengyuan].&[58优博].
&[08版新优博2段900g听装较大婴儿配方奶粉].parent on 1
from [YHDJBLMDataDW]

/*Descendants/ancestors/ascendants*/
select [Measures].[积金币次数] on 0,
order(filter(Descendants([产品].[产品层次].[商家].&[shengyuan].&[58优博],
[产品].[产品层次].[产品名],self_and_before),[Measures].[积金币次数]>150000),[Measures].[积金币次数],desc) on 1
from [YHDJBLMDataDW]

select [Measures].[积金币次数] on 0,
order(filter(ancestors([产品].[产品层次].[商家].&[shengyuan].
&[58优博].&[10版优博4段400g盒装奶粉],
[产品].[产品层次].[商家]),[Measures].[积金币次数]>150000),[Measures].[积金币次数],desc) on 1
from [YHDJBLMDataDW]

select [Measures].[积金币次数] on 0,
ascendants([产品].[产品层次].[商家].&[shengyuan].&[58优博])on 1
from [YHDJBLMDataDW]

/*hierarchize 整理回层次结构的正常顺序*/
select [Measures].[积金币次数] on 0,
hierarchize(union(ascendants([产品].[产品层次].[商家].&[shengyuan].&[58优博]),
Descendants([产品].[产品层次].[商家].&[shengyuan].&[58优博],
[产品].[产品层次].[产品名],self_and_before)))
on 1
from [YHDJBLMDataDW]

select [Measures].[积金币次数] on 0,
[产品].[产品层次].[商家].&[shengyuan].firstchild.parent.children on 1
from [YHDJBLMDataDW]
/*openingperiod/closingperiod*/
with member [firstmonth] as
([Measures].[积金币次数]
,closingperiod([创建时间].[年季月层次].[月],[创建时间].[年季月层次]))

select {[firstmonth],[Measures].[积金币次数]} on 0,
[产品].[产品层次].[商家].&[shengyuan].children 
having [Measures].[积金币次数]>10000 and [firstmonth]>6028 on 1
from [YHDJBLMDataDW]
where [创建时间].[年季月层次].[年].&[2011]

19.前12个月有积金币次数的情况，且倒序排列

with set [lastmonth] as 
tail(filter([创建时间].[月].[月],[Measures].[积金币次数]),1)

set [last12months] as
order(lastperiods(12,[lastmonth].item(0).item(0)),
[创建时间].[月].currentmember.properties("Key0")
+VBA!Right([创建时间].[月].currentmember.properties("Key1"),2),desc)

select
[Measures].[积金币次数] on 0,
[last12months] on 1
from [YHDJBLMDataDW]


select
[Measures].[积金币次数] on 0,
Mtd([创建时间].[月].&[2012]&[5])on 1
from [YHDJBLMDataDW]

先这些吧，以后再慢慢添加。