I have two time dimensions: Time_Incurred_YrMth and Time_Paid_YrMth.
Structures and content are identical.

When I test the linkmember function on its own, everything is fine.
LinkMember([TIME_INCURRED_YrMth].currentmember,[TIME_PAID_YrMth]).

But, when I use this in the following expression, the syntax is ok but the
grid displays #ERR:

SUM(CROSSJOIN(Ytd([TIME_INCURRED_YrMth]),
{[TIME_PAID_YrMth].CURRENTMEMBER.LEVEL.MEMBERS.ITEM(0):
LinkMember([TIME_INCURRED_YrMth].currentmember,[TIME_PAID_YrMth])
..nextmember}),(CalculationDimension.&[1]))

Essentially, the above expression looks at when something was billed (e.g.,
Time_Incurred) and when something was paid (e.g., Time_Paid). I pick up all
services billed for a given time_incurred (YTD), and crossjoin against when
the services were paid within one additional month beyond time_incurred.

So, if services were billed YTD(March 2005), then the LinkMember() should
generate a Time_Paid member that is April 2004.

Anyone know why I get a #ERR?

Thanks,
Pete

--
Message posted via http://www.sqlmonster.com

Correction -- I mis-typed the last statement. The linkmember should
generate April 2005 (not April 2004).
--Pete

--
Message posted via http://www.sqlmonster.com

There's a couple of questions raised by this MDX:

- is YTD([TIME_INCURRED_YrMth]) really supposed to be:
YTD([TIME_INCURRED_YrMth].CurrentMember)?

- What is selected on the [TIME_PAID_YrMth] dimension in this scenario?
Unless the selected member is always at the same level as
[TIME_INCURRED_YrMth].currentmember, the range on [TIME_PAID_YrMth]
dimension will not be valid.



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

"There's a couple of questions raised by this MDX:

- is YTD([TIME_INCURRED_YrMth]) really supposed to be:
YTD([TIME_INCURRED_YrMth].CurrentMember)?"

--> Yes, it is .currentmember


"- What is selected on the [TIME_PAID_YrMth] dimension in this scenario?
Unless the selected member is always at the same level as
[TIME_INCURRED_YrMth].currentmember, the range on [TIME_PAID_YrMth]
dimension will not be valid."

--> Agreed. Through some more testing, I discovered that the current
selection of [TIME_PAID_YrMth] affects the results. Consequently, the MDX
expression I thought was broken actually works, but only under the
circumstances you noted in your email.

Ideally, I'd need to have [TIME_PAID_YrMth] reflect the most current month
(e.g., June 2005). Is there a way to setup the time dimension to default to
the current month rather than [(All)]?

Thanks for your help,
--Pete

--
Message posted via http://www.sqlmonster.com

Sure...I usually use something like this as my default member
expression in my time dimension.

Tail(NonEmptyCrossJoin([Time].Month.Members)).Item(0).Item(0)

In typical MDX fashion...it's a somewhat cryptic way of saying give me
the most current member of the time dimension's month level that
happens to have data in the fact table.