Post reply

Setting the default member on a Time Dimension to current date

  • April 30, 2004 at 9:10 am

    #161225

    I have a time shared dimension in my olap cube.  I would like to set the default member to the current date.  Does anybody know if there is a way to do this?  I do not know mdx but I thought it might be possible using it.  I would appreciate any help someone has to offer on this.

    Thanks!

    John

  • May 3, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • May 4, 2004 at 1:16 am

    #505079

    It can be done, it's not pretty but here is one way of doing it.....

    Steps I took:

    1. copy the current Shared [Time] dimension, I named it [Time2]

    2. For the sake of making the demo simple, delete the Month Qtr level from the new dim.

    3. Change the key and display settings for the month level to be yyyymm, hopefully you can re-use the script --> "time_by_day"."the_year" & IIF("time_by_day"."month_of_year" < 10, '0' &  "time_by_day"."month_of_year", "time_by_day"."month_of_year")

    4. Go to the default member property, and copy /past the following script in, or click the ellipses button (...) and select the 'MDX' option, then copy and paste   --> StrToMember("[Time2].[" + CStr(Year(Now())) + "].[" + CStr(Year(Now())) + IIF(Month(Now()) < 10, "0" + CStr(Month(Now())), CStr(Month(Now()))) + "]")

    What you've done by changing the key (step 3 above) in the lowest level of the dim is make it easier to form a string that is a valid key value. Ie each member now has

    Step 4 is making use of the (automagically loaded) Excel/VBA library functions to manipulate the current (servers) date into a string that willmatch a dimension key. In this case it generates "[Time2].[2004].[200405]" ie the current month for me.

    We've had to do this style of thing occassionally, which has lead me to *always* use an integer as a date key in my time table (eg 19990522).  makes matching your keys a lot easier.

    Notes:

    A. I used Foodmart:sales to do up a demo

    B. you'll need to mod the mdb (the time_by_day table, add a record for the current date where you are)

    C. If you dimension is deep (ie a lot of levels) you can try either for a unique match on the key you generate, or you'll have fun trying to determine your quarters etc via VBA code

    Steve.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply