Search code examples
mdxpentaho

MDX Sort By Month

I am trying to put together my first query for a pentaho CDE dashboard chart.

Starting Query

WITH 
  SET [~COLUMNS] AS 
    {[DimProgram.Name].[Name].MEMBERS} 
  SET [~ROWS] AS 
    {[DimTime.CalendarYearMonth].[CalendarYearMonth].MEMBERS} 
SELECT 
  NON EMPTY 
    CrossJoin
    (
      [~COLUMNS]
     ,{[Measures].[SubmissionCount]}
    ) ON COLUMNS
 ,NON EMPTY 
    [~ROWS] ON ROWS
FROM [PSE_FactSubmission];

enter image description here

This query returns the data I want but needs to be tweaked a bit to be ready for actual use. I want to sort by date descending and limit to only the past 12 months.

I've read several webpages on sorting in MDX, but haven't been able to put together a query that will run. When the query doesn't run just an "Error" prompt.

Ordering Attempt

WITH 
  SET [~COLUMNS] AS 
    {[DimProgram.Name].[Name].MEMBERS} 
SELECT 
  NON EMPTY 
    CrossJoin
    (
      [~COLUMNS]
     ,{[Measures].[SubmissionCount]}
    ) ON COLUMNS
 ,NON EMPTY 
    Order
    (
      [DimTime.CalendarYearMonth].[CalendarYearMonth].MEMBERS
     ,[DimTime.CalendarYearMonth].CurrentMember.Member_Key
     ,DESC
    ) ON ROWS
FROM [PSE_FactSubmission];

Any tips on sorting or how to limit to the past X months would be very appreciated.

Solution

  • Usually a Date/Time dimension is ordered naturally in the cube design so there is no need to use Order. I don't need to with the cubes I use.

    If it is in a strange order in the cube then you need to break (B) this hierarchical order by using BASC or BDESC:

    WITH 
  SET [~COLUMNS] AS 
    {[DimProgram.Name].[Name].MEMBERS} 
  MEMBER [Measures].[orderMeas] AS 
    [DimTime.CalendarYearMonth].CurrentMember.Member_Key 
  SET [~ROWS] AS 
    Order
    (
      [DimTime.CalendarYearMonth].[CalendarYearMonth].MEMBERS
     ,[Measures].[orderMeas]
     ,BASC
    ) 
SELECT 
  NON EMPTY 
    CrossJoin
    (
      [~COLUMNS]
     ,{[Measures].[SubmissionCount]}
    ) ON COLUMNS
 ,NON EMPTY 
    [~ROWS] ON ROWS
FROM [PSE_FactSubmission];

    To get the most recent 12 months you can use the Tail function - better to use it against NonEmpty months:

    WITH 
  SET [~COLUMNS] AS 
    {[DimProgram.Name].[Name].MEMBERS} 
  MEMBER [Measures].[orderMeas] AS 
    [DimTime.CalendarYearMonth].CurrentMember.Member_Key 
  SET [~ROWS] AS 
    Order
    (
      NonEmpty
      (
        [DimTime.CalendarYearMonth].[CalendarYearMonth].MEMBERS
       ,[Measures].[SubmissionCount]
      )
     ,[Measures].[orderMeas]
     ,BASC
    ) 
SELECT 
  NON EMPTY 
    CrossJoin
    (
      [~COLUMNS]
     ,{[Measures].[SubmissionCount]}
    ) ON COLUMNS
 ,NON EMPTY 
    Tail
    (
      [~ROWS]
     ,12
    ) ON ROWS
FROM [PSE_FactSubmission];

    Hi Andrew - against AdvWrks I've got the following running without any errors. I needed to change Member_Key to MemberValue:

    WITH 
  SET [~COLUMNS] AS 
    [Product].[Product Categories].[Product]
  MEMBER [Measures].[orderMeas] AS 
    [Date].[Calendar].CurrentMember.MemberValue 
  SET [~ROWS] AS 
    Order
    (
      NonEmpty
      (
        [Date].[Calendar].[Month].MEMBERS
       ,[Measures].[Internet Sales Amount]
      )
     ,[Measures].[orderMeas]
     ,ASC
    ) 
SELECT 
  NON EMPTY 
    CrossJoin
    (
      [~COLUMNS]
     ,{[Measures].[Internet Sales Amount]}
    ) ON COLUMNS
 ,NON EMPTY 
    Tail
    (
      [~ROWS]
     ,12
    ) ON ROWS
FROM [Adventure Works];