Hi



I migrated an SQL SERVER AS2000 database to 2005, and this is giving me some
trouble concerning the time dimensions and YTD calculation.



In AS2000 I have defined a calculation like:



'iif([Date].CurrentMember.level.ordinal > 0,

Sum(PeriodsToDate([Date].[Year]),[Measures].[howmany]),

Sum(PeriodsToDate([Date Week].[Year]),[Measures].[howmany]))'



This means that the YTD calculation works for both the "Year, Quarter, Month
and Day" time dimension as well for the "Year, Week, Day" time dimension.
But I can't get this to work in AS 2005. It seems like only one time
dimension is "allowed". So my question is: Is only one time dimension
allowed in 2005 and how can I obtain the same functionality in 2005 that I
have in the 2000 calculation above.



Thanks in Advance



-Henrik.

Henrik,

It looks like we need a little more information here. Are the 2 time
dimensions you used in AS2K the same dimension but multiple
hierarchies, or are they 2 separate dimensions. The code you listed
looks like you are using 2 different dimensions, but it also could be 2
hierarchies.

This is important because if you used the AS2K5 Migration Wizard and
this was a multiple hierarchy then the wizard creates 2 separate
dimensions with differing names. If you could provide a little bit
more info on your cube make up I think someone here can help you.

V

Thanks much for the info, Henrik.

This is a little bit of a shot in the dark, but try this MDX for your
calculation and see if it works.

iif([Date].[Date].CurrentMember.level.ordinal > 0,

Sum(PeriodsToDate([Date].[Date].[Year]),[Measures].[howmany]),

Sum(PeriodsToDate([Date Week][Date Week].[Year]),[Measures].[howmany]))

This is one of the new nuances in AS2K5 that makes you declare the
dimension and the hierarchy. Make sure your dimensions are named [Date
Week] and [Date] and that they are functioning. I think this might
solve your problem.

V

Hi Henrik,

You might also try to explicitly specify the second parameter for
PeriodsToDate() - as in this recent post:

http://groups.google.com/group/micro...olap/msg/56110
fa88d8e0655
From: Deepak Puri
Date: Thu, 26 Jan 2006 19:52:46 -0800
Subject: Re: running value

Clint,

After experimenting with AS 2005 Adventure Works, there seems to be a
quirk in PeriodsToDate() behavior (versus AS 2000) which I can't quite
explain: if only a [Date].[Calendar] level is specified, but no member,
it returns members from the [Date].[Fiscal] hierarchy instead. So,
explicitly specify [Date].[Calendar].CurrentMember, like:

Sum(PERIODSTODATE([Date].[Calendar].[(All)],
[Date].[Calendar].CurrentMember),
[Measures].[Reseller Sales Amount])
Select {[Measures].[Reseller Sales Amount],
[Measures].[Cumulative Sales Amount]} on 0,
[Date].[Calendar].[Month].Members on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thank you both for your answers...

After rewriting the two time dimensions to one time dimension with two
hierarchies (yqmd and ywd) the following does the trick.

iif([Date].[yqmd].CurrentMember.level.ordinal > 1,
Sum(PeriodsToDate([Date].[yqmd].[Year],[Date].[yqmd].Currentmember),[Measure
s].[howmany]),
Sum(PeriodsToDate([Date].[ywd].[Year],[Date].[ywd].Currentmember),[Measures]
..[howmany]))

-Henrik.


"Henrik Hjøllund" <hjoellund (AT) mail (DOT) tele.dk> skrev i en meddelelse
news:43e3d41d$0$67259$157c6196 (AT) dreader2 (DOT) cybercity.dk...