"Now" for time dimesion selection

A common requirement we see is to report against a sliding time window.
However, as far as I can tell, SSAS has no native ability to give me the "Now" time dimension member (that is, the year, month, day, ..., dimension member for the current moment in time). Is that correct?

Are there any techniques people have used to derive that info? It seems like something other people must have run in to.

Kevin,

You can use the VB Now() function and then format the result to build a member matching the format you use for the date in your time dimension. Here is an example against "Adventure Works". I had to subtract 900 days from the current time to render a member that exists in the "Date" dimension included with "Adventure Works", but I think you will get the idea.

WITH

MEMBER [Now Formatted]

AS

Format(Now() - 900,"MMMM d, yyyy")

SET [Current Day]

AS

StrToSet("[Date].[Calendar].[Date].[" + [Now Formatted] + "]")

SELECT

{[Current Day] } ON COLUMNS,

{[Measures].[Order Count],[Now Formatted]} ON ROWS

FROM

[Adventure Works]

|||

It is not good style to concatenate the unique or fully qualified name of members.

Another much more elegant way in the AS2005 is to use MemberValue.
MemberValue is a strongly typed value. You shouldn't use awkward formatting.

|||

Vladimir is correct in making the observation that the previous solution I offerred is not optimal. I was posting a "hammer and nails" approach to the question. As Vladimir points out, a better method would be to set the "Member Value" property of your date attribute to be the date from your dimension table. You could then use the following MDX:

WITH

SET [Current Day]

AS

Filter([Date].[Calendar].[Date].Members,

[Date].[Calendar].CurrentMember.MemberValue = (Round(Now()) - 900))

SELECT

{[Current Day] } ON COLUMNS,

{[Measures].[Order Count]} ON ROWS

FROM

[Adventure Works]

HTH,

Steve

|||

There are many ways to make a sliding time member in MDX. It all depends on your business requirements and how you have constructed your cube.

One approach is already presented.

The second way will work if you have a time dimension with members only to the current date. In this case you start at the top of your time dimension and use(MDX) lastchild (from the top member)until you hit the right level and member.

The third approach will work if you have a time dimension that extends across the current date to the full year or an additional year. This is the approach when you have budgets or forcasts involved. In this case you will have to find the last measure that it is not empty. MDX Tail() and Filter() on a measure that shows the current date(like actual sales) will help you here. Make a named set of this Time member.

Mosha and his cowriters have an interesting solution to the third approach in "Fast Track To MDX-Second edition". They are talking about recursion in a chapter in this book.

HTH

Thomas Ivarsson