Hi

I have 2 dimensions Person and Answer with two levels "Field" and lower
level "Name".
Now with the below MDX query i get the output

Person Answer
__________________________
Kimberly Accepted
Kimberly Declined
Tom Enrolled
Kevin NotInterested

MDX Query
___________
with
set [FinalSet] as 'NonEmptyCrossJoin(except({[Person].[Name].Members},
({[Person].[All Person]})), [Answer].[Name].members,
{[Question].[DMStatus]}, 2)'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&[DMStatus])


I want the Last Answer for that person
The output should be

Person Answer
__________________________
Kimberly Declined (This is the lamost recent answer)
Tom Enrolled
Kevin NotInterested

I tried lot of options like LastChild, Closingperiod but all in vain.

Please help

Thanks in advance

Milind

I don't really understand the logic of what you are trying to do, it
seems strange that you are looking for the "Last" of something, but
there is no mention of a time dimension.

But that concern aside, you could try something like the following,
using the tail function and a second nested crossjoin. I have not been
able to test this so I hope there are no syntax errors in there.

I have broken out my change so that you can see it easier.


SET [FinalSet] as 'NonEmptyCrossJoin(except({[Person].[Name].Members},
({[Person].[All Person]})),

TAIL(NonEmptyCrossJoin([Answer].[Name].members,{Person.CurrentMember},
{[Question].[DMStatus]}, 1))

)'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&[DMStatus])
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132243859.351157.260150 (AT) g43g2000cwa (DOT) googlegroups.com>,
milind.bhabal (AT) gmail (DOT) com says...

Thanks Darren for the response.

Sorry, I do have a time dimension called [ModifiedTime] with levels
[Year], [Month], [Actual Date] in my MDX ( Actually, it got deleted
while I was removing all the unnecessary dimensions for simplicity).

I dont want to show the ModifiedTime in the output but the Answer above
is the "recent" ModifiedDate for that person.

Sorry for the confusion.

Milind

To take [ModifiedTime] dimension into account, try this:

Set [FinalSet] as
'Generate([Person].[Name].Members,
Extract(Tail(NonEmptyCrossJoin(
{[Person].CurrentMember},
[ModifiedTime].[Actual Date].Members,
[Answer].[Name].Members)),
[Person], [Answer]))'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&[DMStatus])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak

I tried the above solution but it simply hangs for a long time in the
MDX Sample app.
If I remove "Generate" in the above MDX, the output is one line

Person Answer Encounter
_________ __________ _________
All Person DisEnrolled 15

Any ideas? Thanks for responding.

thanks

Milind

Hi Milind,


I created a similar query for Foodmart Sales, where the hanging problem
occurs when the set of persons in Generate is large (about 10,000 total
for Foodmart). One way around this is to work on smaller subsets of
persons, using nested Generate(). In the case of Foodmart Sales, the
following query starts with cities (< 100 with data), then processes all
Customer names under each city in turn:

'Generate(NonEmptyCrossJoin(
[Customers].[City].Members),
Generate(NonEmptyCrossJoin(
[Customers].Children),
Extract(Tail(NonEmptyCrossJoin(
{[Customers].CurrentMember},
[Time].[Month].Members,
[Promotions].[Promotion Name].Members)),
[Customers], [Promotions])))'

Select {[Measures].[Unit Sales]} on columns,
[TestExtract] on rows
from Sales
where [Product].[All Products].[Food]

In your case, it sounds like you have a [Field] level above [Name] that
might work, depending on the number of Fields, and Names under each one,
in the [Person] dimension:

Set [FinalSet] as
'Generate(NonEmptyCrossJoin(
[Person].[Field].Members),
Generate(NonEmptyCrossJoin(
[Person].Children),
Extract(Tail(NonEmptyCrossJoin(
{[Person].CurrentMember},
[ModifiedTime].[Actual Date].Members,
[Answer].[Name].Members)),
[Person], [Answer])))'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&[DMStatus])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks Deepak! It worked.