Hello,

I have a calculated member that that returns the last non empty value
accross dimensions time and account:

nonemptycrossjoin(
{[account].[market value]), {periodstodate([Time].[(All)],
[time].currentmember)}
).
Item(
nonemptycrossjoin(
{[account].[market value]), {periodstodate([Time].[(All)],
[time].currentmember)}
)
).count - 1)


***** How can I get the the actual date? (i.e. the "name" property of the
time dimension member used in the tuple?)


Thanks in advance

Eric

Hi Eric,

First of all, to get the last tuple in a set I would recommend using the
TAIL function rather than the approach you're using below - it will be more
efficient. But, to answer your question, once you have the tuple you want
you can simply reference a member within it using the ITEM function. Here's
an example from Foodmart 2000:

WITH
MEMBER MEASURES.GETLASTPRODUCT AS '
TAIL(
NONEMPTYCROSSJOIN([Product].[Product Name].MEMBERS, [Time].[Month].MEMBERS)
,1).ITEM(0).ITEM(0).NAME'
MEMBER MEASURES.GETLASTMONTH AS '
TAIL(
NONEMPTYCROSSJOIN([Product].[Product Name].MEMBERS, [Time].[Month].MEMBERS)
,1).ITEM(0).ITEM(1).NAME'
SELECT {MEASURES.GETLASTPRODUCT, MEASURES.GETLASTMONTH} ON 0,
[Customers].[All Customers].[USA].[WA].[Seattle].CHILDREN ON 1
FROM SALES

What this query does is, for each customer, display the last product they
bought and the month they bought it. I use NONEMPTYCROSSJOIN to find the set
of all tuples containing a (Product, Month) combination, and then use the
TAIL funtion to get a set containing one member - the last tuple in the
original set. Since I now have a set with one member, a tuple, I can get at
that tuple using .ITEM(0). And then, within that tuple, I can get at the
Product member it contains by using .ITEM(0) and the Time member it contains
by using .ITEM(1). Finally, once I have the member I can use the .NAME
function.

You might also want to check out the EXTRACT function, which can be useful
in these circumstances, and the final parameter of NONEMPTYCROSSJOIN that
allows you to filter by dimensions but not include them in the set returned.

HTH,

Chris

--

Chris Webb
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
no rights.

"Eric Bowden" <ehbowden (AT) nospamyahoo (DOT) com> wrote