SQL Server Performance

Obtaining current month from system date using MDX

Discussion in 'Analysis Services/Data Warehousing' started by lidsurfer, Nov 9, 2004.

  1. lidsurfer New Member

    Hi there,

    I am using the following MDX to default to the current month.

    tail(filter([FiscalYear].[Month].members, not
    isempty([FiscalYear].currentmember)),1).item(0)


    The issue i have however, is if there is an incorrect transaction in the system for a date in the future, or of there is budget data, this MDX will not work. How can I use MDX to somehow point to the Current month from the System date?

    Is there a way to do this?

    e.g. Where system month = dimension time period
  2. Raulie New Member

    MDX allows you to incorporate VBA functions into your code, so for your example you can use Month(Date()) that will generate 11 for November.
  3. lidsurfer New Member

    Yeah but there will no difference form year to year.

    I found this MDX to work well.

    Tail(Filter([MyDate].[Day].members, [MyDate].Properties("Is_Current_Date_Flag") ="yes" ),1).item(0)
  4. Raulie New Member

    Alternativly I was thinking you could use something like this.

    You can set the Default Member property for the Time dimension to an MDX formula, which uses the VB Now() function to pick up the server date, e.g.

    StrToMember("[Time].[Month].[" + Format(Now(), "mmmm") + "]")

    This will let the time dimension default to the current month based on the server date, but you might want to consider adding a flag to the Time dimension table to denote the current period. This is because you probably want to default based on the load date, not the server date. If you have a flag you can use the Filter function to pick up the default member, or see the SQL Server Accelerator for BI for good example of time dimensions.

    http://blogs.msdn.com/bi_systems/articles/162831.aspx

    Setting up the flags is also a very good approach.

    Raulie
    hp

Share This Page