MDX 函数的使用介绍

最新推荐文章于 2024-08-20 08:51:15 发布

michaelgy

最新推荐文章于 2024-08-20 08:51:15 发布

阅读量5.6k

点赞数

文章标签：数据库 sqlserver hierarchy filter properties 数据挖掘

根据SqlServer2000 Analysis Services提供的帮助材料展开，略作说明并且根据各个函数的侧重点编写相应的MDX；这些成果主要源于我的老大浩哥，再次向他表示感谢。

数组函数

函数
描述

SetToArray
将一个或多个集合转换为数组，以用于用户定义函数中。

维度函数、层次结构函数和级别函数

维度函数

函数
描述

Dimension
返回包含指定的层次结构、级别或成员的维度。

with member [measures].[abc] as '[Time].currentmember.Dimension.name'

SELECT { [Time].&[1997] } ON COLUMNS , { [Product].[All Products] } ON ROWS FROM [Sales] WHERE ( [measures].[abc])

Dimensions
返回多维数据集中基于零的位置是由数值表达式指定的维度，或者其名称是由字符串指定的维度。

with member [measures].[abc] as 'Dimensions("[Time]").name'

SELECT { [Time].&[1997] } ON COLUMNS , { [Product].[All Products] } ON ROWS FROM [Sales] WHERE ( [measures].[abc])

层次结构函数

函数
描述

Hierarchy
返回级别或成员的层次结构。

级别函数

函数
描述

Level
返回成员的级别。

with member [measures].[abc] as ' [Time].&[1997].&[q1].level.name'

SELECT { [Time].&[1997] } ON COLUMNS , { [Product].[All Products] } ON ROWS FROM [Sales] WHERE ( [measures].[abc])

Levels
返回维度中其位置是由数值表达式指定的级别，或者其名称是由字符串表达式指定的级别。

with member [measures].[abc] as 'Levels([time].currentmember.level.name).name'

SELECT { [Time].&[1997], [Time].&[1997].&[Q1], [Time].&[1997].&[Q1].&[1], [Time].&[1997].&[Q1].&[2], [Time].&[1997].&[Q1].&[3] } ON COLUMNS , { [Product].[All Products] } ON ROWS FROM [Sales]

WHERE ( [measures].[abc])

逻辑函数

函数
描述

Is
如果两个相比较的对象相等，则返回 True，否则返回 False。

with member [measures].[abc] as '[time].currentmember.level is quarter'

SELECT { [Time].allmembers} ON COLUMNS , { [Product].[All Products] } ON ROWS FROM [Sales]

WHERE ( [measures].[abc])

IsAncestor
确定指定的成员是否为另一个指定成员的祖先。

with member [measures].[abc] as 'IsAncestor([Time].CurrentMember, [Time].[1997].[Q2].[4])'

SELECT { [Time].allmembers} ON COLUMNS , { [Product].[All Products] } ON ROWS FROM [Sales]

WHERE ( [measures].[abc])

IsEmpty
确定表达式是否取值为空单元值。

with member [measures].[abc] as ' IsEmpty([Measures].[Unit Sales]) '

SELECT { DESCENDANTS( [Time].&[1997], [Time].[Month] )*{[measures].[abc],[Measures].[Unit Sales]} } ON COLUMNS , { DESCENDANTS( [Product].[Product Family].&[Food], [Product].[Product Name] ) } ON ROWS FROM [Sales]

IsGeneration
确定指定成员是否位于指定的代中。

with member [measures].[a1] as ' IsGeneration([Time].CurrentMember,0) '

member [measures].[a2] as ' IsGeneration([Time].CurrentMember,1) '

member [measures].[a3] as ' IsGeneration([Time].CurrentMember,2) '

SELECT {[measures].[a1],[measures].[a2],[measures].[a3]} ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

IsLeaf
确定指定成员是否为叶成员。

with member [measures].[a1] as ' IsLeaf([Time].CurrentMember) '

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

IsSibling
确定指定成员是否为另一个指定成员的兄弟。

with member [measures].[a1] as ' IsSibling([Time].currentmember.prevmember,[Time].currentmember)'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

成员函数

函数
描述

Ancestor
返回指定级别或距成员指定距离的成员的祖先。

with member [measures].[a1] as 'Ancestor([Time].currentmember, 1).name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

ClosingPeriod
返回成员在一个级别上的后代中的最后一个兄弟。

with member [measures].[a1] as 'ClosingPeriod( [Time].[Month], [Time].currentmember) .name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

Cousin
返回成员下方与指定的成员具有相同的相对位置的成员。

with member [measures].[a1] as 'Cousin([time].currentmember, [time].[1998] ).uniquename'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

CurrentMember
返回迭代过程中维度上的当前成员。

with member [measures].[a1] as ' [time].currentmember.uniquename'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

