MDX 函数的使用介绍(一)
发布时间：2020-12-12 16:02:21 所属栏目：MsSql教程 来源：网络整理
导读：根据SqlServer2000 Analysis Services提供的帮助材料展开，略作说明并且根据各个函数的侧重点编写相应的MDX；这些成果主要源于我的老大浩哥，再次向他表示感谢。 数组函数 函数 描述 SetToArray 将一个或多个集合转换为数组，以用于用户定义函数中。 ? ? ?
根据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
,{ [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]
?
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
