Elon's Blog

Stuff that may be of use to someone…

MDX to return Current Week (SSAS)

Posted by Elon B. on January 29, 2009

I had an interesting problem at client site, using SSAS 2008, where I had to create a Named Set that returned the range of members for the Last 4 Weeks, from the current week number, e.g. [W05 09], for Week 5 in 2009, picked up from the current system date.

I had to use the following MDX to create a the named set

CREATE DYNAMIC SET CURRENTCUBE.[Last 4 Weeks] AS

strtoset(“LASTPERIODS( 4, [Time].[Week].[W”+ right(cstr(datepart ( “ww”, Now()) +100),2) +” “ + right(cstr(Year(Now())),2) + “])” ) ;

When viewed in Excel 2007, in a PivotTable connected to my Analysis Services Cube, (on 29 Jan 2009) this returned :

Row Labels

W02 09

W03 09

W04 09

W05 09

 

My [Time].[Week] Hierachy looked as follows:

.[Week] Hierachy”][Time].[Week] Hierachy

My StrToSet command basically produced this set for me:

LASTPERIODS( 4, [Time].[Week].[W05 09])

Which returned the last 4 weeks members.

I had to make use of the DATEPART functions to dynamically get the current week.

The DatePart script datepart ( “ww”, Now())” returned the number 5.

As I needed it to be “05”, I added 100 to it to give 105, then converted it to a string using “Cstr” to give “105” and finally used the “Right” function to return the last two characters “05”.

A list of all the DATEPART arguments can be found here: (DATEPART (Transact-SQL))  http://technet.microsoft.com/en-us/library/ms174420.aspx

SYNTAX:  DATEPART ( datepart , date )   
Arguments 

datepart

Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

TZoffset

tz

ISO_WEEK

isowk, isoww

 

I also saw that the FORMAT function could be useful to return other dyanamic time related attributes with your MDX queries.

See here: (User Defined Date/Time Formats)

http://msdn.microsoft.com/en-us/library/73ctwf33(VS.80).aspx

 

Advertisements

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: