How to get the last, non empty 10 members with MDX?

2
votes

I have an MDX query:

SELECT
NON EMPTY {Hierarchize({[Measures].[Rating]})} ON COLUMNS,
NON EMPTY {Hierarchize({[Response].[Response Name].Members})} ON ROWS
FROM [Ratings]

That returns a table of Response Names to Response count. This query returns a row for ALL responses, though, and I just want the most recent 10 responses.

I tried to do this using HEAD like so:

SELECT
NON EMPTY {Hierarchize({[Measures].[Rating]})} ON COLUMNS,
HEAD(NON EMPTY {Hierarchize({[Response].[Response Name].Members})}, 10) ON ROWS
FROM [Ratings]

but it gives an error "Syntax error at line 3, column 18, token 'NON'"

If I remove the NON EMPTY, then it works as I would expect (returns only 10 members), but then it includes the empty Ratings.

How can I use NON EMPTY and HEAD together? (Or accomplish the same thing another way)

2
mdx
Head will return the 'first n' elements in a set, which may or may not coincide with 'most recent'. You can use the Order function in addition to Head to return what you need reliably. – Ezequiel Muns

2 Answers

5
votes

I believe it is already a bit late for the answer, but here's the solution I used (I was having the same problem):

select {[Measures].[Percentage Present]} ON COLUMNS, 
Head(Filter([Student].[Student].AllMembers, not isEmpty([Measures].[Percentage Present])),10) ON ROWS 
from [Attendance]

Head + Filter(, not isEmpty([measure])) did the trick!

0
votes

Not sure to understand what you mean by 'last' but here is a statement using the NonEmpty function instead of the NON EMPTY keywords:

SELECT

NON EMPTY [Measures].[Rating] ON COLUMNS,

HEAD( NonEmpty( 
         [Response].[Response Name].Members, 
         [Measures].[Rating] 
      ), 
      10
) ON ROWS

FROM [Ratings]