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

Currency formatting: how to change to use another locale... or is US format hard coded?

How to I change the formatting of my currency based members according to the actual currency that they are defined in rather than the default US format? (in my case GBP).

Answers to this can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=887100&SiteID=1

I got sorta the same problem..

|||

FYI: Direct link to answer:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

Am I right in thinking that I should create a Script Command in the Calculuations tab to set the appropriate languages for my measures?

eg:

Language([Measures].[GBPEQUIV-SENSITIVIES]) = 2057;

Language([Measures].[GBPEQUIV-STRESS]) = 2057;

BTW: I also changed the server locale to English-UK to get a default setting.

|||

Yeah I think you could do that, I have a script command like that with: Language(this) = 1053