none
MDX Question. How to Order by the second attribute RRS feed

  • Question

  • Hello,

    We have some MDX query that we are using in SSRS Report and I am having hardtime trying to figure out how to sort based on the second column.

    Below is where I am having the issue:

    set  [OrderedSet] AS 
    ORDER([Property].[Report Property Heading].[Report Property Heading].MEMBERS
    ,[Measures].[Open Date], DESC)

    In this Set function, We are ordering based on Open Date DESC but there are cases where some of the open dates are same and in that particular case, I want it to be sorted by Report Property Heading Value.

    Right now it is sorting by DESC (By Default I guess) and I want it to be sorted base on ASC and I couldn't get how to.

    Any help is appreciated.

    FULL Query: 

    WITH


    MEMBER [Measures].[Open Date] AS
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading", [Property].[Report Concepts].currentmember.FIRSTCHILD.NAME,NULL)


    set  [OrderedSet] AS 
    ORDER([Property].[Report Property Heading].[Report Property Heading].MEMBERS
    ,[Measures].[Open Date], DESC)


    MEMBER [Measures].[OpenDateOrder] AS
    RANK([Property].[Report Property Heading].CURRENTMEMBER,
    [OrderedSet])


    MEMBER [Measures].[Concept RowNumber] AS
    Rank(([Property].[Report Concept Heading].CurrentMember,
    [Property].[Report Property Heading].CurrentMember),


    NonEmpty
    (([Property].[Report Concept Heading].CurrentMember,
    [Property].[Report Property Heading].[Report Property Heading]),
    {[Measures].[OpenDateOrder]}),


    [Measures].[OpenDateOrder]
    )


    MEMBER [Measures].[PageNumber New] AS
    case when [Measures].[Concept RowNumber] <=22 then  1 
    when ([Measures].[Concept RowNumber] >22 and [Measures].[Concept RowNumber]<=44) then  2
    when ([Measures].[Concept RowNumber] >44 and [Measures].[Concept RowNumber]<=66) then  3
    when ([Measures].[Concept RowNumber] >66 and [Measures].[Concept RowNumber]<=88) then  4
    when ([Measures].[Concept RowNumber] >88 and [Measures].[Concept RowNumber]<=110) then  5 else  6 end


    MEMBER [Measures].[Currency Net Sales] AS
    //IIF(@IsLocalCurrency = "True", ([Measures].[Net Sales],[Revenue Type].[Revenue Types].[Revenue Category].&[RESTAURANT]&[REVENUE]), ([Measures].[Net Sales USD],[Revenue Type].[Revenue Types].[Revenue Category].&[RESTAURANT]&[REVENUE])) //, [Property].[Report Property Heading].&[DailySalesReport]&[Rainforest Cafe]&[SCOOPS KEM]) )
    ([Measures].[Net Sales USD],[Revenue Type].[Revenue Types].[Revenue Category].&[RESTAURANT]&[REVENUE])


    MEMBER  [Measures].[Month #] AS
    ([Date].[Month Of Year].CurrentMember.MEMBERVALUE)


    MEMBER  [Measures].[Month # for Monthly Total] AS
    //MONTH(TAIL( STRTOSET(IIF (IsSubReport = "True", @DateCalendarCalculated,@DateCalendar)), 1).Item(0).MEMBERVALUE)
    (MONTH(TAIL( STRTOSET(@DateCalendarRange), 1).Item(0).MEMBERVALUE))
    //(MONTH(TAIL((  STRTOSET(@DateCalendar) ), 1).Item(0).MEMBERVALUE))




    MEMBER  [Measures].[Net Sales for Monthly Total] AS
    IIF( [Measures].[Month # for Monthly Total] = [Measures].[Month #], 
    [Measures].[Currency Net Sales], NULL)
    //[Measures].[Currency Net Sales]


    MEMBER [Measures].[Property No] AS
    [Property].[Report Property Heading].CurrentMember.MEMBERVALUE


    MEMBER [Measures].[Report Property Heading Value] AS
    [Property].[Report Property Heading].CurrentMember.MEMBERVALUE


    MEMBER [Measures].[Report Concept Heading Value] AS
    [Property].[Report Concept Heading].CurrentMember.MEMBERVALUE


    MEMBER [Measures].[Report Concept Sequence Value] AS
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Concept Heading",
    [Property].[Report Concepts].CURRENTMEMBER.Properties( "Report Concept Sequence Number" ),
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading",
    [Property].[Report Concepts].CURRENTMEMBER.PARENT.Properties( "Report Concept Sequence Number" ),NULL))


    MEMBER [Measures].[Report Property Sequence Value] AS
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading",
    [Property].[Report Concepts].CURRENTMEMBER.Properties( "Report Property Sequence Number" ),NULL)


    MEMBER [Measures].[Report Property Page Number] AS
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading",
    [Property].[Report Concepts].CURRENTMEMBER.Properties( "Report Property Page Number" ),NULL)


    MEMBER [Measures].[Specific Concepts Net Sales] AS
    IIF(([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading"
    AND ([Property].[Report Concepts].CURRENTMEMBER.Parent IS [Property].[Report Concepts].[Report Concept Heading].&[DailySalesReport]&[Fertitta Hospitality]
    OR [Property].[Report Concepts].CURRENTMEMBER.Parent IS [Property].[Report Concepts].[Report Concept Heading].&[DailySalesReport]&[Golden Nugget LC]) )
    ,([Property].[Report Concepts].CURRENTMEMBER.Parent,[Measures].[Currency Net Sales]),
    SUM(DESCENDANTS((IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Concept Heading",
    [Property].[Report Concepts].CURRENTMEMBER,
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading",
    [Property].[Report Concepts].CURRENTMEMBER.Parent,NULL))),[Property].[Report Concepts].[Report Open Date])
    *[Property].[Is Included Total Net Sales].&[True]
    ,[Measures].[Currency Net Sales]))


    MEMBER [Measures].[Specific Concepts Net Sales for Monthly Total] AS
    IIF([Measures].[Month # for Monthly Total] = [Measures].[Month #],
    IIF(([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading"
    AND ([Property].[Report Concepts].CURRENTMEMBER.Parent IS [Property].[Report Concepts].[Report Concept Heading].&[DailySalesReport]&[Fertitta Hospitality]
    OR [Property].[Report Concepts].CURRENTMEMBER.Parent IS [Property].[Report Concepts].[Report Concept Heading].&[DailySalesReport]&[Golden Nugget LC]) )
    ,([Property].[Report Concepts].CURRENTMEMBER.Parent,[Measures].[Currency Net Sales]),
    SUM(DESCENDANTS((IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Concept Heading",
    [Property].[Report Concepts].CURRENTMEMBER,
    IIF([Property].[Report Concepts].CURRENTMEMBER.level.name = "Report Property Heading",
    [Property].[Report Concepts].CURRENTMEMBER.Parent,NULL)))
    ,[Property].[Report Concepts].[Report Open Date])
    *[Property].[Is Included Total Net Sales].&[True]
    ,[Measures].[Currency Net Sales]))
    , NULL)


    MEMBER [Measures].[All Concepts Net Sales] AS
    SUM((DESCENDANTS([Property].[Report Concepts].[Report Type].&[DailySalesReport],[Property].[Report Concepts].[Report Open Date])
    *[Property].[Is Included Total Net Sales].&[True]),[Measures].[Currency Net Sales])


    MEMBER [Measures].[All Concepts Net Sales for Monthly Total] AS
    IIF([Measures].[Month # for Monthly Total] =[Measures].[Month #], 
    SUM((DESCENDANTS([Property].[Report Concepts].[Report Type].&[DailySalesReport],[Property].[Report Concepts].[Report Open Date])
    *[Property].[Is Included Total Net Sales].&[True]),[Measures].[Currency Net Sales])
    , NULL)


    SELECT
    NON EMPTY 
    {
    [Measures].[Currency Net Sales],
    [Measures].[Net Sales for Monthly Total],
    [Measures].[All Concepts Net Sales],//med
    [Measures].[All Concepts Net Sales for Monthly Total],//med
    [Measures].[Specific Concepts Net Sales],//big
    [Measures].[Specific Concepts Net Sales for Monthly Total],//big
    [Measures].[Property No],
    [Measures].[Open Date],
    [Measures].[Report Concept Heading Value],//small
    [Measures].[Report Concept Sequence Value],
    [Measures].[Report Property Heading Value],
    [Measures].[Report Property Sequence Value],//small
    [Measures].[Report Property Page Number],
    [Measures].[Month # for Monthly Total],
    [Measures].[Month #]
       ,[Measures].[OpenDateOrder]
       ,[Measures].[Concept RowNumber]
       ,[Measures].[PageNumber New]
    }
    ON COLUMNS,


    NON EMPTY 

    [Date].[Calendars].[Month].MEMBERS 

    [Date].[Week of Year].Children 
    *
    [Date].[Week Part].Children 
    *
    [Date].[Date].Children
    *
    [Date].[Day Of Week Short Name].Children 
    *
    EXCEPT(
    EXCEPT(
    EXCEPT(
    Exists([Property].[Report Concepts].MEMBERS, , "Daily Store Sales")
    ,[Property].[Report Concepts].[Report Open Date])
    ,[Property].[Report Concepts].[Report Type])
    ,[Property].[Report Concepts].[All])
    }
    ON ROWS


    FROM
    (SELECT {[Property].[Report Concepts].[Report Type].&[DailySalesReport]} ON COLUMNS FROM
    (SELECT {STRTOSET(@PropertyConcept)} ON COLUMNS FROM
    (SELECT {STRTOSET( @DateCalendarRange)} ON COLUMNS FROM
    [Enterprise_Cube])))


    • Edited by reddy421 Friday, February 14, 2020 10:15 PM
    Friday, February 14, 2020 10:14 PM

All replies

  • Since you are using SSRS the easiest thing to do is to set the order there, if you go into the table properties you can set the sorting based on multiple columns there. If you wanted to do it in MDX you would have to manually construct a concatenated measure some how to build a sort key as the ORDER() function only accepts a single sorting expression.

    http://darren.gosbell.com - please mark correct answers

    Sunday, February 16, 2020 4:32 AM
    Moderator
  • But the reason I am doing in MDX is to calculate the number and display as shown in the pic.

    So I want the Atlanta to have 2 , Baltimore to have 3 and so on.

    Monday, February 17, 2020 5:05 PM
  • So if you want to do ranking in MDX over multiple attributes you need to construct a ranking measure of some sort that combines the two attributes/measures together. For example you could do something like the following formatting the date as a string and concatenating it to the report heading name. 

    eg

    WITH MEMBER MEASURES.SortKey as 

    FORMAT([Measures].[Open Date], "yyyyMMdd") + "|" +  [Property].[Report Property Heading].CurrentMember.Name

    http://darren.gosbell.com - please mark correct answers

    Monday, February 17, 2020 7:44 PM
    Moderator