SSAS : Last 12 months data from cube returning NULL

Question

I run this query in SSMS 2012

SELECT YTD([Date].[Calendar].[Month].&[2003])ON 0 FROM [Adventure Works];

I am getting this error

Executing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet Formatting. Cell set consists of 1 rows and 0 columns. Done formatting. Execution complete

I want to select last twelve months from Cube and aginst that want to show some measures.

EDIT 1:

when I try to fire it against one of the measure it is showing null

SELECT YTD([Date].[Calendar].[Month].&[2003].[8])ON 0 ,
[Measures].[Internet Sales Amount] on 1
FROM [Adventure Works];

output is:

where i am lacking. somebody can redirect me to the right direction

Answer 1

Let's first look at YTD documentation - it expects member expression. You specified [Date].[Calendar].[Month].&[2003] - that's strange, as I suppose there is Year = 2003, not month. Anyway, if you want to get last 12 months, you should try

select
{
   [Measures].[Total Sales] // for example
} on 0,
{
   Descendants(
               Ancestors(
                          Tail(EXISTING [Date].[Calendar].[Day].members,1).Item(0),
                          [Date].[Calendar].[Year]
                        ) // end ancestors
            ,[Date].[Calendar].[Month]
              ) // end descendants

 }
 on 1
from [YourCube]

Now a bit of explanation. Tail(EXISTING [Date].[Calendar].[Day].members,1).Item(0) gives you last date existing in the cube Calendar dimensions. You need months, so there are two steps remaining:

• get year for this date
• get months for this year that exist in cube hierarchy

Ancestors here is used to get Year member (second parameter [Date].[Calendar].[Year]). Now we have moved to Year level, so we are ready to get all months using Descendants function, which gives all members of level related to [Year], here we specify [Month]. Please check listed functions MSDN documentation also