Issue ordering MDX query result

Question

Dears:

I have the following MDX query.

    WITH
    MEMBER [Measures].[Member Full Path] AS
        [Revenuedim_Client].CurrentMember.UniqueName
    MEMBER [Measures].[Member Ordinal] AS
        [Revenuedim_Client].CurrentMember.Ordinal

    SELECT 
    NON EMPTY 
    Hierarchize(
    Union(
            Crossjoin( [Measures].[BeaconAmount_USD]  ,     
             LastPeriods(12,[Date.YQM].lastChild.lastChild.lastChild)  
            )
        ,
        Crossjoin({[Measures].[Member Ordinal], [Measures].[Member Full Path]},[Date.YQM].[2018])
    )   
    ,POST) ON COLUMNS,
        TOGGLEDRILLSTATE(
            TOGGLEDRILLSTATE(
                TOGGLEDRILLSTATE(
                    TOGGLEDRILLSTATE(
                      {[Revenuedim_Client].[All Clients] } , 
                         {[Revenuedim_Client].[All Clients] }
                    ) 
                , {[Revenuedim_Client].[Group Client - Altria Group Inc.] }
                ), {}
            ), {}
        ) ON ROWS from [Revenue_Client]

This give me an output as follows:

On rows: Client (with 4 levels to drill down Up to Project level) On columns: 12 months + column with full member path in cells: revenue

How or where is the query can I add an ORDER statement so that the rows are sorted based on the total revenue for the 12 months and from most revenue too little revenue, this for each 4 levels in the Revenuedim_client hierarchy?

order definition is here: docs.microsoft.com/en-us/sql/mdx/order-mdx ... did you try applying it? — whytheq
Indeed, it was as simple as this: ORDER( TOGGLEDRILLSTATE( TOGGLEDRILLSTATE( TOGGLEDRILLSTATE( TOGGLEDRILLSTATE( {[Revenuedim_Client].[All Clients]}, {${param_level1_m}} ) , {${param_level2_m}} ), {${param_level3_m}} ), {${param_level4_m}} ) ,[Measures]. ${MarketOrBeacon}, DESC) — user1186098

whytheq whytheq · Accepted Answer · 2017-08-14T16:00:11

I think to make things a little clearer move the 12mths set into the WITH clause - then add a member aggregating it - then use a tuple made of this new member along with revenue as the numeric argument of your ORDER function.

WITH 
  MEMBER [Measures].[Member Full Path] AS 
    [Revenuedim_Client].CurrentMember.UniqueName 
  MEMBER [Measures].[Member Ordinal] AS 
    [Revenuedim_Client].CurrentMember.Ordinal 
  SET [12mth] AS 
    LastPeriods
    (12
     ,[Date.YQM].LastChild.LastChild.LastChild
    ) 
  MEMBER [Date.YQM].[All].[12mth_Agg] AS 
    Aggregate([12mth]) 
SELECT 
  NON EMPTY 
    Hierarchize
    (
      Union
      (
        CrossJoin
        (
          [Measures].[BeaconAmount_USD]
         ,[12mth]
        )
       ,CrossJoin
        (
          {
            [Measures].[Member Ordinal]
           ,[Measures].[Member Full Path]
          }
         ,[Date.YQM].[2018]
        )
      )
     ,POST
    ) ON COLUMNS
 ,Order
  (
    ToggleDrillState
    (
      ToggleDrillState
      (
        ToggleDrillState
        (
          ToggleDrillState
          (
            {[Revenuedim_Client].[All Clients]}
           ,{[Revenuedim_Client].[All Clients]}
          )
         ,{[Revenuedim_Client].[Group Client - Altria Group Inc.]}
        )
       ,{}
      )
     ,{}
    )
   ,[Date.YQM].[All].[12mth_Agg]  //<<think I prefer to explicitly add what I'm ordering by
   ,DESC
  ) ON ROWS
FROM [Revenue_Client];

Issue ordering MDX query result

1 Answers