Filter MDX hierarchy, however still show total of leave - Ragged Hierarchy?

Question

The following query

 SELECT
{  [Measures].[Comp Money In] } ON COLUMNS,
{ 
 (
  [Dim User].[UserLevel1].[UserLevel1].AllMembers * 
  [Dim User].[UserLevel2].[UserLevel2].AllMembers * 
  [Dim User].[UserLevel3].[UserLevel3].AllMembers * 
  [Dim User].[UserLevel4].[UserLevel4].AllMembers * 
  [Dim User].[UserLevel5].[UserLevel5].AllMembers   
 )
} ON ROWS
FROM   [Activities] 
WHERE ( [Dim User].[UserIdHierarchy].[UserLevel1Id].&[#513],
[Dim User].[UserTypeHierarchy].[UserTypeLevel1].&[Commercial Partner].&[Agent]  ) CELL Properties Value 

Brings up the following result :

My problem is that, since I am filtering the [Dim User].[UserTypeHierarchy], the measure values which are being shown, are only of those which have the type as a [Commercial Partner].&[Agent]. I would like that even though I am showing the user tree, the figures that are being shown show all the measures, as if it is like descendants([Hierarchy], 0, self_and_after).

How do I achieve this? I tried using calculated members already, but the figures shown are not aggregating the descendants of all the user types under the [Dim User].[UserTypeHierarchy] hierarchy.

To explain myself better, the following image is without the user type filter:

So my desired result, is the first image, where I am only displaying users of user type commercial partner, but the figures of the 2nd image, so for example

• 513 will show all the figures aggregating the descendants

• 100310 will show all the figures aggregating all users under him on the 3rd level

So on so fourth.

Answer 1

The [Activities] Cube is not set up correctly. When querying one level, it should show the values of itself and its children (e.g. "Country Shop 1" should show 19). This should be part of aggregation rules within the cube. The value that's shown now in the parents of the Cube (e.g. Country Shop 1 €6) should show in an additional rest group created automatically as an additional child for every parent.

So, the simple MDX for this cube would be:

SELECT {
[Measures].[In]
} ON COLUMNS,
{
DESCENDANTS([Dim User].[UserTypeHierarchy].[UserTypeLevel1],0,SELF_AND_AFTER)
}
from [Activities]

Would show all the regions, countries and shops with the proper aggregated values.

Greets Phil

Answer 2

This is the solution:

WITH 
    MEMBER [Measures].[Test] AS
        0
SELECT
    { 
         [Test]
    } ON 0, 
    NON EMPTY { 
        filter (
            {[dim user].[userhierarchy].[userlevel1].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel2].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel3].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel4].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel5].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel6].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel7].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel8].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel9].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        ),
        filter (
            {[dim user].[userhierarchy].[userlevel10].allmembers  }
            ,[dim user].[userhierarchy].currentmember.children.count > 1
        )
    } ON 1
FROM   [Activities]

I am not filtering by parent user types, but bringing up those users which have the children count greater than 1.