MDX 函数的使用介绍(一)

根据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]