推荐这篇日记的豆列
&(1392人关注)
&(369人关注)
&(418人关注)
&(184人关注)
&(428人关注)3822人阅读
根据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] ))'
&{ { [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(
&(& + MemberToStr(Store.CurrentMember) + &, Measures.[Unit Sales])&
{Measures.Amount, Measures.[Store Unit Sales]} ON COLUMNS,
Store.CA.CHILDREN ON ROWS
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) )'
&{ [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])'
&{ [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 ) ) '&
&{ [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]) '&
&{ [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)) '&&
&{[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],
LE***ES)&SELECT
&{[Measures].[Store Sales]& } ON COLUMNS,
{Descendants([Time].[1997],[time].[quarter],LE***ES)} 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
和下面的对比着理解
{[Measures].[Unit Sales]} on columns,
{VisualTotals({ [Time].[1997],
&& [Time].[1997].[Q2],
&&& [Time].[1997].[Q4]
}, &**Subtotal - *&)
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])) '&
&{ [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,
& {kkk} on rows
from Sales&StrToTuple&用字符串构造一个元组。&&&with member measures.jjj as 'StrToTuple(&([store Sales], &+time.currentmember.uniquename+&)&)',solve_order=1
select& {& measures.jjj } on columns,
& {time.allmembers} on rows
from Sales&&
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：45538次
排名：千里之外
转载：20篇
(1)(1)(24)
(window.slotbydup = window.slotbydup || []).push({
id: '4740881',
container: s,
size: '200,200',
display: 'inlay-fix'