Sort Order

[pmakulski]

I have a Person Dimension. The Element is EmpID.
I have a text attribute EmpName, and an Alias PersonName = EmpName | ' (' | EmpID | ')'
I have to do this because EmpName is not unique (too many John Smith's and Gobinder Kumar's)

There is a single Consolidation "All People"

I want to have the dimension, or at least a subset, sorted by PersonName

In the dimension editor, Sort Ascending puts it in order by EmpID.
In the subset editor, Sort Ascending puts it in the right order (Aachati comes first, followed by Abad, Abalos, etc) BUT "All People" falls between Allison and Ally.

How can I get my Consolidation to appear at the top, followed by the children in alpha order

[paulsimon]

Hi

Hi

You can do this with an MDX expression

Open the Subset Editor

On the View Menu tick Expression Window

In the expression window enter

{UNION(
{ [Person].[All People] }
,
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Person] )}, 0)}, ASC)}
)}

Ensure that your PersonName Alias is selected and is on.

Click the Update button and you should get the order that you want

Save the Subset ensuring that Save Expression is ticked.

The above is an MDX expression that Unions a set containing just the top level element 'All People' to the set of call base level elements, sorted in Ascending order, and so long as the Alias is on, then it will sort in Alias order, rather than by Element id.

Regards

Paul Simon

I have a Person Dimension. The Element is EmpID.
I have a text attribute EmpName, and an Alias PersonName = EmpName | ' (' | EmpID | ')'
I have to do this because EmpName is not unique (too many John Smith's and Gobinder Kumar's)

There is a single Consolidation "All People"

I want to have the dimension, or at least a subset, sorted by PersonName

In the dimension editor, Sort Ascending puts it in order by EmpID.
In the subset editor, Sort Ascending puts it in the right order (Aachati comes first, followed by Abad, Abalos, etc) BUT "All People" falls between Allison and Ally.

How can I get my Consolidation to appear at the top, followed by the children in alpha order

[pmakulski]

Amazing!
After looking at your note for a minute and saying MDX? wtf?

But after following your instruction step-by-step, it worked perfectly.
So, MDX, eh? Something new to learn.

Thanks for your help.