NON Empty vs NONEMPTY()

NON Empty:

Remove empty rows or columns from a query

To remove empty rows or columns from a query, you can use the NON EMPTY statement before the axis set definition

SELECT 
{[Measures].[Internet Tax Amount]}
ON COLUMNS,
NON EMPTY
[Product].[Category].[Category].MEMBERS
ON ROWS
FROM [Adventure Works]
WHERE([Date].[Calendar].[Calendar Year].&[2001])


NONEMPTY():

Remove empty tuples from a set

More generally, to remove empty tuples from a set you can use the NonEmpty function.

WITH 
MEMBER MEASURES.CategoryCount AS
COUNT([Product].[Category].[Category].MEMBERS)
MEMBER MEASURES.NonEmptyCategoryCountFor2001 AS
COUNT(
NONEMPTY(
[Product].[Category].[Category].MEMBERS
,([Date].[Calendar].[Calendar Year].&[2001], [Measures].[Internet Tax Amount])
))

SELECT
{MEASURES.CategoryCount,MEASURES.NonEmptyCategoryCountFor2001 }
ON COLUMNS
FROM [Adventure Works]

posted @ 2011-01-07 13:55  AOT  阅读(249)  评论(0编辑  收藏  举报