MDX中函数的应用
成员百分比分析 函数：CurrentMember、Parent等；
分析各城市的销售所占全部城市的总销售额百分比。
WITH MEMBER Measures.[Unit Sales Percent] AS '((Store.CURRENTMEMBER, Measures.[Unit Sales]) / (Store.CURRENTMEMBER.PARENT, Measures.[Unit Sales])) ', FORMAT_STRING = 'Percent' SELECT {Measures.[Unit Sales], Measures.[Unit Sales Percent]} ON COLUMNS, ORDER(DESCENDANTS(Store.[USA].[CA], Store.[Store City], SELF),[Measures].[Unit Sales], ASC) ON ROWS FROM Sales;
Adventure Works实例：
WITH MEMBER Measures.[Unit Sales Percent]
AS ([Geography].[Geography].CURRENTMEMBER, [Measures].[Reseller Sales Amount]) /
([Geography].[Geography].CURRENTMEMBER.PARENT, [Measures].[Reseller Sales Amount])
, FORMAT_STRING = 'Percent'
SELECT {[Measures].[Reseller Sales Amount], Measures.[Unit Sales Percent]} ON COLUMNS,
non empty{
ORDER(
DESCENDANTS([Geography].[Geography].[State-Province].&[NSW]&[AU],
[Geography].[Geography].[City], SELF),
[Measures].[Reseller Sales Amount]
,ASC)
} ON ROWS
FROM [Adventure Works];
重要顾客分布分析 函数：Count、Sum、Filter、Descendants等；
分析各个省份中重要顾客的数量及他们的总购买量，"重要顾客"的定义是一个顾客的购买金额或者购买数目达到或超过一定的数值。
WITH MEMBER [Measures].[Qualified Count] AS 'COUNT(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10))' MEMBER [Measures].[Qualified Sales] AS 'SUM(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10), ([Measures].[Store Sales]))' SELECT {[Measures].[Qualified Count], [Measures].[Qualified Sales]} ON COLUMNS, DESCENDANTS([Customers].[All Customers], [State Province], SELF_AND_BEFORE) ON ROWS FROM Sales
Adventure Works实例：
WITH MEMBER [Measures].[Qualified Count] AS
COUNT(
FILTER(
DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount] > 1000 OR [Measures].[Internet Order Count] > 10)
)
MEMBER [Measures].[Qualified Sales] AS
SUM(
FILTER(
DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount] > 1000 OR [Measures].[Internet Order Count] > 10)
,[Measures].[Internet Sales Amount]
)
SELECT {[Measures].[Qualified Count],
[Measures].[Qualified Sales]} ON COLUMNS,
DESCENDANTS([Customer].[Customer Geography],
[Customer].[Customer Geography].[State-Province], SELF_AND_BEFORE)
ON ROWS
FROM [Adventure Works];
排序 函数：Order
对各个产品类别按照Store Sales指标降序排列，排序分为维内排序/整体排序。
select {[Measures].[Unit Sales], [Measures].[Store Sales]} on columns, Order([Product].[Product Department].members, [Measures].[Store Sales], DESC) on rows from Sales
历史相关的累计值 函数：YTD、Sum、Descendants
求销售额的本年累计值YTD()，类似还可以求解历史累计YTD()、本月累计MTD()、本周累计WTD()等, 以及更通用的函数PeriodToDate()。
with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])' select {[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns, {Descendants([Time].[1997],[Time].[Month])} on rows from [Warehouse and Sales]
Adventure Works实例：
with member [Measures].[Accumulated Sales]
as Sum(YTD(),[Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount],[Measures].[Accumulated Sales]} on columns,
{Descendants([Date].[Calendar].[Calendar Year].&[2004],[Date].[Calendar].[Month])}
on rows
from [Adventure Works];
四则运算 函数：四则运算函数；
在成员上及指标上均可以进行四则运算，动态派生出新的成员及指标。
WITH MEMBER MEASURES.ProfitPercent AS '([Measures].[Store Sales]-[Measures].[Store Cost])/([Measures].[Store Cost])',FORMAT_STRING = '#.00%' MEMBER [Time].[First Half 97] AS '[Time].[1997].[Q1] + [Time].[1997].[Q2]' MEMBER [Time].[Second Half 97] AS '[Time].[1997].[Q3] + [Time].[1997].[Q4]' SELECT {[Time].[First Half 97], [Time].[Second Half 97], [Time].[1997].CHILDREN} ON COLUMNS, {[Store].[Store Country].[USA].CHILDREN} ON ROWS FROM [Sales] WHERE (MEASURES.ProfitPercent)
Adventure Works实例：
WITH MEMBER MEASURES.ProfitPercent AS
([Measures].[Reseller Sales Amount]-[Measures].[Reseller Average Sales Amount])/
[Measures].[Reseller Sales Amount],
FORMAT_STRING = '#.00%'
MEMBER [Date].[Calendar].[First Half 97] AS
[Date].[Calendar].[Calendar Quarter].&[2003]&[1] + [Date].[Calendar].[Calendar Quarter].&[2003]&[2]
MEMBER [Date].[Calendar].[Second Half 97] AS
[Date].[Calendar].[Calendar Quarter].&[2003]&[3] + [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
SELECT {[Date].[Calendar].[First Half 97],
[Date].[Calendar].[Second Half 97],
[Date].[Calendar].[Calendar Year].&[2003].CHILDREN} ON COLUMNS,
{[Geography].[Geography].[Country].&[United States].CHILDREN} ON ROWS
FROM [Adventure Works]
WHERE (MEASURES.ProfitPercent);
逻辑判断 函数：IIf
逻辑判断可以根据不同的条件产生不同的结果。下例判断各商店是否是啤酒及白酒的大卖家。
WITH MEMBER [Product].[BigSeller] AS 'IIf([Product].[Drink].[Alcoholic Beverages].[Beer and Wine] > 100, "Yes","No")' SELECT {[Product].[BigSeller],[Product].children} ON COLUMNS, {[Store].[All Stores].[USA].[CA].children} ON ROWS FROM Sales
成员属性 函数：Properties、Dimension Properties
成员属性是与成员绑定的，其对应关系导致很难选择合适的使用方式。以下是使用成员属性的例子，它对应每个商店成员列出了商店类型属性，相应的，商店经理、商店规模、商店地址等属性也可以被列出。该用法稍加灵活应用就可以解决过去遇到的企业名称——〉企业代码对应展示问题。
WITH MEMBER [Measures].[StoreType] AS '[Store].CurrentMember.Properties("Store Type")' MEMBER [Measures].[ProfitPct] AS '(Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales]', FORMAT_STRING = '##.00%' SELECT { Descendants([Store].[USA], [Store].[Store Name])} ON COLUMNS, {[Measures].[Store Sales], [Measures].[Store Cost], [Measures].[StoreType], [Measures].[ProfitPct] } ON ROWS FROM Sales 另外一种用法： SELECT {[Measures].[Units Shipped], [Measures].[Units Ordered]} ON COLUMNS, [Store].[Store Name].MEMBERS DIMENSION PROPERTIES [Store].[Store Name].[Store Type] ON ROWS FROM Warehouse
多步计算实现复杂逻辑 函数：其实可以是任意函数合乎逻辑的组合
求出从来没有买过乳制品的顾客，求解过程是先求出每位顾客在过去购买的乳制品的数量累计，然后找出累计值为0的顾客。同样，过去遇到的求税额大于平均税额的海关的问题可以类似求出。
with member [Measures].[Dairy ever] as 'sum([Time].members, ([Measures].[Unit Sales],[Product].[Food].[Dairy]))' set [Customers who never bought dairy] as 'filter([Customers].members, [Measures].[Dairy ever] = 0)' select {[Measures].[Unit Sales], [Measures].[Dairy ever]} on columns, [Customers who never bought dairy] on rows from Sales
Adventure Works实例：
with member x as
sum([Date].[Calendar Year].[Calendar Year],[Measures].[Internet Sales Amount])
set y as
filter([Product].[Product Categories].[Product],[Measures].[Internet Sales Amount]>10000)
select x on 0,
y on 1
from [Adventure Works];
同期、前期 函数：PrevMember、ParellelPeriod
求解各产品销售额的去年同期值，年增长率。
with member [Measures].[Store Sales Last Period] as '([Measures].[Store Sales], Time.PrevMember)', format='#,###.00' member [Measures].[Yearly Increase Rate] as '([Measures].[Store Sales] - [Measures].[Store Sales Last Period])/ [Measures].[Store Sales Last Period]', FORMAT_STRING = 'Percent' select {[Measures].[Store Sales], [Measures].[Store Sales Last Period]} on columns, { [Product].members} on rows from Sales where ([Time].[1998])
Adventure Works实例：
with member [Measures].[Store Sales Last Period]
as ([Measures].[Internet Sales Amount], [Date].[Calendar Year].PrevMember), format='#,###.00'
select {[Measures].[Store Sales Last Period]} on columns,
non empty{[Product].[Product].[Product]} on rows
from [Adventure Works]
where [Date].[Calendar Year].&[2003];
另一个例子，使用ParellelPeriod函数。
WITH MEMBER [Measures].[YTD Unit Sales] AS 'COALESCEEMPTY(SUM(YTD(), [Measures].[Unit Sales]), 0)' MEMBER [Measures].[Previous YTD Unit Sales] AS '(Measures.[YTD Unit Sales], PARALLELPERIOD([Time].[Year]))' MEMBER [Measures].[YTD Growth] AS '[Measures].[YTD Unit Sales] - ([Measures].[Previous YTD Unit Sales])' SELECT {[Time].[1998]} ON COLUMNS, {[Measures].[YTD Unit Sales], [Measures].[Previous YTD Unit Sales], [Measures].[YTD Growth]} ON ROWS FROM Sales;
Adventure Works实例：
WITH MEMBER [Measures].[YTD Unit Sales]
AS CoalesceEmpty(SUM(YTD(), [Measures].[Internet Sales Amount]), 0)
MEMBER [Measures].[Previous YTD Unit Sales]
AS (Measures.[YTD Unit Sales], ParallelPeriod([Date].[Calendar Year].[Calendar Year]))
MEMBER [Measures].[YTD Growth]
AS [Measures].[YTD Unit Sales] - ([Measures].[Previous YTD Unit Sales])
SELECT {[Date].[Calendar Year].&[2003]} ON COLUMNS,
{[Measures].[YTD Unit Sales], [Measures].[Previous YTD Unit Sales], [Measures].[YTD Growth]} ON ROWS
FROM [Adventure Works];
Top N分析 函数：TopCount, TopPercent, BottomCount, BottomPercent
求解1998年总购买量处于前5名的顾客；
select {[Measures].[Store Sales]} on columns, {TopCount([Customers].[Customer Name].members,5, [Measures].[Store Sales])} on rows from Sales where ([time].[1998])
Adventure Works实例：
select {[Measures].[Internet Sales Amount]} on columns,
{TopCount([Product].[Product].[Product],5, [Measures].[Internet Sales Amount])} on rows
from [Adventure Works]
where ([Date].[Calendar Year].&[2003]) ;
select {[Measures].[Internet Sales Amount]} on columns,
{TopPercent([Product].[Product].[Product],10, [Measures].[Internet Sales Amount])} on rows
from [Adventure Works]
where ([Date].[Calendar Year].&[2003]);
成员过滤 函数：Filter、Except
求解1998年所有顾客中购买总额得到1万元以上的顾客，列出满足条件的顾客的名字、年购买数量、年购买金额。
Select {[measures].[Store Sales],[measures].[unit sales]} on columns, FILTER(Customers.[Name].Members,[Measures].[Store Sales] > 10000) on rows From sales Where ([time].[1998]) 另外一种成员过滤（从所有的媒体类型中剔除No Media类型），确切的说应该是集合运算。 select {[Measures].[Unit Sales]} on columns, except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}) on rows from Sales
时间段 函数：sum、"："运算符
求美国的商店在指定时间段内的销售额。
WITH MEMBER [Time].[1997].[Six Month] AS 'SUM([Time].[1]:[Time].[6])' MEMBER [Time].[1997].[Nine Month] AS 'SUM([Time].[1]:[Time].[9])' SELECT {[Time].[1997].[Six Month],[Time].[1997].[Nine Month]} ON COLUMNS, {[measures].[store salse]} ON ROWS FROM Sales Where ([Store].[USA])
成员百分比分析 函数：CurrentMember、Parent等；
分析各城市的销售所占全部城市的总销售额百分比。
WITH MEMBER Measures.[Unit Sales Percent] AS '((Store.CURRENTMEMBER, Measures.[Unit Sales]) / (Store.CURRENTMEMBER.PARENT, Measures.[Unit Sales])) ', FORMAT_STRING = 'Percent' SELECT {Measures.[Unit Sales], Measures.[Unit Sales Percent]} ON COLUMNS, ORDER(DESCENDANTS(Store.[USA].[CA], Store.[Store City], SELF),[Measures].[Unit Sales], ASC) ON ROWS FROM Sales;
Adventure Works实例：
WITH MEMBER Measures.[Unit Sales Percent]
AS ([Geography].[Geography].CURRENTMEMBER, [Measures].[Reseller Sales Amount]) /
([Geography].[Geography].CURRENTMEMBER.PARENT, [Measures].[Reseller Sales Amount])
, FORMAT_STRING = 'Percent'
SELECT {[Measures].[Reseller Sales Amount], Measures.[Unit Sales Percent]} ON COLUMNS,
non empty{
ORDER(
DESCENDANTS([Geography].[Geography].[State-Province].&[NSW]&[AU],
[Geography].[Geography].[City], SELF),
[Measures].[Reseller Sales Amount]
,ASC)
} ON ROWS
FROM [Adventure Works];
重要顾客分布分析 函数：Count、Sum、Filter、Descendants等；
分析各个省份中重要顾客的数量及他们的总购买量，"重要顾客"的定义是一个顾客的购买金额或者购买数目达到或超过一定的数值。
WITH MEMBER [Measures].[Qualified Count] AS 'COUNT(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10))' MEMBER [Measures].[Qualified Sales] AS 'SUM(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10), ([Measures].[Store Sales]))' SELECT {[Measures].[Qualified Count], [Measures].[Qualified Sales]} ON COLUMNS, DESCENDANTS([Customers].[All Customers], [State Province], SELF_AND_BEFORE) ON ROWS FROM Sales
Adventure Works实例：
WITH MEMBER [Measures].[Qualified Count] AS
COUNT(
FILTER(
DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount] > 1000 OR [Measures].[Internet Order Count] > 10)
)
MEMBER [Measures].[Qualified Sales] AS
SUM(
FILTER(
DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount] > 1000 OR [Measures].[Internet Order Count] > 10)
,[Measures].[Internet Sales Amount]
)
SELECT {[Measures].[Qualified Count],
[Measures].[Qualified Sales]} ON COLUMNS,
DESCENDANTS([Customer].[Customer Geography],
[Customer].[Customer Geography].[State-Province], SELF_AND_BEFORE)
ON ROWS
FROM [Adventure Works];
排序 函数：Order
对各个产品类别按照Store Sales指标降序排列，排序分为维内排序/整体排序。
select {[Measures].[Unit Sales], [Measures].[Store Sales]} on columns, Order([Product].[Product Department].members, [Measures].[Store Sales], DESC) on rows from Sales
历史相关的累计值 函数：YTD、Sum、Descendants
求销售额的本年累计值YTD()，类似还可以求解历史累计YTD()、本月累计MTD()、本周累计WTD()等, 以及更通用的函数PeriodToDate()。
with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])' select {[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns, {Descendants([Time].[1997],[Time].[Month])} on rows from [Warehouse and Sales]
Adventure Works实例：
with member [Measures].[Accumulated Sales]
as Sum(YTD(),[Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount],[Measures].[Accumulated Sales]} on columns,
{Descendants([Date].[Calendar].[Calendar Year].&[2004],[Date].[Calendar].[Month])}
on rows
from [Adventure Works];
四则运算 函数：四则运算函数；
在成员上及指标上均可以进行四则运算，动态派生出新的成员及指标。
WITH MEMBER MEASURES.ProfitPercent AS '([Measures].[Store Sales]-[Measures].[Store Cost])/([Measures].[Store Cost])',FORMAT_STRING = '#.00%' MEMBER [Time].[First Half 97] AS '[Time].[1997].[Q1] + [Time].[1997].[Q2]' MEMBER [Time].[Second Half 97] AS '[Time].[1997].[Q3] + [Time].[1997].[Q4]' SELECT {[Time].[First Half 97], [Time].[Second Half 97], [Time].[1997].CHILDREN} ON COLUMNS, {[Store].[Store Country].[USA].CHILDREN} ON ROWS FROM [Sales] WHERE (MEASURES.ProfitPercent)
Adventure Works实例：
WITH MEMBER MEASURES.ProfitPercent AS
([Measures].[Reseller Sales Amount]-[Measures].[Reseller Average Sales Amount])/
[Measures].[Reseller Sales Amount],
FORMAT_STRING = '#.00%'
MEMBER [Date].[Calendar].[First Half 97] AS
[Date].[Calendar].[Calendar Quarter].&[2003]&[1] + [Date].[Calendar].[Calendar Quarter].&[2003]&[2]
MEMBER [Date].[Calendar].[Second Half 97] AS
[Date].[Calendar].[Calendar Quarter].&[2003]&[3] + [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
SELECT {[Date].[Calendar].[First Half 97],
[Date].[Calendar].[Second Half 97],
[Date].[Calendar].[Calendar Year].&[2003].CHILDREN} ON COLUMNS,
{[Geography].[Geography].[Country].&[United States].CHILDREN} ON ROWS
FROM [Adventure Works]
WHERE (MEASURES.ProfitPercent);
逻辑判断 函数：IIf
逻辑判断可以根据不同的条件产生不同的结果。下例判断各商店是否是啤酒及白酒的大卖家。
WITH MEMBER [Product].[BigSeller] AS 'IIf([Product].[Drink].[Alcoholic Beverages].[Beer and Wine] > 100, "Yes","No")' SELECT {[Product].[BigSeller],[Product].children} ON COLUMNS, {[Store].[All Stores].[USA].[CA].children} ON ROWS FROM Sales
成员属性 函数：Properties、Dimension Properties
成员属性是与成员绑定的，其对应关系导致很难选择合适的使用方式。以下是使用成员属性的例子，它对应每个商店成员列出了商店类型属性，相应的，商店经理、商店规模、商店地址等属性也可以被列出。该用法稍加灵活应用就可以解决过去遇到的企业名称——〉企业代码对应展示问题。
WITH MEMBER [Measures].[StoreType] AS '[Store].CurrentMember.Properties("Store Type")' MEMBER [Measures].[ProfitPct] AS '(Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales]', FORMAT_STRING = '##.00%' SELECT { Descendants([Store].[USA], [Store].[Store Name])} ON COLUMNS, {[Measures].[Store Sales], [Measures].[Store Cost], [Measures].[StoreType], [Measures].[ProfitPct] } ON ROWS FROM Sales 另外一种用法： SELECT {[Measures].[Units Shipped], [Measures].[Units Ordered]} ON COLUMNS, [Store].[Store Name].MEMBERS DIMENSION PROPERTIES [Store].[Store Name].[Store Type] ON ROWS FROM Warehouse
多步计算实现复杂逻辑 函数：其实可以是任意函数合乎逻辑的组合
求出从来没有买过乳制品的顾客，求解过程是先求出每位顾客在过去购买的乳制品的数量累计，然后找出累计值为0的顾客。同样，过去遇到的求税额大于平均税额的海关的问题可以类似求出。
with member [Measures].[Dairy ever] as 'sum([Time].members, ([Measures].[Unit Sales],[Product].[Food].[Dairy]))' set [Customers who never bought dairy] as 'filter([Customers].members, [Measures].[Dairy ever] = 0)' select {[Measures].[Unit Sales], [Measures].[Dairy ever]} on columns, [Customers who never bought dairy] on rows from Sales
Adventure Works实例：
with member x as
sum([Date].[Calendar Year].[Calendar Year],[Measures].[Internet Sales Amount])
set y as
filter([Product].[Product Categories].[Product],[Measures].[Internet Sales Amount]>10000)
select x on 0,
y on 1
from [Adventure Works];
同期、前期 函数：PrevMember、ParellelPeriod
求解各产品销售额的去年同期值，年增长率。
with member [Measures].[Store Sales Last Period] as '([Measures].[Store Sales], Time.PrevMember)', format='#,###.00' member [Measures].[Yearly Increase Rate] as '([Measures].[Store Sales] - [Measures].[Store Sales Last Period])/ [Measures].[Store Sales Last Period]', FORMAT_STRING = 'Percent' select {[Measures].[Store Sales], [Measures].[Store Sales Last Period]} on columns, { [Product].members} on rows from Sales where ([Time].[1998])
Adventure Works实例：
with member [Measures].[Store Sales Last Period]
as ([Measures].[Internet Sales Amount], [Date].[Calendar Year].PrevMember), format='#,###.00'
select {[Measures].[Store Sales Last Period]} on columns,
non empty{[Product].[Product].[Product]} on rows
from [Adventure Works]
where [Date].[Calendar Year].&[2003];
另一个例子，使用ParellelPeriod函数。
WITH MEMBER [Measures].[YTD Unit Sales] AS 'COALESCEEMPTY(SUM(YTD(), [Measures].[Unit Sales]), 0)' MEMBER [Measures].[Previous YTD Unit Sales] AS '(Measures.[YTD Unit Sales], PARALLELPERIOD([Time].[Year]))' MEMBER [Measures].[YTD Growth] AS '[Measures].[YTD Unit Sales] - ([Measures].[Previous YTD Unit Sales])' SELECT {[Time].[1998]} ON COLUMNS, {[Measures].[YTD Unit Sales], [Measures].[Previous YTD Unit Sales], [Measures].[YTD Growth]} ON ROWS FROM Sales;
Adventure Works实例：
WITH MEMBER [Measures].[YTD Unit Sales]
AS CoalesceEmpty(SUM(YTD(), [Measures].[Internet Sales Amount]), 0)
MEMBER [Measures].[Previous YTD Unit Sales]
AS (Measures.[YTD Unit Sales], ParallelPeriod([Date].[Calendar Year].[Calendar Year]))
MEMBER [Measures].[YTD Growth]
AS [Measures].[YTD Unit Sales] - ([Measures].[Previous YTD Unit Sales])
SELECT {[Date].[Calendar Year].&[2003]} ON COLUMNS,
{[Measures].[YTD Unit Sales], [Measures].[Previous YTD Unit Sales], [Measures].[YTD Growth]} ON ROWS
FROM [Adventure Works];
Top N分析 函数：TopCount, TopPercent, BottomCount, BottomPercent
求解1998年总购买量处于前5名的顾客；
select {[Measures].[Store Sales]} on columns, {TopCount([Customers].[Customer Name].members,5, [Measures].[Store Sales])} on rows from Sales where ([time].[1998])
Adventure Works实例：
select {[Measures].[Internet Sales Amount]} on columns,
{TopCount([Product].[Product].[Product],5, [Measures].[Internet Sales Amount])} on rows
from [Adventure Works]
where ([Date].[Calendar Year].&[2003]) ;
select {[Measures].[Internet Sales Amount]} on columns,
{TopPercent([Product].[Product].[Product],10, [Measures].[Internet Sales Amount])} on rows
from [Adventure Works]
where ([Date].[Calendar Year].&[2003]);
成员过滤 函数：Filter、Except
求解1998年所有顾客中购买总额得到1万元以上的顾客，列出满足条件的顾客的名字、年购买数量、年购买金额。
Select {[measures].[Store Sales],[measures].[unit sales]} on columns, FILTER(Customers.[Name].Members,[Measures].[Store Sales] > 10000) on rows From sales Where ([time].[1998]) 另外一种成员过滤（从所有的媒体类型中剔除No Media类型），确切的说应该是集合运算。 select {[Measures].[Unit Sales]} on columns, except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}) on rows from Sales
时间段 函数：sum、"："运算符
求美国的商店在指定时间段内的销售额。
WITH MEMBER [Time].[1997].[Six Month] AS 'SUM([Time].[1]:[Time].[6])' MEMBER [Time].[1997].[Nine Month] AS 'SUM([Time].[1]:[Time].[9])' SELECT {[Time].[1997].[Six Month],[Time].[1997].[Nine Month]} ON COLUMNS, {[measures].[store salse]} ON ROWS FROM Sales Where ([Store].[USA])
分析各城市的销售所占全部城市的总销售额百分比。
WITH MEMBER Measures.[Unit Sales Percent] AS '((Store.CURRENTMEMBER, Measures.[Unit Sales]) / (Store.CURRENTMEMBER.PARENT, Measures.[Unit Sales])) ', FORMAT_STRING = 'Percent' SELECT {Measures.[Unit Sales], Measures.[Unit Sales Percent]} ON COLUMNS, ORDER(DESCENDANTS(Store.[USA].[CA], Store.[Store City], SELF),[Measures].[Unit Sales], ASC) ON ROWS FROM Sales;
Adventure Works实例：
WITH MEMBER Measures.[Unit Sales Percent]
AS ([Geography].[Geography].CURRENTMEMBER, [Measures].[Reseller Sales Amount]) /
([Geography].[Geography].CURRENTMEMBER.PARENT, [Measures].[Reseller Sales Amount])
, FORMAT_STRING = 'Percent'
SELECT {[Measures].[Reseller Sales Amount], Measures.[Unit Sales Percent]} ON COLUMNS,
non empty{
ORDER(
DESCENDANTS([Geography].[Geography].[State-Province].&[NSW]&[AU],
[Geography].[Geography].[City], SELF),
[Measures].[Reseller Sales Amount]
,ASC)
} ON ROWS
FROM [Adventure Works];
重要顾客分布分析 函数：Count、Sum、Filter、Descendants等；
分析各个省份中重要顾客的数量及他们的总购买量，"重要顾客"的定义是一个顾客的购买金额或者购买数目达到或超过一定的数值。
WITH MEMBER [Measures].[Qualified Count] AS 'COUNT(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10))' MEMBER [Measures].[Qualified Sales] AS 'SUM(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10), ([Measures].[Store Sales]))' SELECT {[Measures].[Qualified Count], [Measures].[Qualified Sales]} ON COLUMNS, DESCENDANTS([Customers].[All Customers], [State Province], SELF_AND_BEFORE) ON ROWS FROM Sales
Adventure Works实例：
WITH MEMBER [Measures].[Qualified Count] AS
COUNT(
FILTER(
DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount] > 1000 OR [Measures].[Internet Order Count] > 10)
)
MEMBER [Measures].[Qualified Sales] AS
SUM(
FILTER(
DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount] > 1000 OR [Measures].[Internet Order Count] > 10)
,[Measures].[Internet Sales Amount]
)
SELECT {[Measures].[Qualified Count],
[Measures].[Qualified Sales]} ON COLUMNS,
DESCENDANTS([Customer].[Customer Geography],
[Customer].[Customer Geography].[State-Province], SELF_AND_BEFORE)
ON ROWS
FROM [Adventure Works];
排序 函数：Order
对各个产品类别按照Store Sales指标降序排列，排序分为维内排序/整体排序。
select {[Measures].[Unit Sales], [Measures].[Store Sales]} on columns, Order([Product].[Product Department].members, [Measures].[Store Sales], DESC) on rows from Sales
历史相关的累计值 函数：YTD、Sum、Descendants
求销售额的本年累计值YTD()，类似还可以求解历史累计YTD()、本月累计MTD()、本周累计WTD()等, 以及更通用的函数PeriodToDate()。
with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])' select {[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns, {Descendants([Time].[1997],[Time].[Month])} on rows from [Warehouse and Sales]
Adventure Works实例：
with member [Measures].[Accumulated Sales]
as Sum(YTD(),[Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount],[Measures].[Accumulated Sales]} on columns,
{Descendants([Date].[Calendar].[Calendar Year].&[2004],[Date].[Calendar].[Month])}
on rows
from [Adventure Works];
四则运算 函数：四则运算函数；
在成员上及指标上均可以进行四则运算，动态派生出新的成员及指标。
WITH MEMBER MEASURES.ProfitPercent AS '([Measures].[Store Sales]-[Measures].[Store Cost])/([Measures].[Store Cost])',FORMAT_STRING = '#.00%' MEMBER [Time].[First Half 97] AS '[Time].[1997].[Q1] + [Time].[1997].[Q2]' MEMBER [Time].[Second Half 97] AS '[Time].[1997].[Q3] + [Time].[1997].[Q4]' SELECT {[Time].[First Half 97], [Time].[Second Half 97], [Time].[1997].CHILDREN} ON COLUMNS, {[Store].[Store Country].[USA].CHILDREN} ON ROWS FROM [Sales] WHERE (MEASURES.ProfitPercent)
Adventure Works实例：
WITH MEMBER MEASURES.ProfitPercent AS
([Measures].[Reseller Sales Amount]-[Measures].[Reseller Average Sales Amount])/
[Measures].[Reseller Sales Amount],
FORMAT_STRING = '#.00%'
MEMBER [Date].[Calendar].[First Half 97] AS
[Date].[Calendar].[Calendar Quarter].&[2003]&[1] + [Date].[Calendar].[Calendar Quarter].&[2003]&[2]
MEMBER [Date].[Calendar].[Second Half 97] AS
[Date].[Calendar].[Calendar Quarter].&[2003]&[3] + [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
SELECT {[Date].[Calendar].[First Half 97],
[Date].[Calendar].[Second Half 97],
[Date].[Calendar].[Calendar Year].&[2003].CHILDREN} ON COLUMNS,
{[Geography].[Geography].[Country].&[United States].CHILDREN} ON ROWS
FROM [Adventure Works]
WHERE (MEASURES.ProfitPercent);
逻辑判断 函数：IIf
逻辑判断可以根据不同的条件产生不同的结果。下例判断各商店是否是啤酒及白酒的大卖家。
WITH MEMBER [Product].[BigSeller] AS 'IIf([Product].[Drink].[Alcoholic Beverages].[Beer and Wine] > 100, "Yes","No")' SELECT {[Product].[BigSeller],[Product].children} ON COLUMNS, {[Store].[All Stores].[USA].[CA].children} ON ROWS FROM Sales
成员属性 函数：Properties、Dimension Properties
成员属性是与成员绑定的，其对应关系导致很难选择合适的使用方式。以下是使用成员属性的例子，它对应每个商店成员列出了商店类型属性，相应的，商店经理、商店规模、商店地址等属性也可以被列出。该用法稍加灵活应用就可以解决过去遇到的企业名称——〉企业代码对应展示问题。
WITH MEMBER [Measures].[StoreType] AS '[Store].CurrentMember.Properties("Store Type")' MEMBER [Measures].[ProfitPct] AS '(Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales]', FORMAT_STRING = '##.00%' SELECT { Descendants([Store].[USA], [Store].[Store Name])} ON COLUMNS, {[Measures].[Store Sales], [Measures].[Store Cost], [Measures].[StoreType], [Measures].[ProfitPct] } ON ROWS FROM Sales 另外一种用法： SELECT {[Measures].[Units Shipped], [Measures].[Units Ordered]} ON COLUMNS, [Store].[Store Name].MEMBERS DIMENSION PROPERTIES [Store].[Store Name].[Store Type] ON ROWS FROM Warehouse
多步计算实现复杂逻辑 函数：其实可以是任意函数合乎逻辑的组合
求出从来没有买过乳制品的顾客，求解过程是先求出每位顾客在过去购买的乳制品的数量累计，然后找出累计值为0的顾客。同样，过去遇到的求税额大于平均税额的海关的问题可以类似求出。
with member [Measures].[Dairy ever] as 'sum([Time].members, ([Measures].[Unit Sales],[Product].[Food].[Dairy]))' set [Customers who never bought dairy] as 'filter([Customers].members, [Measures].[Dairy ever] = 0)' select {[Measures].[Unit Sales], [Measures].[Dairy ever]} on columns, [Customers who never bought dairy] on rows from Sales
Adventure Works实例：
with member x as
sum([Date].[Calendar Year].[Calendar Year],[Measures].[Internet Sales Amount])
set y as
filter([Product].[Product Categories].[Product],[Measures].[Internet Sales Amount]>10000)
select x on 0,
y on 1
from [Adventure Works];
同期、前期 函数：PrevMember、ParellelPeriod
求解各产品销售额的去年同期值，年增长率。
with member [Measures].[Store Sales Last Period] as '([Measures].[Store Sales], Time.PrevMember)', format='#,###.00' member [Measures].[Yearly Increase Rate] as '([Measures].[Store Sales] - [Measures].[Store Sales Last Period])/ [Measures].[Store Sales Last Period]', FORMAT_STRING = 'Percent' select {[Measures].[Store Sales], [Measures].[Store Sales Last Period]} on columns, { [Product].members} on rows from Sales where ([Time].[1998])
Adventure Works实例：
with member [Measures].[Store Sales Last Period]
as ([Measures].[Internet Sales Amount], [Date].[Calendar Year].PrevMember), format='#,###.00'
select {[Measures].[Store Sales Last Period]} on columns,
non empty{[Product].[Product].[Product]} on rows
from [Adventure Works]
where [Date].[Calendar Year].&[2003];
另一个例子，使用ParellelPeriod函数。
WITH MEMBER [Measures].[YTD Unit Sales] AS 'COALESCEEMPTY(SUM(YTD(), [Measures].[Unit Sales]), 0)' MEMBER [Measures].[Previous YTD Unit Sales] AS '(Measures.[YTD Unit Sales], PARALLELPERIOD([Time].[Year]))' MEMBER [Measures].[YTD Growth] AS '[Measures].[YTD Unit Sales] - ([Measures].[Previous YTD Unit Sales])' SELECT {[Time].[1998]} ON COLUMNS, {[Measures].[YTD Unit Sales], [Measures].[Previous YTD Unit Sales], [Measures].[YTD Growth]} ON ROWS FROM Sales;
Adventure Works实例：
WITH MEMBER [Measures].[YTD Unit Sales]
AS CoalesceEmpty(SUM(YTD(), [Measures].[Internet Sales Amount]), 0)
MEMBER [Measures].[Previous YTD Unit Sales]
AS (Measures.[YTD Unit Sales], ParallelPeriod([Date].[Calendar Year].[Calendar Year]))
MEMBER [Measures].[YTD Growth]
AS [Measures].[YTD Unit Sales] - ([Measures].[Previous YTD Unit Sales])
SELECT {[Date].[Calendar Year].&[2003]} ON COLUMNS,
{[Measures].[YTD Unit Sales], [Measures].[Previous YTD Unit Sales], [Measures].[YTD Growth]} ON ROWS
FROM [Adventure Works];
Top N分析 函数：TopCount, TopPercent, BottomCount, BottomPercent
求解1998年总购买量处于前5名的顾客；
select {[Measures].[Store Sales]} on columns, {TopCount([Customers].[Customer Name].members,5, [Measures].[Store Sales])} on rows from Sales where ([time].[1998])
Adventure Works实例：
select {[Measures].[Internet Sales Amount]} on columns,
{TopCount([Product].[Product].[Product],5, [Measures].[Internet Sales Amount])} on rows
from [Adventure Works]
where ([Date].[Calendar Year].&[2003]) ;
select {[Measures].[Internet Sales Amount]} on columns,
{TopPercent([Product].[Product].[Product],10, [Measures].[Internet Sales Amount])} on rows
from [Adventure Works]
where ([Date].[Calendar Year].&[2003]);
成员过滤 函数：Filter、Except
求解1998年所有顾客中购买总额得到1万元以上的顾客，列出满足条件的顾客的名字、年购买数量、年购买金额。
Select {[measures].[Store Sales],[measures].[unit sales]} on columns, FILTER(Customers.[Name].Members,[Measures].[Store Sales] > 10000) on rows From sales Where ([time].[1998]) 另外一种成员过滤（从所有的媒体类型中剔除No Media类型），确切的说应该是集合运算。 select {[Measures].[Unit Sales]} on columns, except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}) on rows from Sales
时间段 函数：sum、"："运算符
求美国的商店在指定时间段内的销售额。
WITH MEMBER [Time].[1997].[Six Month] AS 'SUM([Time].[1]:[Time].[6])' MEMBER [Time].[1997].[Nine Month] AS 'SUM([Time].[1]:[Time].[9])' SELECT {[Time].[1997].[Six Month],[Time].[1997].[Nine Month]} ON COLUMNS, {[measures].[store salse]} ON ROWS FROM Sales Where ([Store].[USA])
