Hi all,

Thanks to Deepak who saved me and pointed me towards the Dimension
Granularity in AS2005 I now have a functioning Time Dimension.

I have two hierarchy's... Actual Year and Fiscal Year.

I also have a number of Calculated Members for YTD:
iif(Sum(Ytd(),[measures].[Fees])=NULL,NULL,Sum(Ytd(),[Measures].[Fees]))



However my YTD figures are all 0's... How do I do a YTD calculation with
multiple hierarchys?


Clint

Hi Clint,

Since there are 2 hierarchies with Year, you might try explicitly
specifying one or the other in YTD(). Each Year attribute should also be
tagged with type: "Year".

Sum(Ytd([Time].[Actual Year].CurrentMember),
[Measures].[Fees])


http://msdn2.microsoft.com/en-us/lib...S,SQL.90).aspx

Returns a set of sibling members from the same level as a given member,
starting with the first sibling and ending with the given member, as
constrained by the Year level in the Time dimension.

Syntax

Ytd( [ Member_Expression ] )
Arguments

Member_Expression
A valid Multidimensional Expressions (MDX) member expression.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak...

Thanks for this, how does one tag the year attribute as a "Year"??

Clint

Check that the Type of the attribute is set accordingly:

http://msdn2.microsoft.com/en-us/lib...e_dimension_at
tribute_types

In Microsoft SQL Server 2005 Analysis Services (SSAS), attribute types
help classify an attribute in terms of business functionality. There are
many attribute types, most of which are used by client applications to
display or support an attribute. However, some attribute types also have
specific meaning to Analysis Services. For example, some attribute types
identify attributes that represent time periods in various calendars for
time dimensions.
...
Time dimension attributes

These values identify an attribute that belongs to a time dimension.
For more information about time dimensions, see Time (SSAS).
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

My attribute is set to Fiscal Years as the year in Australia Runs from July
to June.. In AS 2000 I just configured the start of the year as July 1. In
AS2005 I seem to have to create a Fiscal Year Hierarchy..

Will YTD work with Fiscal Year?

It appears that after setting the type to Year the code now works.. Thanks
for your assistance again!!

Clint
"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

Not sure if YTD() work with a Fiscal Year attribute type, but you can
try PeriodsToDate(), which doesn't depend on the level attribute type,
like:

PeriodsToDate([Time].[Fiscal Date].[Year],
[Time].[Fiscal Date].CurrentMember)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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