none
parent-child + custom rollup + excel RRS feed

  • Question

  • Dear,

    In a parent-child hierarchy I want to use the value of the parent instead of the aggregation. Therefore I use the DataMember functionality.

    However, in excel, I cannot see my hierarchy or part of it, as the value of the parent can be empty.

    When I select the option in excel "show rows with no data" I can see that the data is ok.
    But asking the end-user to enable each time this option + filtering the dimension values is to much to ask!

    Is there a way to fix this? It is acceptable if the parent is overwritten with "0" as value (if there is no value ofcourse).

    Thanks in advance!

    Thursday, February 16, 2012 8:42 AM

Answers

  • Hi Bram,

    You'd need to do something like this:

    CREATE MEMBER CURRENTCUBE.MEASURES.MYMEASURE AS NULL;

    SCOPE(MEASURES.MYMEASURE);
    SCOPE([Line].[H_Line].MEMBERS);
    THIS = IIF( ISEMPTY((MEASURES.[VALUE], [Line].[H_Line].CURRENTMEMBER.DATAMEMBER)),
    IIF(ISEMPTY(MEASURES.[VALUE]), NULL, 0), (MEASURES.[VALUE], [Line].[H_Line].CURRENTMEMBER.DATAMEMBER));
    END SCOPE;
    END SCOPE;

    Hope this helps,

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    • Marked as answer by BramDe Thursday, February 16, 2012 1:42 PM
    Thursday, February 16, 2012 11:06 AM
    Moderator

All replies

  • Yes, using a scoped assignment to overwrite the All Member with a 0 or a string consisting of a space is the best way of solving this so long as your users are always going to know to choose a member other than the All Member in their queries. Alternatively it might make more sense to turn off the All Member by setting the IsAggregatable property on the attribute to false, and then setting an appropriate default member on the hierarchy.

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    Thursday, February 16, 2012 9:02 AM
    Moderator
  •  

    Hi Chris,

    Thanks for the quick response.

    This issue also occurs in the middle of my parent-child hierarchy when the datamember is empty the hierarchy stops. So I think overwriting the All Member won't be enough?

    I give an example

    Group                                      12
                Line 1                          1
                Line 2                          2         
                Subgroup                   
                            Line A              3
                            Line B              4

    So in excel I will only see the first 3 lines as by default the hierarchy is collapsed and "Subgroup" has no value

    Group                                      12
                Line 1                          1
                Line 2                          2         

    In some cases "Group" is empty as well and we don't see anything! A bit confusing...

    I also could use some help in the mdx statement, I believe I need something that checks, when the datamember is empty, and if one of the children has a value, we overwrite it with the 0 value.

    But when using something like Count(DrillDownLevel(([Line].[H_Line].CurrentMember.Children,[Measures].[Value])), excludeempty) the performance is to slow.

    Thanks in advance!!

    Bram

    Thursday, February 16, 2012 10:22 AM
  • Hi Bram,

    You'd need to do something like this:

    CREATE MEMBER CURRENTCUBE.MEASURES.MYMEASURE AS NULL;

    SCOPE(MEASURES.MYMEASURE);
    SCOPE([Line].[H_Line].MEMBERS);
    THIS = IIF( ISEMPTY((MEASURES.[VALUE], [Line].[H_Line].CURRENTMEMBER.DATAMEMBER)),
    IIF(ISEMPTY(MEASURES.[VALUE]), NULL, 0), (MEASURES.[VALUE], [Line].[H_Line].CURRENTMEMBER.DATAMEMBER));
    END SCOPE;
    END SCOPE;

    Hope this helps,

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    • Marked as answer by BramDe Thursday, February 16, 2012 1:42 PM
    Thursday, February 16, 2012 11:06 AM
    Moderator
  • Hi Chris,

    Thanks a lot!! It worked like a charm, you really made my day!!

    Thanks,

    Bram

    Thursday, February 16, 2012 1:42 PM