none
Set default member based on an attribute

    Question

  • Hi,

    I have a time dimension which is based on a time_dimension table. This has a column called current period which can be set to 0 or 1. My requirement is that I want to set the default member of my Year attribute (which is also part of the time hierarchy) to that value in my dimension table that has current period set to 1. How do i do this?

     

    Monday, September 20, 2010 9:13 AM

Answers

  • OK, so is TimeID and Current Period from the same hierarchy? IF so, what is the relation? If currentperiod is the parent of TimeID, then maybe you can write this expression

    [Time].[Time].[Currentperiod].[1].firstchild

    This will give the first child of CurrentPeriod=1 which will be the required TimeID (provided TimeID is the next level)... 


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    • Marked as answer by Deepak.P Monday, September 20, 2010 11:13 AM
    Monday, September 20, 2010 11:08 AM
    Moderator
  • Hi Jason,

    I found an easier way to do this.

    I added a filter to the default member property :

    Filter([Time].[Period].members,[Time].[Period].Properties("CurrentPeriod")="1").Item(0)

    This works smooth. Thanks for the help about the other method as well!

    • Marked as answer by Deepak.P Monday, September 20, 2010 11:40 AM
    • Unmarked as answer by Deepak.P Monday, September 20, 2010 12:08 PM
    • Marked as answer by Deepak.P Monday, September 20, 2010 12:08 PM
    Monday, September 20, 2010 11:36 AM

All replies

  • Not sure whether it would work as I haven't tried it, but logically thinking, this solution should.

    Go to the default member property of Year, and enter the following MDX expression

    {[Time].[Year].children*[Time].[Current Period].[1]}.item(0).item(0)

     

    The set would give the crossjoin of all Years with current period=1. The first item(0) will give the first row of the tuple, and the second item(0) should give the Year. Make sure that the syntax of [Time].[Current Period].[1] is correct and that it refers the column with flag set to 1


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Monday, September 20, 2010 9:37 AM
    Moderator
  • Not sure whether it would work as I haven't tried it, but logically thinking, this solution should.

    Go to the default member property of Year, and enter the following MDX expression

    {[Time].[Year].children*[Time].[Current Period].[1]}.item(0).item(0)

     

    The set would give the crossjoin of all Years with current period=1. The first item(0) will give the first row of the tuple, and the second item(0) should give the Year. Make sure that the syntax of [Time].[Current Period].[1] is correct and that it refers the column with flag set to 1


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

     


    Thanks Jason, but am getting an error which says that the time dimension appears twice on the cross join
    Monday, September 20, 2010 10:27 AM
  • Where are you getting the error? and could you post the error as well as the MDX expression that you have supplied?

    I just checked in Adventure Workd cube and I was able to pass the syntax of an expression like

    {[Date].[Calendar Year].children*[Date].[Day Name].[Sunday]}.item(0).item(0)

     

    Also, do you have role playing dimensions for time? if so, errors might come, so please post the full error message for me to understand better

     


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Monday, September 20, 2010 10:40 AM
    Moderator
  • The Hierarchy is called Time. It's under a dimension called time.

    The Query :

    {[Time].[Time].[TimeID].members*[Time].[Time].[Currentperiod].[1]}.item(0).item(0)

    The Error :

    DefaultMember(Time,TimeID) (1, 2) The Time hierarchy is used more than once in the Crossjoin function.
    Monday, September 20, 2010 10:49 AM
  • OK, so is TimeID and Current Period from the same hierarchy? IF so, what is the relation? If currentperiod is the parent of TimeID, then maybe you can write this expression

    [Time].[Time].[Currentperiod].[1].firstchild

    This will give the first child of CurrentPeriod=1 which will be the required TimeID (provided TimeID is the next level)... 


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    • Marked as answer by Deepak.P Monday, September 20, 2010 11:13 AM
    Monday, September 20, 2010 11:08 AM
    Moderator
  • Hi Jason,

    I found an easier way to do this.

    I added a filter to the default member property :

    Filter([Time].[Period].members,[Time].[Period].Properties("CurrentPeriod")="1").Item(0)

    This works smooth. Thanks for the help about the other method as well!

    • Marked as answer by Deepak.P Monday, September 20, 2010 11:40 AM
    • Unmarked as answer by Deepak.P Monday, September 20, 2010 12:08 PM
    • Marked as answer by Deepak.P Monday, September 20, 2010 12:08 PM
    Monday, September 20, 2010 11:36 AM