locked
How to Only show a Calculated member on one level?? RRS feed

  • Question

  • Hi All,

    i have some problems with the calculated member below. It works fine, but I need it to show null when the level isn't Month. I have struggled a long time now with creative (at least what I think ;)) solutions with no success. And it's probably not hard or difficult at all. Could anyone PLEASE help?

    CREATE MEMBER CURRENTCUBE.[Measures].[Avg Per Month]
     AS AVG({[Datum].[Month].CURRENTMEMBER.PREVMEMBER,
             [Datum].[Month].CURRENTMEMBER,
             [Datum].[Month].CURRENTMEMBER.NEXTMEMBER },[Measures].[Total Sum]), 
    FORMAT_STRING = "# ### ###.00;-# ### ###.00", 
    NON_EMPTY_BEHAVIOR = {  }, 
    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  

    Thanks!
    Wednesday, April 28, 2010 3:11 PM

Answers

  • Use SCOPE. I have no way to test this at the moment but something like:

    CREATE MEMBER CURRENTCUBE.[Measures].[Avg Per Month]
     AS
    NULL;

    SCOPE ([Datum].[Month].Members, [Measures].[Avg Per Month]);
     THIS = AVG({[Datum].[Month].CURRENTMEMBER.PREVMEMBER,
     [Datum].[Month].CURRENTMEMBER,
     [Datum].[Month].CURRENTMEMBER.NEXTMEMBER },[Measures].[Total Sum])
    FORMAT_STRING = "# ### ###.00;-# ### ###.00"
    END SCOPE;
    

    • Edited by Philip Stephenson Wednesday, April 28, 2010 3:43 PM What is it with these <br/>?
    • Marked as answer by smlu2010 Thursday, April 29, 2010 7:22 AM
    Wednesday, April 28, 2010 3:32 PM

All replies

  • Use SCOPE. I have no way to test this at the moment but something like:

    CREATE MEMBER CURRENTCUBE.[Measures].[Avg Per Month]
     AS
    NULL;

    SCOPE ([Datum].[Month].Members, [Measures].[Avg Per Month]);
     THIS = AVG({[Datum].[Month].CURRENTMEMBER.PREVMEMBER,
     [Datum].[Month].CURRENTMEMBER,
     [Datum].[Month].CURRENTMEMBER.NEXTMEMBER },[Measures].[Total Sum])
    FORMAT_STRING = "# ### ###.00;-# ### ###.00"
    END SCOPE;
    

    • Edited by Philip Stephenson Wednesday, April 28, 2010 3:43 PM What is it with these <br/>?
    • Marked as answer by smlu2010 Thursday, April 29, 2010 7:22 AM
    Wednesday, April 28, 2010 3:32 PM
  • Not sure how the attribute relations and hierarchies are set up in the [Datum] dimension - maybe testing for the All level will work, like:

    CREATE MEMBER CURRENTCUBE.[Measures].[Avg Per Month]
     AS
    Iif([Datum].[Month].CURRENTMEMBER.Level.Ordinal = 0, null,
    AVG({[Datum].[Month].CURRENTMEMBER.PREVMEMBER,
             [Datum].[Month].CURRENTMEMBER,
             [Datum].[Month].CURRENTMEMBER.NEXTMEMBER },[Measures].[Total Sum])), 
    FORMAT_STRING = "# ### ###.00;-# ### ###.00", 
    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  


    - Deepak
    Wednesday, April 28, 2010 3:35 PM
  • Thanks Deepak :)

    This worked fine too.

    Thursday, April 29, 2010 7:22 AM
  • Thanks a lot. This was IT :)

    /Sara

    Thursday, April 29, 2010 7:23 AM