Creating role which denied the access of all the empty elements

Question

I just wanted to have a role which denied the access of all the empty elements of a dimension.

I have the right MDX query which I've tested in SQL Server:

SELECT NON EMPTY [People].[Category].[Category].allmembers ON COLUMNS  FROM [MyCube] 

I just wanted to have the same result in my allowed member set on my role but I have an error:

incorrect syntax.

Thanks

Answer 1

There is no such thing as an empty element of the dimension. There are elements of the dimension without a measure value which is a different thing. So what you need is to deny access to the measure when the non-empty situation arises.

The following example shows how to select the MyMeasure values based on the non-empty set of Category Member - MyMeasure

SELECT [Measures].[MyMeasure] ON 0,
NONEMPTY(
    [People].[Category].[Category].MEMBERS, [Measures].[MyMeasure])}
)
ON 1
FROM [MyCube] 

Hope this helps.