CODEWITHSUNDEEP
ssasmdxolapbusiness-intelligencedimensional-modeling
Mez
Mez

Reputation: 4726

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

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 :

enter image description here

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: enter image description here

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

So on so fourth.

Upvotes: 3

Views: 2660

Answers (2)

Mez
Mez

Reputation: 4726

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.

Upvotes: 0

Phil
Phil

Reputation: 41

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

Upvotes: 3

Related Questions