MDX Ordering By Dimension

user_1532551 0
Viewed 4694

Over in the SQL side, my data is looking like this: 

Select f.id, f.TimeKey,t.CalendarYearMonth 
from FactSubmission f
inner join DimTime t on t.TimeKey = f.TimeKey
order by f.Id asc

enter image description here

Sorting from MDX we have descending

SELECT
NON EMPTY ORDER(
 [DimTime.CalendarYearMonth].[CalendarYearMonth].Members,
 [DimTime.CalendarYearMonth].CurrentMember.Properties("MEMBER_KEY"), 
 DESC
 )  ON COLUMNS
FROM [PSE_FactSubmission]

enter image description here

And Ascending

enter image description here

The January dates aren't at the top of either sort, which suggests I'm sorting by the FactSubmission.ID key instead of DimTime.CalendarYearMonth

Is this how things are supposed to work? I'd like to pull back Jan,Feb,March.

DimTime.CalendarYearMonthNum is a column with data in the form 201501,201502,201503 etc. Here's an attempt at using this column to to sort the CalendarYearMonth data.

enter image description here

Debugging Query to Select Keys enter image description here

NonEmpty Query enter image description here

pentaho mdx sorting
2 Answers
Related