DataMember
返回与非叶成员关联的系统生成的数据成员。

1
with member [measures].[a1] as ' ([time].currentmember.datamember, [Measures].[Store Sales])'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

2
with member [measures].[a1] as ' ([Employees].currentmember.datamember, [Measures].[Org Salary])'

SELECT { [Time].allmembers*{[measures].[a1],[Measures].[Org Salary]} } ON COLUMNS ,

{ [Employees].allmembers } ON ROWS

FROM [HR]

备注：
当非叶子节点存在相应的数据库里的记录，那么会显示数据库里该节点自己对应的值，如果不存在相应的数据库里的记录，那么会显示聚合出来的值；

DefaultMember
返回维度或层次结构的默认成员。

with member [measures].[a1] as ' [time].DefaultMember.name '

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

FirstChild
返回成员的第一个子代。

with member [measures].[a1] as ' [time].currentmember.firstchild.name '

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

FirstSibling
返回成员的父代的第一个子代。

with member [measures].[a1] as ' [time].currentmember.FirstSibling.name '

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

Ignore
保留。

Item
从元组中返回成员。

with member [measures].[a1] as '{time.currentmember.children}.Item(0).item(0).name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

Lag
返回指定成员的维度上的上一个成员。

with member [measures].[a1] as 'time.currentmember.lag(1).name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

LastChild
返回成员的最后一个子代。

with member [measures].[a1] as 'time.currentmember.lastchild.name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

LastSibling
返回成员的父代的最后一个子代。

with member [measures].[a1] as 'time.currentmember.LastSibling.name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

Lead
返回指定成员的维度上后面的成员。

with member [measures].[a1] as 'time.currentmember.Lead(1).name'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

LinkMember
返回按层次结构排列的成员。

with member [measures].[a1] as 'LinkMember([time].[1997].[q1].[1], 其它时间维度名称)'

SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

Members
返回其名称由字符串表达式指定的成员。

with member [measures].[a1] as '111'

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

NextMember
返回指定成员所在级别的下一个成员。

with member [measures].[a1] as 'time.currentmember.nextmember.name'

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

OpeningPeriod
返回某一级别上成员的后代中的第一个兄弟。

with member [measures].[a1] as 'OpeningPeriod(Month, time.currentmember).name '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

ParallelPeriod
返回上一时期中与指定成员具有相同的相对位置的成员。

1
with member [measures].[a1] as 'ParallelPeriod(Year).uniquename '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

2
with member [measures].[a1] as 'ParallelPeriod(Year,1,time.currentmember).uniquename '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

3
with member [measures].[a1] as 'ParallelPeriod(month,1,time.currentmember).uniquename '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

Parent
返回成员的父代。

with member [measures].[a1] as ' time.currentmember.parent.uniquename '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

PrevMember
返回指定成员所在级别的上一个成员。

with member [measures].[a1] as ' time.currentmember.PrevMember.uniquename '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

StrToMember
根据字符串表达式返回成员。

with member [measures].[a1] as ' strtomember(time.currentmember.uniquename).uniquename '

SELECT {[measures].[a1] } ON COLUMNS, { [Time].members } ON ROWS FROM [Sales]

ValidMeasure
通过将不适用的维度强制到其顶层，来返回虚拟多维数据集中的有效度量值。

with member measures.[abc] as ' ValidMeasure([Measures].[Warehouse Sales]) '

SELECT {measures.[abc],[Measures].[Store Sales],[Measures].[Warehouse Sales] } ON COLUMNS ,

{DESCENDANTS( [Customers].[State Province].&[CA].&[Altadena], [Customers].[Name] ) } ON ROWS FROM [Warehouse and Sales]

MDX 函数的使用介绍(二)：数值函数
数值函数

函数
描述

Aggregate
返回根据查询的上下文，用适当的聚合函数计算所得的值。

WITH MEMBER store.Total AS 'AGGREGATE({[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR]})'

SELECT {Measures.[store sales], Measures.MaxSales} ON COLUMNS,

{[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR], Total} ON ROWS

FROM Sales

比较

WITH MEMBER store.Total AS 'sum({[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR]})'

SELECT {Measures.[store sales], Measures.MaxSales} ON COLUMNS,

{[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR], Total} ON ROWS

FROM Sales

Avg
返回在某一集合上对数值表达式求得的平均值。

WITH MEMBER store.Total AS 'Avg({[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR]})'

SELECT {Measures.[store sales], Measures.MaxSales} ON COLUMNS,

{[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR], Total} ON ROWS

FROM Sales

CalculationCurrentPass
针对当前查询上下文返回多维数据集的当前计算传递。

with member [Time].[1997].[Q2-Q1] as '[Time].[1997].[Q2] - [Time].[1997].[Q1]', SOLVE_ORDER=1

