MDX to sort Elements based on Substring

[GPC]

Hi All,

I'm new to MDX but I want to sort the elements of a dimension based on a Substring of the element name. So I need something like this;

{
ORDER(
{ TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Schedule] )}
,0)}
, SUBSTRING([Schedule],6,1), BDESC)
}

Of course the the syntax is wrong and I don't know if SUBSTRING or SUBST can be used in MDX. Can anyone point me in the right direction?

thanks,

Gregory

[standtrue]

Substring doesn't work in subsets as far as I know, but here is one solution:

On your Schedule dimension create a text attribute called "SubString" (or whatever).

In your }ElementsAttributes_Schedule cube write a rule along the lines of:

['SubString'] = S: SUBST(!Schedule, 6, 1);

Now in your subset you can say:

{
order(
tm1filterbylevel([schedule].MEMBERS,0)
,[schedule].SubString
, bdesc
)
}

[GPC]

Thankyou standtrue - that works well.