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:

enter image description here

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

George · Accepted Answer · 2015-02-17T11:16:21.243

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

actually it is not an error, query just gives no result. Can you try extracting single member, like [Date].[Calendar].[Month].&[2003].[8], without YTD function? — George, Feb 17 '15 at 11:14

whytheq · Answer 2 · 2015-02-17T13:37:38.613

Try this:

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

It returns the year to date using the level we have specified i.e. days:

enter image description here

We could then aggregate the above using a custom measure like so:

WITH 
  MEMBER [Date].[Calendar].[YTDtotalTo14jan] AS 
    Aggregate(YTD([Date].[Calendar].[Date].&[20070114])) 
SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,[Date].[Calendar].[YTDtotalTo14jan] ON 1
FROM [Adventure Works];

Looks like you just picked a bad year 2003 !

To return the last 12 months I'd use the Tail function:

SELECT 
  {} ON 0 //<<add whatever measures you like in here
 ,Tail
  (
    [Date].[Calendar].[Month]
   ,12
  ) ON 1
FROM [Adventure Works];

p.s. this runs fine in my SSMS:

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

This is not an Error message...

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

SSAS : Last 12 months data from cube returning NULL

2 Answers2