MDX to return Current Week (SSAS)

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”]

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

 

Advertisement