1

How to filter MDX output for first 'n' rows and then loop over the next 'n' rows and so on using following example.

SELECT  
{ [Measures1].members,  [Measures2].members } ON COLUMNS,  
{ [Date].members } ON ROWS  
FROM [cube]

I'm trying this because I would like to loop over every 'n' rows of the MDX output and export the result to excel.

The original query returns very large number of records. That is the reason I'm planning to loop over the records.

User1493
  • 481
  • 2
  • 7
  • 23

1 Answers1

1

You can do it like this: first, just take top N (lets say top 5 members), after that take top 10 members except top five. After that take top 15 members except top 10, and so on...

Here is the formula for the first 5, ordered by [SomeAmount], which is one of the measures from the cube

SELECT  
{ [Measures].[SomeAmount] } ON COLUMNS,  
EXCEPT(TopCount([Date].members, 5,[Measures].[SomeAmount] ),TopCount([Date].members, 0,[Measures].[SomeAmount] ))  ON ROWS  
FROM [cube]

and for the next five (first five are skipped):

SELECT  
{ [Measures].[SomeAmount] } ON COLUMNS,  
EXCEPT(TopCount([Date].members, 10,[Measures].[SomeAmount]  ),TopCount([Date].members, 5,[Measures].[SomeAmount]  ))  ON ROWS  
FROM [cube]

And so on...

If you have AdventureWorks cube you can try this query there:

SELECT  
{ [Measures].[Internet Order Count] } ON COLUMNS,  
EXCEPT(TopCount([Date].[Calendar].members, 10, [Measures].[Internet Order Count]),
TopCount([Date].[Calendar].members, 5,[Measures].[Internet Order Count] ))  ON ROWS  
FROM [Adventure Works]

And also, for the example from your comment where you have cross join:

SELECT 
{Department.members}  on COLUMNS,
EXCEPT(TopCount({[Product].[Status].members}*{Date.[Calendar].members}, 10), 
TopCount({[Product].[Status].members}*{Date.[Calendar].members}, 5)) on ROWS
FROM [Adventure Works]

You can try this on Adventure Works also.

And also for your example from the comment:

SELECT EXCEPT(TopCount({dim1.members}*{dim2.members},10), 
TopCount({dim1.members}*{dim2.members},5)) on ROWS, 
{dim3.members} on COLUMNS 
FROM [cube]

Hope it helps.

vldmrrdjcc
  • 2,082
  • 5
  • 22
  • 41
  • Can you please modify this example? `SELECT {dim1.members}*{dim2.members} on ROWS, {dim3.members} on COLUMNS FROM [cube]` I don't understand the word '[Measures]' in your example, what does it refer to? – User1493 Mar 11 '19 at 16:15