SELECT{ {Filter

({{[Time].[1997].[Q1], [Time].[1997].[Q2], [Time].[1997].[Q2-Q1] }}*{

[Measures].[Sales Average]},

([Time].[1997].[Q1], [Measures].[Store Sales])>0) } }

ON COLUMNS , {[Store].[Store Country].members } ON ROWS FROM Sales

比较如果不加, SOLVE_ORDER=1会怎样？

CalculationPassValue
返回在当前多维数据集的指定计算传递上对 MDX 表达式求得的值。

CoalesceEmpty
将空单元值合并为数字或字符串。

with member [Measures].[abc] as 'iif(CoalesceEmpty( [Measures].[Store Sales],1)=1 ,"空的","不空的")', SOLVE_ORDER=1

SELECT{{{[Time].[1997].[Q1], [Time].[1997].[Q2] }}*{

[Measures].[Store Sales],[Measures].[abc]}}

ON COLUMNS , {[Store].[Store Country].members } ON ROWS FROM Sales

Correlation
返回在某一集合上对两个系列求得的相关性。

Count
返回多维数据集中的维度数、维度中的级别数、集合中的单元数或元组中的维度数。

with member [measures].[abc] as 'Descendants([Store].currentmember,0, AFTER).count'

SELECT{{{[Time].[1997].[Q1], [Time].[1997].[Q2] }}*{

[Measures].[Store Sales],[measures].[abc] }}

ON COLUMNS , {[Store].[Store Country].members } ON ROWS FROM Sales

Covariance
返回使用偏置填充公式在某一集合上对两个系列求得的总体协方差。

CovarianceN
返回使用非偏置填充公式在某一集合上对两个系列求得的样本协方差。

DistinctCount
返回集合中的元组数，不包括重复的元组。

WITH MEMBER Measures.a as 'DistinctCount( DESCENDANTS( [Product].[Product Family].&[Drink].&[Alcoholic Beverages].&[Beer and Wine].&[Wine].&[Good], [Product].[Product Name] ))'

SELECT

{ { [Time].&[1997].&[Q1].&[2] } * { Measures.a, [Measures].[Store Sales], [Measures].[Store Sales Net], [Measures].[Unit Sales] } } ON COLUMNS ,

{ { [Store].[Store Name].&[6] } * { DESCENDANTS( [Product].[Product Family].&[Drink].&[Alcoholic Beverages].&[Beer and Wine].&[Wine].&[Good], [Product].[Product Name] ) } } ON ROWS

FROM [Sales]

IIf
返回由逻辑测试确定的两个数值或字符串值之一。

with member measures.abc as 'iif(isempty(measures.[unit sales]),"空了","不空")'

SELECT { { { [Time].&[1997] } * { [Measures].[Unit Sales],measures.abc } } } ON COLUMNS , { DESCENDANTS( [Store].[All Stores], [Store].[Store Name] ) } ON ROWS FROM [Sales]

LinRegIntercept
对集合进行线性回归，并返回回归线公式 y = ax + b 中 b 的值。

LinRegPoint
对集合进行线性回归，并返回回归线公式 y = ax + b 中 y 的值。

LinRegR2
对集合进行线性回归，并返回 R2（确定系数）。

LinRegSlope
对集合进行线性回归，并返回回归线公式 y = ax + b 中 a 的值。

LinRegVariance
对集合进行线性回归，并返回与回归线公式 y = ax + b 相关的方差。

LookupCube
返回在同一数据库中另外一个指定的多维数据集上对 MDX 表达式求得的值。

with member measures.abc as 'LookupCube("Warehouse","("+time.currentmember.uniquename+",measures.[warehouse sales])")'

SELECT { [Measures].[Unit Sales],measures.abc } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales]

WITH MEMBER

Measures.[Store Unit Sales]

'LookupCube(

"Sales",

"(" + MemberToStr(Store.CurrentMember) + ", Measures.[Unit Sales])"

SELECT

{Measures.Amount, Measures.[Store Unit Sales]} ON COLUMNS,

Store.CA.CHILDREN ON ROWS

FROM

Budget

Max
返回在某一集合上对数值表达式求得的最大值。

with member measures.abc as ' max(Descendants([Time].currentmember,quarter),[unit sales]) ' SELECT { [Measures].[Unit Sales],measures.abc } ON COLUMNS , { [Time].[1997],[Time].[1998] } ON ROWS FROM [Sales]

Median
返回在某一集合上对数值表达式求得的中值。

with member measures.mid as ' Median(Descendants([Time].currentmember,quarter),[unit sales]) '

member measures.[avg] as ' avg(Descendants([Time].currentmember,quarter),[unit sales]) '

SELECT { [Measures].[Unit Sales],measures.mid,measures.[avg] } ON COLUMNS , { [Time].[1997],[Time].[1998] } ON ROWS FROM [Sales]

Min
返回在某一集合上对数值表达式求得的最小值。

with member measures.min as ' min(Descendants([Time].currentmember,quarter),[unit sales]) ' SELECT { [Measures].[Unit Sales],measures.[min] } ON COLUMNS , { [Time].[1997],[Time].[1998] } ON ROWS FROM [Sales]

Ordinal
返回级别的以零为基的序数值。

with member measures.abc as ' [Time].currentmember.level.Ordinal'

SELECT { [Measures].[Unit Sales],measures.[abc] } ON COLUMNS , { [Time].allmembers} ON ROWS FROM [Sales]

Predict
计算当前坐标中指定的数据挖掘模型中的字符串表达式。

Rank
以一为基返回元组在集合中的阶。

with member measures.abc as

'Rank(([Time].[1997].[Q1].[2],[Measures].[Sales Count]),

{([Time].[1997].[Q1].[1],[Measures].[Store Cost]),

([Time].[1997].[Q1].[2],[Measures].[Sales Count]),

([Time].[1997].[Q1].[3],[Measures].[Profit])}

SELECT { [Measures].[Unit Sales],measures.[abc] } ON COLUMNS , { [Time].[1997] } ON ROWS FROM [Sales]

RollupChildren
扫描成员参数的子代并将字符串表达式运算符应用于计算所得的值。

with member measures.abc as

'RollupChildren([Accounttest].CurrentMember, [Accounttest].CurrentMember.Properties("AccountRollup"))'

member measures.abcd as '[Accounttest].CurrentMember.Properties("AccountRollup")'

SELECT { {[Time].&[1997] }*{ [Measures].[Amount],measures.abc ,measures.abcd} } ON COLUMNS ,

{ Descendants([Accounttest].[All Account],1,after) } ON ROWS FROM [Budgettest]

Stddev
Stdev 的别名。

StddevP
StdevP 的别名。

Stdev
返回使用非偏置填充公式对集合计算数值表达式所得的样本标准偏差。

StdevP
返回使用偏置填充公式对集合计算数值表达式所得的填充标准偏差。

StrToValue
根据字符串表达式返回值。

with member measures.abc as ' strtovalue("555")'

SELECT { [Measures].[Unit Sales],measures.[abc] } ON COLUMNS , { [Time].allmembers} ON ROWS FROM [Sales]

Sum
返回在某一集合上对数值表达式求得的和。

with member measures.abc as 'SUM( [Time].currentmember.children,[Measures].[Unit Sales])'

SELECT { [Measures].[Unit Sales],measures.[abc] } ON COLUMNS , { [Time].allmembers} ON ROWS FROM [Sales]

Value
返回度量值的值。

没什么用处，未成功

Var
返回使用无偏置填充公式在某一集合上对数值表达式求得的样本方差。

Variance
Var 的别名。

VarianceP
VarP 的别名。

VarP
返回使用偏置填充公式对集合计算数值表达式的总体方差。

MDX函数使用介绍（三）：聚合函数
聚合函数

函数
描述

AddCalculatedMembers
向集合中添加计算成员。

WITH MEMBER [Time].[1997].[1到6月的] AS 'SUM([Time].[1]:[Time].[6])'

MEMBER [Time].[1997].[1到9月的] AS 'SUM([Time].[1]:[Time].[9])' SELECT

AddCalculatedMembers([Time].[1997].Children) ON COLUMNS, [Product].Children ON rows from sales

AllMembers
返回包含指定维度或级别的所有成员的集合，包括计算成员。

SELECT

{[Product].Children} ON COLUMNS, [Time].allmembers ON rows from sales

Ancestors
返回指定距离上某个成员的所有祖先。

1
with member [measures].[1] as 'settostr(Ancestors(time.currentmember,[Time].[Year]) ) '

member [measures].[2] as 'settostr(Ancestors(time.currentmember,1) )'

member [measures].[3] as 'settostr(Ancestors(time.currentmember,2) )'

SELECT

{ [Measures].[Unit Sales],[measures].[1],[measures].[2],[measures].[3] } ON COLUMNS, [Time].allmembers ON rows from sales

2
with member [measures].[1] as 'sum(Ancestors(time.currentmember,[Time].[Year]),[Measures].[Unit Sales]) '

member [measures].[2] as 'sum(Ancestors(time.currentmember,1),[Measures].[Unit Sales])'

member [measures].[3] as 'sum(Ancestors(time.currentmember,2),[Measures].[Unit Sales])'

SELECT

{ [Measures].[Unit Sales],[measures].[1],[measures].[2],[measures].[3] } ON COLUMNS, [Time].allmembers ON rows from sales

Ascendants
返回成员祖先的集合，包括成员本身。

