mdx 各类典型语句的集锦

with 
MEMBER [Measures].[Values1] AS [Employee - Owner].[Finance].currentmember.properties('name')
set mm as filter([Employee - Owner].[Finance].allmembers,INSTR([Employee - Owner].[Finance].currentmember.name,'P10074766')>0)
SELECT {[Measures].[Values1]} ON 0,
mm ON 1
FROM   [Cube]
WHERE  ({[Employee - Owner].[Person Status].&[Active],[Employee - Owner].[Person Status].&[Active contingent]},
[Time - UTC].[Fiscal].[Month].&[2014]&[8])

补充一段根据唯一ID获取name的例子:

with   
MEMBER [Measures].[Values1] AS [Service Offering].[CTS Offering].currentmember.properties('name')  
set mm as filter([Service Offering].[CTS Offering].allmembers,[Service Offering].[CTS Offering].currentmember.properties('key') ='263781')  
SELECT {[Measures].[Values1]} ON 0,  
mm ON 1  
FROM   [CUBE]

2016-6-28再补充一段通过level来filter的:

with member [Measures].value1 as [Customer Area].[Default].currentmember.properties('name')
set mm as filter( [Customer Area].[Default].members, [Customer Area].[Default].currentmember.properties('LEVEL_NUMBER') ='4') 
select
[Measures].value1 ON 0,  
mm on 1
from [cube]

2016-7-19再补充一段结合strtovalue来filter的:

SELECT 
[Measures].[Created SR Count] on 0,
NON EMPTY drilldownlevel(drilldownlevel([CSS Cost Allocation].[CSS Cost Allocation Hierarchy].[All])) *
descendants(
filter({[Time - UTC].[Fiscal].[Year].&[2015],
[Time - UTC].[Fiscal].[Year].&[2016],
[Time - UTC].[Fiscal].[Year].&[2017]},StrToValue([Time - UTC].[Fiscal].currentmember.properties('key')) >=2015),2)on 1
from [cube]
WHERE ( [Geography - Customer].[Geography].[All], 
[Service Offering].[CTS Offering].[All], 
[Product - Routing].[Products].[All], 
[SR Attribute].[Is Bug].[All]) 

2016-8-4:

with 
member [Measures].v4 as [Product - Routing].[Products].currentmember.properties('name')  
member [Measures].v3 as [Product - Routing].[Products].currentmember.parent.properties('name')  
member [Measures].v2 as [Product - Routing].[Products].currentmember.parent.parent.properties('name')  
member [Measures].v1 as [Product - Routing].[Products].currentmember.parent.parent.parent.properties('name')  

select {[Measures].v1,[Measures].v2,[Measures].v3,[Measures].v4} on 0,
filter(
{
descendants([Product - Routing].[Products].[Commercial Product Level1].&[4238610],4,self_and_before),
descendants([Product - Routing].[Products].[Commercial Product Level1].&[4238610],4,self_and_before),
descendants([Product - Routing].[Products].[Commercial Product Level1].&[4888467],4,self_and_before)
}
,StrToValue([Product - Routing].[Products].properties('LEVEL_NUMBER')) <=3)   

on 1
from [the cube]

2016-10-20 根据节点名字排序

SELECT 
[Dates].[FiscalDate].[FiscalYear].&[FY 17] on 0,
non empty 
order(
{descendants([CSSCostAllocations].[CostAllocation].[All],3)}
,[CSSCostAllocations].[CostAllocation].currentmember.properties('name'),basc) 
on 1
FROM [Model]
where ([Measures].[Total Costs])

2016-10-31 define measure

with
measure 'IVRSurveys'[Total Q4 Survey Count]  = SUM(IVRSurveys[Q4_SurveyCount])

SELECT [Measures].[Total Q4 Survey Count] on 0
, non empty products
*{[DateTimeUtc].[FiscalYear UTC].&[2015],
[DateTimeUtc].[FiscalYear UTC].&[2016],
[DateTimeUtc].[FiscalYear UTC].&[2017]}
ON 1
FROM [OneDataModel]

2016-12-9 “case when“ and a skill to get current date

with 
member [Measures].whatmonth as [Time - UTC].[Fiscal].currentmember.name
member aa as 
case 
when STRTOVALUE(format(now(),"MM"))<7 then STRTOVALUE(format(now(),"MM"))+6
else STRTOVALUE(format(now(),"MM"))-6
end 

select [Measures].whatmonth
on 0,
iif(StrtoValue(Format(now(),"dd"))>15 ,
strtomember("[Time - UTC].[Fiscal].[Month].&[2017]&["+cstr(aa)+"]"),
strtomember("[Time - UTC].[Fiscal].[Month].&[2017]&["+cstr(aa-1)+"]")
)
on 1
from [cube]

2016-12-22 

with 
member which as 1

select 
exists(
[Time - UTC].[Fiscal].[Year].members,
case 
when which =1 then
strtomember("[Time - UTC].[Fiscal].[Month].&[2017]&["+cstr(case   
when STRTOVALUE(format(now(),"MM"))<7 then STRTOVALUE(format(now(),"MM"))+6  
else STRTOVALUE(format(now(),"MM"))-6  
end )+"]")
:
strtomember("[Time - UTC].[Fiscal].[Month].&[2017]&["+cstr(case   
when STRTOVALUE(format(now(),"MM"))<7 then STRTOVALUE(format(now(),"MM"))+6  
else STRTOVALUE(format(now(),"MM"))-6  
end -1)+"]")
else
[Time - UTC].[Fiscal].[Month].&[2015]&[1]:strtomember("[Time - UTC].[Fiscal].[Month].&[2017]&["+cstr(case   
when STRTOVALUE(format(now(),"MM"))<7 then STRTOVALUE(format(now(),"MM"))+6  
else STRTOVALUE(format(now(),"MM"))-6  
end )+"]")
end 
)
on 0,
NON EMPTY 
[Product - Routing].[Products].[Commercial Product Level1].&[4238610].children
on 1
from [cube]
WHERE ([Measures].[Created Count])