Current Year Data

I have an MDX DataSet that gets the data for 2007 (hard coded). I want to automate it to get the current year data so that I don't have to manually update the code in 2008.

The data set is as follows:

SELECT NON EMPTY {[Measures].[Fixtures]} ON COLUMNS,

NON EMPTY {( [Charterer].[Current Short Code].[Current Short Code].ALLMEMBERS )} ON ROWS

FROM ( SELECT ( { ([CP Date].[Year].&[2007]) } ) ON COLUMNS FROM [Voyage Analysis])

I got as far as being able to find the current year

WITH MEMBER [Measures].[ThisDay] AS Now()

MEMBER [Measures].[ThisYear] AS 'YEAR([ThisDay])'

SELECT {[ThisDay],[ThisYear]} ON COLUMNS

FROM [Voyage Analysis]

But I have no idea how to replace "&[2007]" with , "[ThisYear]".

As you can see I'm not very experienced with MDX, appreciate if anyone can help.

Thanks

Richard

You could build a string version of the year member, then use StrToMember():

SELECT NON EMPTY {[Measures].[Fixtures]} ON COLUMNS,

NON EMPTY {( [Charterer].[Current Short Code].[Current Short Code].ALLMEMBERS )} ON ROWS

FROM ( SELECT ( { StrToMember("[CP Date].[Year].&["

+ CStr(Year(Now())) + "]") } ) ON COLUMNS FROM [Voyage Analysis])

|||

Thanks. Perfect!

Richard