Aggregating value over multi-level hierarchy while exculding members

Question

I have a cube that contains a multi level manager hierarchy as well as units of work that have been completed over a time dimension. Lets assume this is the data I'm working with:

http://i.stack.imgur.com/eNP4L.png

What I'm trying to do via MDX is focus on a specific level, (Level 1, John Smith) and remove individuals (both end users and managers) from the hierarchy and properly rollup the values.

http://i.stack.imgur.com/xW5CF.png

As you can see from the above image, we've removed Lisa Rice and Morgan Richardson's entire team. The expected results would be two columns, Full Name and Work Units.

The query that I've put together so far looks like this:

WITH SET [MyCustomSet] AS EXCEPT(
    DESCENDANTS([HR].[Mgr Hierarchy].&[JSMITH],, SELF_AND_AFTER), 
    {DESCENDANTS([HR].[Mgr Hierarchy].&[MRICHARDSON],,SELF_AND_AFTER), DESCENDANTS([HR].[Mgr Hierarchy].&[LRICE],,SELF_AND_AFTER)}
)
MEMBER [Measures].[MyMeasure] AS Aggregate([MyCustomSet], [Measures].[#Work Units])
SELECT {
    [Measures].[MyMeasure]
} ON COLUMNS, 
NON EMPTY {
    [MyCustomSet]
} ON ROWS
FROM [MyCube]

But this returns a list of the members I need but the aggregated value for every single member is the total for all members. When I remove the calculated measure and just use the [Measures].[#Work Units] the values represent the total rollup values without the members removed however the members are not present in the column.

The eventual home for this data will be in a SSRS table setup with a recursive parent relationship to display the hierarchy correctly.

Can anyone lend a hand or point me in the right direction? Thanks!

findango findango · Accepted Answer · 2013-06-20T15:29:30

I think you want to change your Aggregate() to be the aggregation below each member, and not for the entire [MyCustomSet]. Something like:

MEMBER [Measures].[MyMeasure] AS 
    Aggregate([HR].[Mgr Hierarchy].CurrentMember, [Measures].[#Work Units])

Edit: If you are looking for the aggregation to change at each parent level, maybe you should be looking at using a subselect.

SELECT 
    [Measures].[#Work Units] ON COLUMNS, 
    NON EMPTY [HR].[Mgr Hierarchy].Members ON ROWS
FROM (
    SELECT EXCEPT(
        DESCENDANTS([HR].[Mgr Hierarchy].&[JSMITH],, SELF_AND_AFTER), 
        {DESCENDANTS([HR].[Mgr Hierarchy].&[MRICHARDSON],,SELF_AND_AFTER), DESCENDANTS([HR].[Mgr Hierarchy].&[LRICE],,SELF_AND_AFTER)}
        ON COLUMNS
    FROM [MyCube]
)

Aggregating value over multi-level hierarchy while exculding members

2 Answers