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