I have a date dimension which is broken into levels year month day,
then I have a measure called count, what I need to do is show the
measure count for all but the lastchild of each of the levels.
etc

Date [measures].[count]

-2003 (last child not shown)
- Jan 2
- Feb 4
- March (last child not shown)
- 1st 3
- 2nd 5
- 3rd 7
- 4th (last child not shown)

I tried writing something like

iif([Date].currentmember <> [date].currentmember.lastchild,
[measures].[count], null)

but this doesnt do anything so anyone have any ideas?
cheers
Dean

"Dean" <dean.shackley (AT) logicacmg (DOT) com> wrote

This can be done by using either new calculated member and hiding the
measure [count], or by calculated cell.
I assume you have chosen calculated member approach. Then the expression
could look like

IIF( [Date].CurrentMember IS [Date].Parent.LastChild, NULL,
Measures.[Count] )

Two notes:

1. Operator "=", "<>" etc will compare _values_ of the cells, not the
members. To compare members use "IS" operators (possibly with conjunction
with "NOT" operator).
2. I assumed, that [Date] dimension has All level, therefore Parent of
[2002] is [All Dates]. Otherwise, there will have to be special test for the
first level, since .Parent will return NULL member for it.

HTH,
Mosha

==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"Dean" <dean.shackley (AT) logicacmg (DOT) com> wrote

Dean, I just tried this expression in the Foodmart

with member measures.x as 'iif(Time.CurrentMember is Time.Parent.LastChild,
NULL, [Measures].[Units Shipped])'
select {x} on 0 from warehouse
where [Time].[1997].[Q3]

and it worked fine.
You are using Analysis Services 2000, not OLAP Services 7.0, right ?

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Simpler boolean expression would be simply

[date monthly].currentmember.properties("ID") = [date
monthly].currentmember.parent.lastchild.properties("ID")

There are several ways to do it. For example

Tail([date].[day].members, 1).Item(0)

should produce last day.

HTH,
Mosha.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"Mosha Pasumansky [MS]" <moshap (AT) microsoft (DOT) com> wrote

thanks Mosha, worked ace!