1
with member [measures].[1] as 'settostr(Ascendants(time.currentmember ) ) '

SELECT

{ [Measures].[Unit Sales],[measures].[1] } ON COLUMNS, [Time].allmembers ON rows from sales

2
with member [measures].[1] as 'sum(Ascendants(time.currentmember ),[Measures].[Unit Sales]) '

SELECT

{ [Measures].[Unit Sales],[measures].[1] } ON COLUMNS, [Time].allmembers ON rows from sales

Axis
返回与主轴关联的集合。

with member [measures].[COLUMNS] as 'settostr(Axis(1)) '

member [measures].[rows] as 'settostr(Axis(1)) '

SELECT

{[measures].[rows],[measures].[COLUMNS] } ON COLUMNS, [Time].allmembers ON rows from sales

BottomCount
从集合底端开始返回指定数目的项，可以选择首先对集合排序。

SELECT

{[Measures].[Store Sales] } ON COLUMNS,

BottomCount(Descendants([Store],[Store].[Store Name]),20,[Measures].[Store Sales] ) ON rows from sales

BottomPercent
对集合排序，并返回底端的 n 个元素，这些元素的累积合计至少为指定的百分比。

select {[Unit Sales]} on COLUMNS,

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

from Sales

BottomSum
对集合排序，并返回底端的 n 个元素，这些元素的累积合计至少为指定的值。

select {[Unit Sales]} on COLUMNS,

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

from Sales

Children
返回成员的子代。

with member [measures].[CurrentChildren] as 'settostr(time.currentmember.Children)'

select {[Unit Sales],[measures].[CurrentChildren]} on COLUMNS,

{[time].allmembers} on ROWS

from Sales

Crossjoin
返回两个集合的矢量积。

替代语法

«Set1» * «Set2»

select {time.allmembers*{[Unit Sales],[store sales]} } on COLUMNS,

{[Store].[Store Name].members} on ROWS

from Sales

或者

select Crossjoin({time.allmembers},{[Unit Sales],[store sales]}) on COLUMNS,

{[Store].[Store Name].members} on ROWS

from Sales

Descendants
返回某一成员在指定级别上的或者距某一成员指定距离的后代集合，可以选择包含或不包含其它级别上的后代。

Descendants([Time].[1997])
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997])} ON rows from sales

Descendants

([Time].[1997],[time].[month])
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].month)} ON rows from sales

Descendants

([Time].[1997],

[time].[ month],

SELF)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].[month],SELF)} ON rows from sales

Descendants

([Time].[1997],

[time].[month],

before)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].[month],before)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

AFTER)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],AFTER)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

BEFORE_AND_AFTER)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],BEFORE_AND_AFTER)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

SELF_BEFORE_AFTER)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],SELF_BEFORE_AFTER)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

LEAVES)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],LEAVES)} ON rows from sales

Descendants

([Time].[1997],1)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],1)} ON rows from sales

Descendants

([Time].[1997],2,

SELF_BEFORE_AFTER)
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{Descendants([Time].[1997],2,SELF_BEFORE_AFTER)} ON rows from sales

Distinct
从集合中删除重复的元组。

SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ distinct({

([Time].[1997],[Store].[All Stores].[USA]),

([Time].[1997],[Store].[All Stores].[Mexico]),

([Time].[1997],[Store].[All Stores].[USA])

})} ON rows from sales

对比
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{{

([Time].[1997],[Store].[All Stores].[USA]),

([Time].[1997],[Store].[All Stores].[Mexico]),

([Time].[1997],[Store].[All Stores].[USA])

} } ON rows from sales

DrilldownLevel
将集合中的成员从指定级别深化一个级别。

另一种方法是，在集合中的指定维度上深化。

SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownLevel({[Time].[1997],[Time].[1997].[Q1],

[Time].[1997].[Q3],[Time].[1998]}) } ON rows from sales

time.month

和

time.quarter

效果对比
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownLevel({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],

[Time].[1998]},

time.quarter

) } ON rows from sales

DrilldownLevelBottom
将集合底端的 n 个成员从指定级别深化一个级别。

SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownLevelBottom({[Time].[1997],[Time].[1997].[Q1],

[Time].[1997].[Q3],[Time].[1998]}

,2,,[Store Sales]

) } ON rows from sales

有意思，分析一下结果
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownLevelBottom({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],[Time].[1998]}

,5,time.year,[Store Sales]

) } ON rows from sales

DrilldownLevelTop
将集合顶端的 n 个成员从指定级别深化一个级别。

SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownLevelTop({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],[Time].[1998]}

,2, ,[Store Sales]

) } ON rows from sales

DrilldownMember
在第一个集合与第二个集合的交集中深化。

1
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownMember({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]}

