Page 1 of 1
MDX Order By not applying to last member in dimension
Posted: Thu Nov 24, 2022 10:39 pm
by 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?
Re: MDX Order By not applying to last member in dimension
Posted: Fri Nov 25, 2022 2:05 am
by howard40116
You can try this MDX query.
Code: Select all
{ORDER(
{TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Month] )}
,0)}
, [Month].[Seq No], ASC)}
Re: MDX Order By not applying to last member in dimension
Posted: Fri Nov 25, 2022 2:22 am
by burnstripe
Alternative option
Code: Select all
{ ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)}, [}ElementAttributes_Month].( [}ElementAttributes_Month].[Seq No] ), BASC)}
Re: MDX Order By not applying to last member in dimension
Posted: Fri Nov 25, 2022 4:55 am
by JohnO
burnstripe wrote: ↑Fri Nov 25, 2022 2:22 am
Alternative option
Code: Select all
{ ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)}, [}ElementAttributes_Month].( [}ElementAttributes_Month].[Seq No] ), BASC)}
This one worked! Why?
Re: MDX Order By not applying to last member in dimension
Posted: Fri Nov 25, 2022 6:01 am
by 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)
Re: MDX Order By not applying to last member in dimension
Posted: Tue Nov 29, 2022 10:01 pm
by JohnO
gtonkin wrote: ↑Fri Nov 25, 2022 6:01 am
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