关键词:
Filter
环比
同比
上卷
下钻
MDX Query Context and Execution
Filter:返回根据搜索条件对指定集进行筛选后得到的集。
它对上下文敏感!!!
语法:Filter(Set_Expression, Logical_Expression )
String mdxStr_140 ="select non empty{[Account].Children} on columns,{Filter({[Time].[2017].[4].[11],[Time].[2017].[4].[12]},[Measures].[金额]>10000)} on rows " +
"from [Col_trade_detail] ";
//错误?
String mdxStr_142 ="select non empty{[Account].Children} on columns,{Filter([Time].[Year].Members,[Measures].[金额]>10000)} on rows " +
"from [Col_trade_detail] ";
结果:
需要注意的是:msdStr_142 语句不是错了,而是有的年份有空值!所有什么也过滤不出来,空值参与运算,后面需要总结一下。
相关比对语句如下:
String mdxStr_144="SELECT Filter({[Account].[Level1projectname].Members},[Measures].[金额]>10000000)on axis(0) FROM [Col_trade_detail] where ([Time].[2017],[Measures].[金额])";
//----去掉条件 啥也查不出来,原因是默认的维度成员有空值引起的?
String mdxStr_145="SELECT Filter({[Account].[Level1projectname].Members},1)on axis(0) FROM [Col_trade_detail] ";
String mdxStr_146="SELECT Filter({[Account].[Level1projectname].Members},1)on axis(0) FROM [Col_trade_detail] where ([Measures].[金额])";
//下面的语句 如果把[Time].[2017] 换成[Time].[2005],则什么也没有
String mdxStr_146_1="SELECT Filter({[Account].[Level1projectname].Members},[Measures].[金额]>10000000)on axis(0) FROM [Col_trade_detail] where ([Time].[2017],[Measures].[金额])";
String mdxStr_147="SELECT Filter([Account].[Level1projectname].Members,[Measures].[金额]>10000000)on axis(0) FROM [Col_trade_detail] where ([Time].[2005])";
后面的内容:
环比:
String mdxStr_151="with member [Measures].[环比] as "+
"'[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2017].Children} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
//这里我想求全部年度的的季度及月份的环比? 首先查2017年第4季度前面30个季度的同比情况
String mdxStr_152="with member [Measures].[环比] as "+
"'[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2011].[4]:[Time].[2017].[4]} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
String mdxStr_153="with member [Measures].[环比] as "+
"'[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2017].[4].Lag(20):[Time].[2017].[4]} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
String mdxStr_151="with member [Measures].[环比] as "+
"'[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2017].Children} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
//这里我想求全部年度的的季度及月份的环比? 首先查2017年第4季度前面30个季度的同比情况
String mdxStr_152="with member [Measures].[环比] as "+
"'[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2011].[4]:[Time].[2017].[4]} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
String mdxStr_153="with member [Measures].[环比] as "+
"'[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2017].[4].Lag(20):[Time].[2017].[4]} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
//修正 应对前一期可能为0的情况 iif 有问题?
String mdxStr_154="with member [Measures].[环比] as "+
"'IIF(([Measures].[金额]=0),null," +
"[Measures].[金额]/([Measures].[金额],[Time].CurrentMember.PrevMember)-1)', FORMAT_STRING=\"0%\""+
" select {[Measures].[金额],[Measures].[环比]} on axis(0),{[Time].[2017].[4].Lag(20):[Time].[2017].[4]} on rows from [Col_trade_detail] "+
" where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
环比结果:
公安行业行政事业收费有个有趣的特点,每年第一个季度,行政事业收费环比都大幅上升,为什么呢,恐怕得结合行业特点去了解。
iif:
根据布尔条件为 true 还是 false,计算不同的分支表达式。
语法:IIf(Logical_Expression, Expression1 [HINT <hints>], Expression2 [HINT <hints>])
同比:
cousin:返回与指定的子成员在父成员下方具有相同的相对位置的子成员。
Cousin( Member_Expression , Ancestor_Member_Expression )
例子:
String mdxStr_160="SELECT Cousin([Time].[2017].[4],[Time].[2016]) ON 0 FROM [Col_trade_detail]";
结果:
同比:
String mdxStr_160="SELECT Cousin([Time].[2017].[4],[Time].[2016]) ON 0 FROM [Col_trade_detail]";
String mdxStr_161="WITH MEMBER Measures.[同比] AS"+
"'[Measures].[金额]/([Measures].[金额], Cousin([Time].CurrentMember, [Time].CurrentMember.Parent.PrevMember))- 1', FORMAT_STRING = \"0%\""+
" SELECT {[Measures].[金额], Measures.[同比]} ON 0,"+
"{[Time].[2011].[1]:[Time].[2017].[4]} ON 1"+
" FROM [Col_trade_detail] where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
String mdxStr_162="WITH MEMBER Measures.[同比] AS"+
"'[Measures].[金额]/([Measures].[金额], Cousin([Time].CurrentMember, [Time].CurrentMember.Parent.PrevMember))- 1', FORMAT_STRING = \"0%\""+
" SELECT {[Measures].[金额], Measures.[同比]} ON 0,"+
"{[Time].[2008].[1]:[Time].[2017].[4]} ON 1"+
" FROM [Col_trade_detail] where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入].[公民出入境证件费]";
结果:
用ParallelPeriod也很容易形成同比,直接上例子:
String mdxStr_167="WITH MEMBER Measures.[同比] AS"+
"'([Measures].[金额]-([Measures].[金额],ParallelPeriod([Time].[Year],1,[Time].[Quarter].currentMember)))/" +
"([Measures].[金额],ParallelPeriod([Time].[Year],1,[Time].[Quarter].currentMember))', FORMAT_STRING = \"0%\""+
" SELECT {[Measures].[金额], Measures.[同比]} ON 0,"+
"{[Time].[2011].[1]:[Time].[2017].[4]} ON 1"+
" FROM [Col_trade_detail] where [Account].[Account_L].[行政事业性收费收入].[公安行政事业性收费收入]";
结果:
可以看出:mdxStr_167与mdxStr_161语句是一样的。
上卷:
下钻:
下钻我理解是DrilldownLevel函数的应用,
DrilldownLevel:Drills down the members of a set to one level below the lowest level represented in the set.
DrilldownLevel(Set_Expression [,[Level_Expression] ,[Index]] [,INCLUDE_CALC_MEMBERS])
例1:
String mdxStr_171="SELECT non empty DrilldownLevel({[Account].[Account_L].[行政事业性收费收入]}) ON COLUMNS from [Col_trade_detail] " +
" where [Time].[2017]";
结果:
ps:如果不限定[Time].[2017],前面的年份是年份,什么都查不出来!空值的处理,后面需要专题学习、测试。
本节未完,待续.....