sql-server-2012ssasmdxcubessms-2012

SSAS : Last 12 months data from cube returning NULL


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:

enter image description here

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


Solution

  • 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:

    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