,{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],[Store].[All Stores].[Mexico] }

) } ON rows from sales

注意第2个set的写法
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownMember({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]}

,{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],

[Store].[All Stores].[Canada],[Store].[All Stores].[Canada].[BC],

[Store].[All Stores].[Mexico] } ,RECURSIVE

) } ON rows from sales

2
SELECT

{[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownMember({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]}

,{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],[Store].[All Stores].[Mexico] },RECURSIVE

) } ON rows from sales

备注
帮助中有个错误的地方：

……示例

DrilldownMember({USA, Canada, Mexico}, {USA, Washington, Mexico},RECURSIVE)

返回集合：

{USA, <all states in USA before Washington>,

WA, <all cities in Washington>, <all cities（应该是states） in USA after Washington>,

Canada, Mexico, <all states in Mexico>}……..

3
SELECT

{time.[1997] } ON COLUMNS,

{ DrilldownMember(

{

([Store].[All Stores].[USA],[Measures].[Store Sales]),

([Store].[All Stores].[Canada],[Measures].[Store Sales]),

([Store].[All Stores].[Mexico],[Measures].[Store Sales])

}

,{[Store].[All Stores].[USA],[Store].[All Stores].[USA].[wa]}

) } ON rows from sales

4

加RECURSIVE
SELECT

{time.[1997] } ON COLUMNS,

{ DrilldownMember(

{

([Store].[All Stores].[USA],[Measures].[Store Sales]),

([Store].[All Stores].[Canada],[Measures].[Store Sales]),

([Store].[All Stores].[Mexico],[Measures].[Store Sales])

}

,{[Store].[All Stores].[USA],[Store].[All Stores].[USA].[wa]}

,RECURSIVE) } ON rows from sales

DrilldownMemberBottom
类似于 DrilldownMember，除了只包括底端的 n 个子代。

1，RECURSIVE 含义同上，且递归部分的成员同样受count控制
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ DrilldownMemberBottom({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]} ,

{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],[Store].[All Stores].[Mexico] },

2,[Measures].[Store Sales],RECURSIVE

) }

ON rows from sales

对元组的深化同DrilldownMember

DrilldownMemberTop
类似于 DrilldownMember，除了只包括顶端的 n 个子代。

同DrilldownMemberBottom

DrillupLevel
从集合的某一指定级别之下的成员浅化。

1
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ DrillUpLevel({USA, Ca, [Los Angeles], Wa , Seattle, Canada, [BC]},[store country])

} ON rows from sales

2
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ DrillUpLevel({USA, Ca, [Los Angeles], Wa , Seattle, Canada, [BC]})

}

ON rows from sales

DrillupMember
在第一个集合与第二个集合的交集中浅化。

SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ DrillupMember({Canada, Mexico, USA, Wa, Seattle},{Wa})

} ON rows from sales

Except
查找两个集合之间不同的项，可以选择保留重复项。

1
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ Except({Canada, [BC], Mexico, [BC], USA, Wa}, {Canada, Mexico, Ca})

}

ON rows from sales

2，呵呵，普科显示的也有点问题
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ Except({Canada, [BC], Mexico, [BC], USA, Wa}, {Canada, Mexico, Ca},all)

}

ON rows from sales

Extract
从析取的维度元素中返回元组集合。即 Crossjoin 的反运算。

Extract 函数执行的操作与 Crossjoin 函数相反
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ Extract({([1997], Wa ), ([1997], Ca ), ([1998], Ca )}, Time)

} ON rows from sales

Filter
返回根据搜索条件对集合进行筛选所得到的集合。

SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ filter(time.allmembers,[Measures].[Store Sales]>50000)

} ON rows from sales

Generate
将集合应用到另一集合的每个成员，然后用 union 运算合并所得的集合。

1
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ Generate({ USA, Canada }, Descendants(store.CurrentMember, [store state]))

} ON rows from sales

2，ca，wa是USA的，加all则简单复制
SELECT {[Measures].[Store Sales] } ON COLUMNS,

{ Generate({USA, Canada}, {ca, wa} ,all)

} ON rows from sales

如果通过 CurrentMember，«Set1» 与 «set_expression» 无关，那么 Generate 生成 «set_expression» 所指的集合的简单复制，它包含的复制与 «Set1» 中的元组一样多。如果指定了可选的 ALL 标志，结果中将保留所有重复项。如果未指定 ALL，重复项将被删除。

3,字符串的
with member [Measures].[合字符串] as 'Generate({Time.allmembers}, Time.CurrentMember.name," and ")'

SELECT { [Measures].[合字符串] } ON COLUMNS,

{[Store].[All Stores]

} ON rows from sales

3，应用扩展
with member [Measures].[合字符串] as 'Generate({Time.[1997].children}, cstr((Time.CurrentMember, [Measures].[Unit Sales],store.[all stores]))," and ")'

