MDX Order By not applying to last member in dimension

[JohnO]

I want to order the N members in my Month dimension by sorting on the 'Seq No' attribute which is a 'N' attribute.

Code: Select all

{
ORDER(
    { TM1FILTERBYLEVEL(
        {TM1SUBSETALL( [Month] )}
        ,0)}
, [}ElementAttributes_Month].[}ElementAttributes_Month].CURRENTMEMBER.PROPERTIES("Seq No"), ASC)

It works fine EXCEPT it does not include the last N member in the dimension regardless of the value of the attribute for that element.

We are using 2.0.9.14, I have a feeling this is something I have come across before.

Comments/ alternatives?

[howard40116]

You can try this MDX query.

Code: Select all

{ORDER(
    {TM1FILTERBYLEVEL(
		{TM1SUBSETALL( [Month] )}
	,0)}
, [Month].[Seq No], ASC)}

[burnstripe]

Alternative option

Code: Select all

{ ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)}, [}ElementAttributes_Month].( [}ElementAttributes_Month].[Seq No] ), BASC)}

[JohnO]

Alternative option

Code: Select all

{ ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)}, [}ElementAttributes_Month].( [}ElementAttributes_Month].[Seq No] ), BASC)}

This one worked! Why?

[gtonkin]

Still a bit confused with your original MDX working as you were basing a lookup to }ElementAttributes_Month using members from Month.

Anyway, a few things to consider:
When using ORDER, ASC and BASC are for different cases where BASC breaks down any hierarchies before sorting, ASC sorts within hierarchies.
Attributes are always seen as strings i.e. sorting will be alphabetical 1, 10, 11..28, 29, 3, 30, 31..
Ideally you want to convert the strings to numerics to sort numerically but there is a gotcha where an attribute is blank, you need to trap this and assign a value.

As yet another alternative, you could try this:

Code: Select all

ORDER(
	  {TM1FILTERBYLEVEL({TM1SUBSETALL( [Month].[Month] )},0)},
	  VAL(
		  IIF([Month].[Month].CurrentMember.Properties("Seq No")="",
			"9999" /* Default Value */,
			[Month].[Month].CurrentMember.Properties("Seq No")
		    )
	      )
	), BASC)

[JohnO]

Anyway, a few things to consider:
When using ORDER, ASC and BASC are for different cases where BASC breaks down any hierarchies before sorting, ASC sorts within hierarchies.

I was thinking that because the MDX was returning an N level set that the rollup would not be referred to but I am wrong. BASC sorts it.

Thanks