mdxiccubeiccube-reportingmdx-query

MDX sort order in Crossjoin

I want to show a list of callers we have missed calls from within a daterange. I want te result to be ordered by date. But I can't figure out how to do this.

My MDX statement:

   With   
   Member [Measures].[Gemist] AS
        sum(
            except({[CM resultaat].[Resultaat].[CM resultaat].allmembers},
                {[CM resultaat].[Resultaat].[CM resultaat].[answer],[CM resultaat].[Resultaat].[CM resultaat].[answer overflow]}),
            [Measures].[SN Gesprekken]
        )

Select
   order([Measures].[Gemist],[Datum].[Datum].currentMember.value, ASC) on 0,
    nonempty(crossjoin(Hierarchize([ServiceNummer ANI].[Ani]),[Datum].[Datum].[Dag]),[Measures].[Gemist]) on 1
FROM (SELECT {[datum].[datum].[dag].[2020-04-01]:[datum].[datum].[dag].[2020-04-28]} ON 0 FROM [Cube])

After some google searches I tried this to order the measure by date but also tried to order the crossjoin. Output stays te same, no order on date:

Missed calls should be sorted on date

Anyone has a solution for this?

Solution

  • You need to order the vertical axis (i.e., the axis 1):

    order(
  nonempty(
    crossjoin( [ServiceNummer ANI].[Ani], [Datum].[Datum].[Dag] )
  ),
  [Datum].[Datum].currentMember.key,
  BASC

) on 1

    using the key (or name) of the current member of the [Datum] dimension.

    Hope that helps.