Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Create calculated measure using PrevMember

Answered Create calculated measure using PrevMember

  • Tuesday, January 01, 2013 3:02 PM
     
     
    Sign In to Vote
    0

    Hello All,

    I created calculated measure using PrevMember function.

    ([DimTime].[Year-Month].CurrentMember.PrevMember , [Measure].[SomeMeasure])

    I browsed cube and previous members displayed correctly. When I applied year-month filter for that no results displayed. I want to get previous member(selected member's previous member) total value.

    Any reason for this case??

    Thanks in advance.

     

All Replies

  • Tuesday, January 01, 2013 3:14 PM
     
     
    Sign In to Vote
    0

    Hi Anush87 ,

    When you are declaring for a set in the BIDS, the members are fixed so no change will be made .

    In our case I think that the default member for [Dim Date].[Year-month] is All, and his PREVMEMBER will be All as well . Please try the DYNAMIC SET : http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

    Regards, David .

     
  • Tuesday, January 01, 2013 3:27 PM
     
     
    Sign In to Vote
    0

    Hi David,

    Thanks for the quick reply.

    Actually I'm having the correct results without that filter. In my data set I have year-month as member as follows.

    January 2000

    February 3000

    March 4000

    so on.  for an example previous value 4000 in march means actual value of February equals to 4000. 


    • Edited by Anush87 Tuesday, January 01, 2013 3:28 PM
    •  
     
  • Tuesday, January 01, 2013 3:59 PM
     
     
    Sign In to Vote
    0

    As long as you got the right resaults :)) Maybe I didn't understand you correctly .

    Do you get a correct answer when you put the set itsef on the axis? (From the SSMS - session OR from the BIDS - global) ? .

    Regards, David .

     
  • Tuesday, January 01, 2013 4:06 PM
     
     
    Sign In to Vote
    0

    Yes. I got correct result when I put set on axis.

    Problem is it's not working when I apply filter to that result set.

     
  • Tuesday, January 01, 2013 4:08 PM
     
     
    Sign In to Vote
    0
    Thanks for your quick reply  . Now we both have the answers ;)

    Regards, David .

     
  • Tuesday, January 01, 2013 4:21 PM
     
     
    Sign In to Vote
    0
    Any one have idea about this ?
     
  • Wednesday, January 02, 2013 9:53 AM
     
     
    Sign In to Vote
    0

    Do you mean you use the SSMS browser ? Where did you put the set ?

    I had a case wich made me explore ... and it's look like Excel will generate a didderent MDX than the SSMS's browser will . Do you have the same problem in Excel ?

    Regards, David .

     
  • Thursday, January 03, 2013 4:18 AM
     
     
    Sign In to Vote
    0

    I tried in SSMS , BIDS and Excel too. same result.

     
  • Thursday, January 03, 2013 9:06 AM
     
     
    Sign In to Vote
    0
    show us the query that does not work, and explain what you expect and what you see instead
     
  • Thursday, January 03, 2013 4:06 PM
     
     
    Sign In to Vote
    0
    The filter you are using, is it a member of the same hierarchy that you are using the PrevMember function on? 
     
  • Friday, January 04, 2013 9:16 AM
     
     
    Sign In to Vote
    0

    Hi,

    Im using separate member for the filter while hierarchy is created using another member.

     
  • Friday, January 04, 2013 11:45 AM
     
     
    Sign In to Vote
    0
    Use SQL Profiler to get the MDX and run this MDX in SSMS and debug

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

     
  • Tuesday, January 08, 2013 8:28 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Anush87,

    Please take a look at the following thread:
    Calculated member using PrevMember: http://social.technet.microsoft.com/Forums/en/sqlanalysisservices/thread/4a497248-c37b-43de-955c-33b193582aec

    Regards,

    Elvis Long
    TechNet Community Support