locked
calculated member aggregation only to a certain level RRS feed

  • Question

  • Hi I created a calculated measure that should be used only for the members of a Calendar's hierachy month level.  At the year level, though, I need a different calculation, namely the output of the value on the last month for that year.  how can i accomplish this?

    I know in regular measures, unary operations can be used, but how do u specify this on calculated members?
    Javier Guillen
    Thursday, January 28, 2010 2:44 PM

Answers

  • Hi,

    You can achieve customer aggregation with SCOPE statement as NS100 said, within calculated member at MDX level, you can try this:

    with member [Measures].x as

      case when [Date].[Calendar].currentmember.level is [Date].[Calendar].[Date]

      then 1

      when [Date].[Calendar].currentmember.level is [Date].[Calendar].[Month]

      then 2

      else null

      end

    Hope this helps,

    Raymond

    • Marked as answer by Raymond-Lee Saturday, February 6, 2010 5:08 AM
    Monday, February 1, 2010 3:52 AM

All replies

  • you could use SCOPE Statment to provide calculation at different level.


    SCOPE{[Measures].[Internet Transaction Amt]}), 
    (date.calendar.month);
    
    this = [Measures].[Internet Transaction AMT])/[Measures].[Internet Transaction Count];
    
    END SCOPE;

     

    Thursday, January 28, 2010 4:09 PM
  • Hi,

    You can achieve customer aggregation with SCOPE statement as NS100 said, within calculated member at MDX level, you can try this:

    with member [Measures].x as

      case when [Date].[Calendar].currentmember.level is [Date].[Calendar].[Date]

      then 1

      when [Date].[Calendar].currentmember.level is [Date].[Calendar].[Month]

      then 2

      else null

      end

    Hope this helps,

    Raymond

    • Marked as answer by Raymond-Lee Saturday, February 6, 2010 5:08 AM
    Monday, February 1, 2010 3:52 AM
  • Hi perhaps you can help...

    Where do you use the above code...in the calculated member expression field ??

    Please can you paste a screenshot ??

    Monday, July 15, 2013 1:50 AM
  • Hi Mark ,

    This is a query calculated member .

    You should open SSMS, connect to relevany analysis server, right-click the database -> New query -> MDX .

    Check the next link for query and session calculated members (ther is a global option as well...) :

    http://msdn.microsoft.com/en-us/library/ms145592(v=sql.100).aspx

    Hope it helps :)


    Regards, David .

    Tuesday, July 16, 2013 9:01 AM