SELECT { [Measures].[合字符串] } ON COLUMNS,

{[Store].[All Stores]

} ON rows from sales

Head
返回集合中指定数目的前若干个元素。

SELECT { Head(Descendants([Time].[1997],2,SELF_BEFORE_AFTER), 3) } ON COLUMNS,

{[measures].[store sales]

} ON rows from sales

Hierarchize
在层次结构中对集合的成员排序。

SELECT Hierarchize

( {[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5],[Time].[1997].[Q2] },post) ON COLUMNS,

{[measures].[store sales]

} ON rows from sales

和下面的语句比较一下就知道了

SELECT {[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5],[Time].[1997].[Q2] } ON COLUMNS,

{[measures].[store sales]

} ON rows from sales

Intersect
返回两个输入集合的交集，可以选择保留重复项。

SELECT

Intersect({[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5] },

{[Time].[1997].[Q2].[5],[Time].[1997].[Q2] },all) ON COLUMNS,

{[measures].[store sales]

} ON rows from sales

注意带ALL和不带ALL 的区别

LastPeriods
返回指定的成员之前（包含该成员）的成员集合。

with member measures.test as 'Generate({LastPeriods(3,time.currentmember)}, Time.CurrentMember.name," and ") '

SELECT {measures.test } ON COLUMNS,

{[Time].members} ON rows from sales

Members
返回维度、层次结构或级别中所有成员的集合。

SELECT

{measures.[store sales] } ON COLUMNS,

{[Time].members} ON rows from sales

Mtd
PeriodsToDate 函数的快捷函数，将级别指定为 Month。

到目前没有发现其意义

NameToSet
基于包含成员名称的字符串表达式，返回一个包含单个成员的集合。

SELECT {measures.[store sales] } ON COLUMNS,

{NameToSet("[Time].[1997]")} ON rows from sales

NonEmptyCrossjoin
返回两个或多个集合的矢量积，除空成员之外。

SELECT {measures.[store sales] } ON COLUMNS,

{NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, {[Promotions].[Big Time Savings]},2)

} ON rows from sales

请详细看联机帮助，这个函数在使用的时候慎用，因为NonEmptyCrossjoin 函数以一个集合的形式返回两个或多个集合的矢量积，不包括空元组或无基础事实数据表提供的数据的元组，因此所有计算成员均被自动排除。

Order
排列集合的成员，可以选择保留或打破层次结构。

SELECT {measures.[store sales] } ON COLUMNS,

Order([Store].[Store State].allmembers, measures.[store sales], BASC) ON rows from sales

PeriodsToDate
返回指定级别上的一个时期（成员）集合，从第一个时期开始到指定的成员为止。

1
SELECT {measures.[store sales] } ON COLUMNS,

PeriodsToDate([Time].[Quarter],[Time].[1997].[Q3].[8]) ON rows from sales

2
SELECT {measures.[store sales] } ON COLUMNS,

PeriodsToDate([Store].[Store Country],[Store].[All Stores].[USA].[OR]) ON rows from sales

Qtd
PeriodsToDate 函数的快捷函数，将级别指定为 Quarter。

同上

Siblings
返回成员的兄弟，包括成员本身。

SELECT {measures.[store sales] } ON COLUMNS,

{[Time].[1997].[Q2].Siblings} ON rows from sales

StripCalculatedMembers
从集合中删除计算成员。

此函数从某个集合中删除计算成员，该集合包含使用 AddCalculatedMembers 添加的计算成员。
WITH MEMBER [Time].[1997].[1到6月的] AS 'SUM([Time].[1]:[Time].[6])'

MEMBER [Time].[1997].[1到9月的] AS 'SUM([Time].[1]:[Time].[9])' SELECT

StripCalculatedMembers(

AddCalculatedMembers([Time].[1997].Children)

) ON COLUMNS, [Product].Children ON rows from sales

StrToSet
用字符串表达式构造一个集合。

SELECT {measures.[store sales] } ON COLUMNS,

{StrToSet("Time.Members")} ON rows from sales

Subset
从集合中返回元素的子集。

SELECT {measures.[store sales] } ON COLUMNS,

{Subset(Time.allMembers,0,7)} ON rows from sales

Tail
从集合尾部返回子集。

SELECT {measures.[store sales] } ON COLUMNS,

{tail(Subset(Time.allMembers,0,7),4)} ON rows from sales

ToggleDrillState
切换对成员的钻取状态。此函数是 DrillupMember 和 DrilldownMember 的组合。

*
SELECT {[Measures].[Store Sales] } ON COLUMNS,

ToggleDrillState({Product.Members},{Product.Bagels, Product.Muffins}, RECURSIVE)

ON rows from sales

