MDX - Row count is 1 after ORDER

Question

I have a MDX Query looking like this:

WITH
SET selection as ([Dates].[Year].&[2014],[Dates].[Month].&[1])        
set testset as (selection, [ThroughputID].[ID].ALLMEMBERS)

MEMBER [Measures].[RowCount] AS COUNT (testset)

SELECT
selection ON 0,
[Measures].RowCount
ON 1
FROM (SELECT [Dates].[Y-H-Q-M].MEMBERS ON 0 FROM [Throughput])

RowCount gives 1182918

If I add ORDER to "testset" like below, RowCount gives 1, how come?

WITH
SET selection as ([Dates].[Year].&[2014],[Dates].[Month].&[1])        
set testset as ORDER(selection, [ThroughputID].[ID].ALLMEMBERS)

MEMBER [Measures].[RowCount] AS COUNT (testset)

SELECT
selection ON 0,
[Measures].RowCount
ON 1
FROM (SELECT [Dates].[Y-H-Q-M].MEMBERS ON 0 FROM [Throughput])

surprised it actually runs - this is a very strange second argument for the ORDER function `[ThroughputID].[ID].ALLMEMBERS` — whytheq, Aug 28 '15 at 10:09
I'm trying to create a calculation of Quartiles, and if I understand everything correct the set needs to be sorted in order to do that. — supremo, Aug 28 '15 at 18:47
The reason for my question is found in my mew question here: http://stackoverflow.com/questions/32304534/trying-to-calculate-quartiles-in-mdx — supremo, Aug 31 '15 at 09:12

score 1 · Accepted Answer · answered Aug 28 '15 at 10:19

Please try this and then you can inspect the data to understand the change:

WITH 
  SET selection AS 
    (
      [Dates].[Year].&[2014]
     ,[Dates].[Month].&[1]
    ) 
  SET testset AS 
    Order
    (
      selection
     ,[ThroughputID].[ID].ALLMEMBERS
    ) 
SELECT 
  {} ON 0
 ,testset ON 1
FROM 
(
  SELECT 
    [Dates].[Y-H-Q-M].MEMBERS ON 0
  FROM [Throughput]
);

I suspect this section of your script is failing and resolving to a single member:

  Order
    (
      selection
     ,[ThroughputID].[ID].ALLMEMBERS
    )

The second argument of Order is usually numeric - you've used a set.

MDX - Row count is 1 after ORDER

1 Answers1