troyh78


I am trying to report on a measure called 'SPEND DOLLAR Monthly Budget Sales Total' and the equivalent YTD in a second column. The only way I am able to bring up *seemingly* meaningful data is using the following MDX:

WITH MEMBER [Measures].[SPEND DOLLAR Monthly Budget Sales Total YTD]
AS Sum(PeriodsToDate([Time].[(All)]), [Measures].[SPEND DOLLAR Monthly Budget Sales Total])
SELECT
{[Measures].[SPEND DOLLAR Monthly Budget Sales Total],[Measures].[SPEND DOLLAR Monthly Budget Sales Total YTD]} ON COLUMNS,
[Time].[Calendar Month].Members on ROWS
FROM [PromaxAnalytx]

returns

SPEND DOLLAR Monthly Budget Sales Total SPEND DOLLAR Monthly Budget Sales Total YTD
All 159375196 159375196
April 6101888 6101888
August 21012273 27114161
December 11303064 38417225
February 10544187 48961412
January 10122657 59084069
July 5524275 64608344
June 17515559 82123903
March 13849411 95973314
May 25481459 121454773
November 12462066 133916839
October 11720277 145637116
September 13738080 159375196

This looks correct (except I need to fix a problem with the sort order of the [Time].[Calendar Month] dimension attribute).

If I change the ON ROWS clause to any other dimension attribute, the data looks incorrect, for example Calendar Quarter:

WITH MEMBER [Measures].[SPEND DOLLAR Monthly Budget Sales Total YTD]
AS Sum(PeriodsToDate([Time].[(All)]), [Measures].[SPEND DOLLAR Monthly Budget Sales Total])
SELECT
{[Measures].[SPEND DOLLAR Monthly Budget Sales Total],[Measures].[SPEND DOLLAR Monthly Budget Sales Total YTD]} ON COLUMNS,
[Time].[Calendar Quarter].Members on ROWS
FROM [PromaxAnalytx]

incorrectly returns YTD as the repeated value of the measure:

SPEND DOLLAR Monthly Budget Sales Total SPEND DOLLAR Monthly Budget Sales Total YTD
All 159375196 159375196
1 34516255 34516255
2 49098906 49098906
3 40274628 40274628
4 35485407 35485407


and the one I really want, Period Month:

WITH MEMBER [Measures].[SPEND DOLLAR Monthly Budget Sales Total YTD]
AS Sum(PeriodsToDate([Time].[(All)]), [Measures].[SPEND DOLLAR Monthly Budget Sales Total])
SELECT
{[Measures].[SPEND DOLLAR Monthly Budget Sales Total],[Measures].[SPEND DOLLAR Monthly Budget Sales Total YTD]} ON COLUMNS,
[Time].[Period Month].Members on ROWS
FROM [PromaxAnalytx]

behaves similarly

SPEND DOLLAR Monthly Budget Sales Total SPEND DOLLAR Monthly Budget Sales Total YTD
All 159375196 159375196
April 16863770 16863770
August 12749429 12749429
December 12123204 12123204
February 10505766 10505766
January 10463188 10463188
July 16841013 16841013
June 17485704 17485704
March 9468736 9468736
May 14194576 14194576
November 11561596 11561596
October 13310850 13310850
September 13807364 13807364

My time dimension is not sourced from a 'server time' dimension but from a view from my DSV. It was selected as a time dimension in the cube wizard.

I would appreciate any suggestions.





Re: PeriodsToDate MDX only works for one dimension attribute.

Darren Gosbell


PeriodsToDate will use the attribute relationships to figure out which months and quarters belong to which year. It looks like there might be an issue with the way your attribute relationships are defined. Your month attribute should be related to your quarter attribute, which in turn should be related to the year attribute and the year and quarter should not have a direct relationship to the key attribute as you can navigate to them through month. It's hard for me to be exact because I don't know the full structure of your dimension, but that is the general concept.

And if you want a YTD amount then you should be passing the PeriodsToDate function a year level, not an all member/level.

eg.

Sum(PeriodsToDate([Time].[Calendar Year].[Calendar Year]), [Measures].[SPEND DOLLAR Monthly Budget Sales Total])

Note that I have Calendar Year in there twice. This is because I am using the format of <dimension>.<hierarchy>.<level> which is unambiguous. When you passed in [Time].[(All)] it was ambiguous and I'm guessing that SSAS is grabbing the first All level that it finds which is probably [Time].[Calendar Month].[(All)] which would explain why that attribute "works" and the others don't.







Re: PeriodsToDate MDX only works for one dimension attribute.

troyh78

Thanks Darren, I found that by referring specifically to the year, and by inserting some missing attribute relationships you suggested I have both corrected the sort order of the month dimension attribute, as well and get an accurate YTD. Thanks for your help.

For the record I used:
Sum(Periodstodate([Time].[Period Month Hierarchy].[Period Year],([Time].[Period Month Hierarchy].currentmember)),[Measures].[SPEND DOLLAR Monthly Budget Sales Total])