|
函数 |
描述 |
|
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。 |
|
略 |
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。