none
Aggregating measure over multi-level hierarchy while exculding members RRS feed

  • 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!


    • Edited by bash721 Thursday, June 20, 2013 6:24 PM more fitting title
    Thursday, June 20, 2013 4:57 PM

All replies

  • How about

    SELECT {

    [Measures].[#Work Units]

    } ON COLUMNS

    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 0 FROM [MyCube])


    Richard

    Monday, June 24, 2013 6:14 AM