CODEWITHSUNDEEP
sqlcountssasmdx
supremo
supremo

Reputation: 151

MDX - Row count is 1 after ORDER

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])

Upvotes: 0

Views: 70

Answers (1)

whytheq
whytheq

Reputation: 35557

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.

Upvotes: 1

Related Questions