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

Cube Translation

Hi,

I'd like to automate cube translation for browsing and i'm wondering how i can do that.

Actually i have a translation table that contains all my labels and the corresponding translation. I want to integrate these translations without using the manual interface in the cube (too long and to avoid possible mistakes).

Does anyone have an idea?

thanks

You can use AMO ( Analysis Management Objects) object model to create objects. Try and see samples of how to program with AMO installed into C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.