The heading may be a bit cryptic, but what I need to do is to return the
last date for a measure. I need to see on what date an employee registered
an invoice.
I've tried using Tail, bottomcount and other functions, but I usually end up
with the value of the measure, not the value of the date.

I have a Date dimension, broken into Year, Month and Day. I also have a
Period dimension, broken into Year, Half year, quarter and month.

This query returns the totals for all days that match month in Period, and
the Day, Month and Year for the dates:

select Tail(Filter([Period].[Month].members,(NOT IsEmpty([Measures].[Total
Hours])))) on columns,
non empty crossjoin({[Employee].&[1005]}, order([Date].[Day].members,
[Date].currentmember.name, desc)) on rows
from [MyCube]

Is there a way to get it to return the UniqueName of the Date members, and
just have it return the first row?

Kaisa M. Lindahl

Maybe something like this will suffice?

'[Date].CurrentMember.UniqueName'

select {[Measures].[DateName]} on columns
from [MyCube]
where (Tail(NonEmptyCrossJoin([Date].[Day].Members,
{[Employee].&[1005]},
{[Measures].[Total Hours]}, 1)).Item(0))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Works like a charm, Puri!
Thanks a lot, once again.

Kaisa M. Lindahl

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

THis is an update of an old message.
I need to get the last dates of a measure for several levels in a dimension.
I need to return the last date for all emplyees in an office. The mdx query
that Deepak Puri suggested (below) returns the date for the first employee,
and shows this date for all employees in that office. How can I make it
check for each employee?

The dimension looks like this:
[All]
[Office]
[Employee]

All help appreciated!!!

Kaisa M: Lindahl



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote