Creating Subset in TI and sorting via Attribute

[cdhodge2002]

I am creating an employee picklist for a cube that is department specific. To do this I create these picklist via a TI process whenever I receive a new employee file. This TI process references a attribute cube that contains the different department employee combinations.

However the client would like to see the picklist in alphabetical order and not employee number order. I believe using an MDX statement to create the subset for the employee dimension in the attribute cube is the way to do this and have read that other people have had success doing this. However my MDX is horrible and was wondering if someone could help me with this.

I know this is the MDX I need to order by subset by attribute ORDER({[Employee].Members}, [Employee].EMPLOYEENAME, BASC)

However I need to incorporate it into my current mdx statement which grabs all 0 level items
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( [employee], ALL, RECURSIVE )}, 0)}

How can I incorporate both of these items into an mdx statement?

[qml]

Code: Select all

{ORDER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( [Employee], ALL, RECURSIVE ) }, 0) }, [Employee].EMPLOYEENAME, BASC) }

[cdhodge2002]

I had tried that and this is the error I get.

Syntax error at or near: ' ALL, RECURSIVE ) }, 0) }, [employe ... ', character position 64 expression:

[qml]

Try this:

Code: Select all

{ORDER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee])}, 0)}, [Employee].[EmployeeName], BASC)}

[rmackenzie]

How can I incorporate both of these items into an mdx statement?

MDX allows you to nest different expressions inside one another as qml's code snippet shows. As long as you have a statement that works then you can pass that in to another function.