CODEWITHSUNDEEP
sql-server-2012ssasmdxcubessms-2012
Brave Soul
Brave Soul

Reputation: 3620

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

Upvotes: 0

Views: 993

Answers (2)

whytheq
whytheq

Reputation: 35605

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

Upvotes: 2

George
George

Reputation: 702

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

Upvotes: 2

Related Questions