How can i make cube to piont to current month whenever i browse the cube
Time dimension has levels Year,Month,Week.
Any idea will be appreciated

The default member for each dimension is usually set to the first member in
the dimension, for example (All) or the first year of the Time/Period
dimension if there is no (All) level. It's trivial to change any dimension's
default member to be another specific member, by changing the aptly named
"Default Member" property in the dimension editor.

But what if you want that default member to be dynamic? For example, let's
say you want the default member for the Time/Period dimension to be the
current month. As usual, let's use FoodMart as our example. Edit the Time
dimension and change the "Default Member" property. Our goal is to create an
MDX expression that returns a member, like:

Time.[ThisYear].[ThisQtr].&[ThisMonthNumber]

But we don't have parameters, so we need to construct a string and then
convert that string to a member. I'm writing this note in Jan-2001, and the
first piece I'm going to get right is the month:

StrToMember("Time.[1998].[Q1].&["+Str(Month(Now()))+"]")

A couple of points here:
- Now() is a VBA function that returns the current date-time
- Month() is a VBA function that returns the month number (eg 1) of a date
- Str() is a VBA function that casts the month number to a string
- The "&" lets me refer to the month by number [1..12] rather than by name.
These happen to be the same in the FoodMart time dimension, but I often
construct my time dimension with nicer labels that that.
- Everything inside the StrToMember function resolves to the string
"Time.[1998].[Q1].&[1]" (since I'm writing in January). Then the StrToMember
function casts that string to a member.

Let's work on the quarter next. Adding the quarter logic makes our MDX
expression much longer, but it's really not very complicated. I'm just
nesting three Iif statements that assign the quarter based on the month:

StrToMember("Time.[1998].Q"+iif(Month(Now())<=3,"1",iif(Month(Now())<=6,"2 ",
iif(Month(Now())<=9,"3","4")))+"].&["+Str(Month(Now()))+"]")

Finally, let's do the year. In theory this should be as simple as the month,
but FoodMart makes an awkward example since it only has two years, 1997 and
1998. I've decided to create my default member so that it points to the
current year if the current year is <= 1998 (very unlikely at this point),
else it uses 1998. Of course it will always use 1998, but I think it's
useful to show the logic:

StrToMember("Time.["+Iif(Year(Now())>=1998,"1998",Str(Year(Now())))+" ].[Q"+i
if(Month(Now())<=3,"1",iif(Month(Now())<=6,"2",iif (Month(Now())<=9,"3","4"))
)+"].&["+Str(Month(Now()))+"]")



--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Prasanna" <tawargerip (AT) hotmail (DOT) com> wrote

Thanks Sean for the help
but i had to use LTRIM and RTRIM and taking into consideration Year
,Month,Week levels only changed the formula

strtomember("Time.[" + IIf(Month(Now()) = 1,
LTrim(RTrim(Str(Year(Now()) - 1))), LTrim(RTrim(Str(Year(Now()))))) +
"].&[" + IIf(Month(Now()) = 1, LTrim(RTrim(Str(12))),
LTrim(RTrim(Str(Month(Now()) - 1)))) + "]")


Works fine
================================================== ===========================

"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote

Prassanna
Sean's example is perfectly acceptable however I think there's a *slightly* easier way

1. You could have a view on your Time dimension table which only pulls out records where the date is before or equal to today (pretty simple in any RDBMS)
2. You base your Time dimension on this view rather than the underlying table
3. Incrementally process the dimension every day/night/hour/period/whatever in which you process your cube
4. Your Defaultmember property should be set to Time.lastchild.lastchild (Stick another .lastchild onto it if you want the current week

Although some people may consider this to be a slightly inelegant solution I quite like it because it means you only see Time dimension members up to and including today. This in turn means that your dimension doesn't contain unused dimension members and your cube is less sparse

Of course, this doesn't work if your cube contains budgetry/forecasting data because you will need future members in your Time dimension

Regard
Jami


----- Prasanna wrote: ----

How can i make cube to piont to current month whenever i browse the cub
Time dimension has levels Year,Month,Week
Any idea will be appreciate

There's potentially a lot of different ways to do this. Another way could
be to have member property for current day and update the underlying record
in the dimension table as part of your ETL process. Then you could set the
default member to a simple MDX FILTER statement. That would be fairly
simple as well. Personally, I prefer an approach where the time dimension
only contains days up to and including the current day, but there are plenty
of cases where that becomes problematic (for example, budgeting and
forecasting)

Sean


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Jamie" <jamiekthomson (AT) blueyonder (DOT) co.uk> wrote

RDBMS).
and including today. This in turn means that your dimension doesn't contain
unused dimension members and your cube is less sparse.

Brilliant !!

However - why are you at year-level testing to see if year is larger than
1998 ? Why not just year - now...


Sean Boon [MS] <seanboon (AT) online (DOT) microsoft.com> wrote

In my time dimension my month level is named by month name - not numbers -
however I have the month number as a member property - how do I adjust the
sentence using a member property with month(now()) and then getting the
levels month name back ?

Michael Vardinghus <mivar (AT) wmdata (DOT) dk> wrote