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等的应用
语法是死的,人是活的.
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等的应用
语法是死的,人是活的.