dimension cannot have more than one hierarchy.hierarchy must be specified explicitly.

Hi

I am new to Analysis services 2005.I have a calculated measure.costval which has following:

CREATE MEMBER CURRENTCUBE.[Measures].costval

AS IIf([Dim Date_Financial].CurrentMember.Level.Name="Date",

IIF([Dim Date_Financial].CurrentMember.Level.Name="(All)",

([Dim Date_Financial].CurrentMember.lastChild),

IIf([Dim Date_Financial].CurrentMember.Level.Name="Year",

([Date].[Financial].CurrentMember.lastChild),

IIf([Dim Date_Financial].CurrentMember.Level.Name="FQuarter",

([Dim Date_Financial].CurrentMember.lastChild),

IIf([Dim Date_Financial].CurrentMember.Level.Name="FMonth",

([Dim Date_Financial].CurrentMember.lastChild),

IIf([Dim Date_Financial].CurrentMember.Level.Name="FWeek",

([Dim Date_Financial].CurrentMember.lastChild),

(Descendants([Dim Date_Financial].CurrentMember,[Dim Date_Financial].[Date]).Item(0).LastSibling,[Measures].[Cost Value])

VISIBLE = 1 ;

After deploying wen i view in browser i get #value error.Please help me to fix this up

Regards

Usha

Questions:

-Is ([Dim Date_Financial] the dimension or the hierarchy. Normally [Date] is the dimension and [Date].[([Dim Date_Financial] the user hierarchy in the dimension.

-You are mixing dimension members like ([Dim Date_Financial].CurrentMember.lastChild) with measures like [Measures].[Cost Value]. You are trying to point to a member in your ([Dim Date_Financial] dimension

Run the Business Intelligence Wizard for the time/date dimension and have a look at its results in the calculations tab in the BI Developer Studio.

Regards

Thomas Ivarsson

Hi Thomas

Thanx so much 4 ur reply .Dim Date_Financial is my dimension not hierarchy....

Regards

Usha

Hello Usha. It is very hard to know about you business logic but can you tell us about your hierarchies after [Dim Date_Financials]

You are trying to point at a [Dim Date_Financials] member depending on a choosen member in that hierarchy. The problem can be that your parent is a measure and that you sometimes point at a time member and sometimes a measure in your calculation.

You will have to supply a little mote details about what you woud like to do.

Regards

Thomas Ivarsson

Hi Thomas,

Thanks so much 4 ur reply.

I am trying to calculate costval,a calculated measure.Date_Financial is the dimension which has one hierarchy...Financial with

FYear,FQuarter,FMonth,FWeek,Date as members.

if the value of date_Financial is Date it should pick the value of costval directly...if its Fyear,FQuarter,FMonth it shld pick up last child of date_financial dimension as costval is semi additive measure.....

the formula works fine in 2000.only in 2005 its givin this err.

thanks in advannce

Regards

Usha

Hi thomas,

I was able to fix the error.Thanks

Regards

Usha