Expand user hierarchy if current member has children - MDX

Question

I have a user hierarchy, and I would like to expand and drill down only if the user has children. If the member in the hierarchy does not have children, I would like to hide this member.

The following query gets all those who are on level 1 in the hiearchy, but I would like to show only those which have at least a child member.

  with member [Test] as '0'
SELECT
{ [Test] } ON COLUMNS, non empty
{ 
 ( 

  [Dim User].[UserHierarchy].[UserLevel1].AllMembers 

 )
}Dimension Properties MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM   [Activities]

How can I achieve this? The user hierarchy is a ragged one.

Answer 1

WITH 
    MEMBER [Measures].[Test] AS
        0
SELECT
    { 
         [Test]
    } ON 0, 
    NON EMPTY { 
        FILTER (
             [Dim User].[UserHierarchy].[UserLevel1].AllMembers
            ,[Dim User].[UserHierarchy].CurrentMember.Children.Count > 0
        )
    } ON 1
FROM   [Activities]