I need the date of my Last Day with data. This works fine in a SELECT
statement:

SELECT {[Measures].[MyCount]} ON 0,
Tail(NonEmpty(Time.Date.MEMBERS,[Measures].[MyCount]),1).Item(0) ON 1 FROM
MyCube

returns: '02/06/2006' 123

When I use the same expression to define a calculated member of the Time
dimension [Time].[Date].[Last Day], I get

returns: 'Last Day' 123

What I cannot figure out is how to write a query to return what DATE [Last
Day] represents!

peterg

Maybe this sample Adventure Works query will help:

iif(NonEmpty([Date].[Calendar].[Date].MEMBERS,
{[Measures].[Order Quantity]}).Count = 0, NULL,
Tail(NonEmpty([Date].[Calendar].[Date].MEMBERS,
{[Measures].[Order Quantity]})).Item(0).Item(0).MemberValue)

select {[Measures].[LastOrderDate]} on 0,
Non Empty [Product].[Product Categories].[Subcategory].Members on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

How can this be changed to work with the AS 2000 FoodMart cube?

I tried with this query:
With Member [Measures].[LastOrderDate] as
'iif(NonEmptycrossjoin([Time].[Month].MEMBERS,{[Measures].[Unit
Sales]}).Count = 0, NULL,
Tail(NonEmptycrossjoin([Time].[Month].MEMBERS,{[Measures].[Unit
Sales]})).Item(0).Item(0).MemberValue)'
select {[Measures].[LastOrderDate]} on 0,
Non Empty [Product].[Product Category].Members on 1
from [Sales]

- but it failed with this error message:
---------------------------
MDX Sample Application
---------------------------
Unable to display cell properties.

Formula error - cannot find dimension member ("MemberValue") - in a
name-binding function

An error occurred during attempt to get a cell value.
---------------------------
OK
---------------------------

- Any suggestions?

Kaisa M. Lindahl

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

Hi Kaisa,

..MemberValue is added in AS 2005 - in AS 2000, the only thing that I can
think of is to define the Date string as a Member Property, then cast it
to a data using CDate():

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!272.entry
The MemberValue property is something I saw in BOL quite a while ago,
but didn't really understand what it was for. It wasn't until George,
Siva et al started discussing it for 'MDX Solutions' that I really
grasped why it is so useful: it's for those occasions when you want to
tie a value other than a caption to a member, and don't want to go to
the hassle of creating a separate attribute for that value. So, for
example, imagine all of your Products have a weight attribute (eg 10Kg,
15Kg, 80Kg) and you want to run queries like a) Show me Sales for
products which weigh 10Kg, and b) Show me sales broken down by weight
for products which weigh more than 30Kg. In AS2K you might have ended up
doing this by having two separate member properties for weight: one
which contained a user-friendly string, such as "10Kg", which you might
create a virtual dimension from or display in a query and a second,
containing just the numeric value such as "10", to make filtering easier
(although you'd still have had to cast it to an integer before you could
do this). In AS2005 all you need to do is tie the user-friendly
description to the NameColumn property and the filter-friendly value to
the ValueColumn property (which, incidentally, is typed).The MemberValue
property of a member then allows you to get this value back in MDX.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Since this is a measure, the last order date shows up under each year in a
pivot table. I would rather LastOrderDate act like a member than a measure
since it only has one value over multiple years. I wasn't able to get it to
work with any dim except measures, so displaying it is a problem. Any
suggestions?

"Deepak Puri" wrote: