MDX selecting a list of years from Date dimension

Question

So I have a Date dimension and one of the members is the 4 digit year, eg 2012

Im still pretty new with MDX.

Im looking at an example using AdventureWorks 2008 DW. The following will get the value for 2008, but I want a list of values for ALL the year members.

WITH MEMBER Measures.ValueColumn as [Date].[Calendar].[July 1, 2008].MemberValue
MEMBER Measures.KeyColumn as [Date].[Calendar].[July 1, 2008].Member_Key
MEMBER Measures.NameColumn as [Date].[Calendar].[July 1, 2008].Member_Name

SELECT {Measures.ValueColumn, Measures.KeyColumn, Measures.NameColumn}  ON 0
from [Adventure Works]

Results:
ValueColumn KeyColumn   NameColumn
7/1/2008    20080701    July 1, 2008

What would I change to get a list of year values for the current year and previous 5 ?

This gives me a list of all the Calendar Year members in the Adventure Works cube

SELECT NULL ON COLUMNS,
[Date].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works];

results:

All Periods
CY 2005
CY 2006
CY 2007
CY 2008
CY 2010

Those are the member names, but i want their values

whytheq whytheq · Accepted Answer · 2015-09-09T19:11:35

You can use the currentmember function to help combine the two scripts you've mentioned:

WITH 
  MEMBER [Measures].[ValueColumn]AS
     [Date].[Calendar Year].CURRENTMEMBER.MemberValue
  MEMBER [Measures].[KeyColumn] AS
     [Date].[Calendar Year].CURRENTMEMBER.Member_Key
  MEMBER [Measures].[NameColumn] AS 
     [Date].[Calendar Year].CURRENTMEMBER.Member_Name
SELECT 
   {
     [Measures].[ValueColumn]
   , [Measures].[KeyColumn]
   , [Measures].[NameColumn]
   }  ON 0
  [Date].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works];

If you want to avoid the All member then add in the level to the expression [Date].[Calendar Year].MEMBERS ON 1 like this:

WITH 
  MEMBER [Measures].[ValueColumn]AS
     [Date].[Calendar Year].CURRENTMEMBER.MemberValue
  MEMBER [Measures].[KeyColumn] AS
     [Date].[Calendar Year].CURRENTMEMBER.Member_Key
  MEMBER [Measures].[NameColumn] AS 
     [Date].[Calendar Year].CURRENTMEMBER.Member_Name
SELECT 
   {
     [Measures].[ValueColumn]
   , [Measures].[KeyColumn]
   , [Measures].[NameColumn]
   }  ON 0
  [Date].[Calendar Year].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works];

MDX selecting a list of years from Date dimension

1 Answers