MDX语句

FILTER ,NOT, ISEMPTY
Filter([Product].[Brand Name].Members, Not IsEmpty( ([USA], [Unit

Sales]) ))

注:排除了在整个美国Unit Sales为空值的品牌


================规格MDX==
with member [Measures].[排名] as
'rank(
[组织].CurrentMember,
Order([组织].currentmember.Siblings,[Measures].[得分],BDESC),
[Measures].[得分]
)',solve_order=10
SELECT {[Measures].[得分],[Measures].[排名]} ON COLUMNS,
{[组织].allmembers} ON ROWS
FROM [立方体名]
==================正确实现

with MEMBER [Measures].[value1_CNRD] AS IIF(ISEMPTY([Time].CurrentMember),null,RANK([Time].CurrentMember,Order([Time].CurrentMember.siblings,[Measures].[value1],BDESC)))
select {[Measures].[value1],[Measures].[value1_CNRD]} on columns,{[Time].members} on rows from BusinessAccept ;

================失败例子
with SET MYSET AS FILTER({[Time].CurrentMember.siblings},[Measures].[value1]>0)
MEMBER [Measures].[value1_CNRD] AS
IIF(RANK([Time].CurrentMember,Order(MYSET,[Measures].[value1],BDESC))=0,NULL,RANK([Time].CurrentMember,Order(MYSET,[Measures].[value1],BDESC)))
SELECT {[Measures].[value1],[Measures].[value1_CNRD]} ON COLUMNS,
{[Time].members} ON ROWS FROM BusinessAccept;

=====NONEMPTY()函数支持性不好
with MEMBER [Measures].[value1_CNRD] AS
RANK([Time].CurrentMember,Order(NONEMPTY([Time].CurrentMember.Siblings),[Measures].[value1],BDESC))
SELECT {[Measures].[value1],[Measures].[value2],[Measures].[KPI_value1_TEND],[Measures].[KPI_value1_STATUS],[Measures].[value1_CNRD]} ON COLUMNS,
{[Time].members} ON ROWS FROM BusinessAccept

===========这个NON EMPTY()可以,
select {[Measures].[value1]} on columns,NON EMPTY([Time].members)on rows from BusinessAccept ;

RANK,ORDER, IIF,ISEMPTY,FILTER,WITH等的应用
语法是死的,人是活的.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值