TopCount
从集合顶端开始返回指定数目的项，可以选择首先对集合排序。

SELECT {[Measures].[Store Sales] } ON COLUMNS,

Topcount(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 10, [store sales]) ON rows from sales

TopPercent
对集合排序，并返回顶端的 n 个元素，这些元素的累积合计至少为指定的百分比。

SELECT {[Measures].[Store Sales] } ON COLUMNS,

TopPercent(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90, [store sales]) ON rows from sales

TopSum
对集合排序，并返回顶端的 n 个元素，这些元素的累积合计至少为指定的值。

SELECT {[Measures].[Store Sales] } ON COLUMNS,

TopSum(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90000, [store sales]) ON rows from sales

UNION
返回两个集合的并集，可以选择保留重复项。

SELECT {[Measures].[Store Sales] } ON COLUMNS,

Union(USA.Children, CANADA.Children, ALL)

ON rows from sales

VisualTotals
动态计算集合中指定的子成员的合计，并在结果集中对合计的标签使用某种模式来显示。

select

{[Measures].[Unit Sales]} on columns,

{ [Time].[1997],

[Time].[1997].[Q2],

[Time].[1997].[Q4]

} on rows

from Sales

和下面的对比着理解

select

{[Measures].[Unit Sales]} on columns,

{VisualTotals({ [Time].[1997],

[Time].[1997].[Q2],

[Time].[1997].[Q4]

}, "**Subtotal - *")

} on rows

from Sales

Wtd
PeriodsToDate 函数的快捷函数，将级别指定为 Week。

略

Ytd
PeriodsToDate 函数的快捷函数，将级别指定为 Year。

略

MDX函数使用介绍（四）：字符串函数&元组函数
字符串函数

函数
描述

CalculationPassValue
返回在多维数据集的指定计算传递上对 MDX 表达式求得的值。

with member measures.test as 'CalculationPassValue(membertostr([sales average]) , -1, RELATIVE)'

select {measures.test, [sales average] } on columns, { [Time].allmembers} on rows

from Sales

上面的语句按理是对的，但不能运行！待改！

CoalesceEmpty
将空单元值合并为字符串或数字。

with member measures.test as 'CoalesceEmpty(Time.currentmember.Parent.Name, "EMPTY")'

select {measures.test } on columns, {time.allmembers} on rows

from Sales

能运行但不是预想效果

Generate
返回通过在集合上对字符串表达式计算而创建的连锁字符串。

看上面的

IIf
返回由逻辑测试确定的两个字符串或数值之一。

with member measures.test as 'iif (isempty(Time.currentmember.Parent ) , "EMPTY","exist")'

select {measures.test } on columns, {time.allmembers} on rows

from Sales

LookupCube
返回在同一数据库中另外一个指定的多维数据集上对 MDX 表达式求得的值。

看上面的

MemberToStr
从某个成员构造一个字符串。

with member measures.test as ' MemberToStr([store Sales]) '

select {measures.test } on columns,

{ [Time].allmembers} on rows

from Sales

Name
返回维度、层次结构、级别或成员的名称。

略

Properties
返回包含成员属性值的字符串。

with member measures.test as 'store.currentmember.Properties("Store Manager") '

select {measures.test } on columns,

{ Descendants([Store].[All Stores].[USA],[Store].[Store Name])} on rows from Sales

SetToStr
用集合构造一个字符串。

select {measures.[store sales] } on columns,

{ strtoset(SetToStr({[Time].[1997], [Time].[1998]}))} on rows

from Sales

TupleToStr
用元组构造一个字符串。

with member [measures].[TupleToStr] as 'TupleToStr(([Measures].[Sales Count], [Store].[All Stores].[USA])) '

SELECT

{ [measures].[TupleToStr] } ON COLUMNS, [Store].allmembers ON rows from sales

UniqueName
返回维度、级别或成员的唯一名称。

略

UserName
返回当前连接的域名和用户名。

with member measures.test as 'username'

select { measures.test } on columns,

{ [Time].[1997], [Time].[1998]} on rows

from Sales

元组函数

函数
描述

Current
返回迭代过程中集合中的当前元组。

with set kkk as '{{[Time].[1997], [Time].[1998]}*{[Store].[All Stores].[Canada],[Store].[All Stores].[USA]} }'

member measures.jjj as 'TupleToStr(kkk.current)',solve_order=1

select { measures.[store sales],measures.jjj } on columns,

{kkk} on rows

from Sales

待改！找不到current的用途！

Item
从集合中返回元组。

with set kkk as '{{[Time].[1997], [Time].[1998]}*{[Store].[All Stores].[Canada],[Store].[All Stores].[USA]} }'

member measures.jjj as 'TupleToStr(kkk.item(0).item(0))',solve_order=1

select { measures.[store sales],measures.jjj } on columns,