MDX subset sort by Alias

[lemon103]

Hi All,

I am trying to create a MDX subset sorted by an Alias.
It seems the ORDER MDX function works for every attribute EXCEPT the aliases.
i.e.
ORDER({[Employee].Members}, [Employee].ZipCode , BASC)
works but
ORDER({[Employee].Members}, [Employee].FullName, BASC)
where FullName is an alias does not.

Is there a special way to reference Alias attributes in the MDX statements?
(I can create a dummy sorting attribute = to the alias as a workaround but I rather avoid it if possible)

Thanks for any advice!
Yan

[lotsaram]

How are you defining the subsets? When using the subset editor you can select which alias to use.

When using TurboIntegrator just use the SubsetAliasSet function prior to SubsetCreatebyMDX
If the subset alias is set to "Fullname" then the TM1 MDX syntax should work using TM1SORT
{TM1SORT( {TM1SUBSETALL( [Employee] )}, ASC)}

[lemon103]

I am defining the subset dynamically by TI Process.
SubsetAliasSet cannot run before SubsetCreateByMDX because the Subset would not exist yet.
And running SubsetAliasSet after SubsetCreateByMDX does not have an impact.

[lotsaram]

Hmmm I would have thought that applying the alias after creating the subset would have done the trick but having done the test you are definitely correct. It looks like the alias is applied only in post the MDX script running when the subset is viewed in the subset editor UI. (If the user subsequently clicks "update" in the expression window then the correct sort order will be applied but that's hardly an acceptable workaround.)

I think the easiest (and possibly only) workaround is as you have already alluded, to essentially have a duplicate text attribute for the alias and sort by the duplicate.

[buncikp]

Although very outdated thread - I was recently solving the same issue. It turned out that problem is not ORDER function at all, but rather problem in alias (attribute) values. Check your }ElementAttributes cube for the dimension and refer to the alias you want to use for sorting. The alias values are probably empty.
Keep in mind that if you use attribute editor, assigning value to alias that is identical with principal name will lead to empty value technically stored in }ElementAttributes cube. The solution is to avoid attribute editor and populate alias values technically via AttrPutS.

[tsw]

Although very outdated thread - I was recently solving the same issue. It turned out that problem is not ORDER function at all, but rather problem in alias (attribute) values. Check your }ElementAttributes cube for the dimension and refer to the alias you want to use for sorting. The alias values are probably empty.
Keep in mind that if you use attribute editor, assigning value to alias that is identical with principal name will lead to empty value technically stored in }ElementAttributes cube. The solution is to avoid attribute editor and populate alias values technically via AttrPutS.

I want to thank you, buncikp, for stopping me from tearing my hair out trying to figure out what was wrong with my MDX expression.
Once I populated the ElementAttributes cube via the TI process's AttrPutS. The MDX expression worked correctly.
Apparently, Perf Modeler and attribute editor defaults to the element/invariant name if this attribute is blank... so I couldn't visually figure out why the sort wasn't working correctly in the MDX.

[Tilo]

I sorted a Dimension by an alias.

a) {TM1SORT( {TM1SUBSETALL( [DimName].[Alias] )}, ASC)}
b) {ORDER( {TM1SUBSETALL( [DimName] )}, [DimName].[Alias], ASC)}

Result:
Both mdx-Statements sort the dimension.
Both mdx-Statements sort differently.

Does somebody know the differences between TM1SORT and ORDER that cause the different sort behaviour?

[lotsaram]

I'm pretty sure that the 1st MDX syntax you posted won't work period as the only thing you pass to TM1SUBSETALL is the dimension name so {TM1SUBSETALL( [DimName].[Alias] )} is pretty unlikely to work. The TM1SORT function will do an ascending or descending alpha-sort depending on what alias is currently set for the subset so the same expression could return different sort orders depending on the selected alias. If you are using TI then you can use the SubsetAliasSet function.

For the standard Miscrosoft MDX ORDER function I think you would want BASC as ASC will do a within hierarchy sort. You probably need an expression more like ... {ORDER( {TM1SUBSETALL( [DimName] )}, [DimName].CurrentMember.Properties('Alias'), BASC)}

You also need to consider that Analysis Services (which MDX was developed for) enforces levelled hierarchies and doesn't have a concept of subsets like TM1 does. "Subsets" as such only really exist in MDX as the row or column member set of a query and the ORDER function is designed to sort the members based on data in a column returned in the query cellset. Sorting a list of members simply in a list based on an element property (as in a TM1 subset) without the context of a view/query is "foreign" to MDX which is probably why the TM1 specific MDX functions exist. From my experience if you want ORDER to produce the same sorting as TM1SORT then is only works if all the elements are from the same dimension level. But I'm not an MDX expert, someone out there probably has a better idea how it all works.

[rmackenzie]

I sorted a Dimension by an alias.

a) {TM1SORT( {TM1SUBSETALL( [DimName].[Alias] )}, ASC)}
b) {ORDER( {TM1SUBSETALL( [DimName] )}, [DimName].[Alias], ASC)}

Result:
Both mdx-Statements sort the dimension.
Both mdx-Statements sort differently.

Does somebody know the differences between TM1SORT and ORDER that cause the different sort behaviour?

Per the syntactical error that Lotsa mentiones, then you're probably comparing the alpha sort of the element principals based on the principal names themselves vs the alpha sort of the element principals per their alias values. E.g. if a, b, c have aliases of z, y, x then the first expression (written properly) will give a, b, c but the second will give c, b, a - by design. If you turn aliases on in the second expression it will give x, y, z.

Can you post a definitive example of the error, rather than an a vague example?

[Tilo]

Thanks to both. That sounds very reasonable.