I have a VENDOR dimension that is very flat - just a list of elements and a subtotal.
I want it to ALPHA sort ascending under the total BUT I need one specific element to appear 1st (and of course, it's not first alphabetically).
EXAMPLE:
Parent: TOTAL - VENDORS
Child 1: A
Child 2: B
Child 3: C
Child 4: Make me 1st
Child 5: Z
I want the subset to appear as follows:
Parent: TOTAL -VENDORS
Child 4: Make me 1st
Child 1: A
Child 2: B
Child 3: C
Child 5: Z
As new elements are added dynamically through a TI Process to a data source, they would continue to alpha sort under CHILD 1.
How can I do this?
MDX for Subset Ordering by 2 variables
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX for Subset Ordering by 2 variables
That's going to require a three part MDX query joined together by a + sign
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [VENDOR] )},"TOTAL - VENDORS")}+
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [VENDOR] )},"Child 4")}+
{EXCEPT({TM1SORT({TM1FILTERBYLEVEL( {TM1SUBSETALL([VENDOR])},0)},ASC)},{[VENDOR].[Child 4]})}
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [VENDOR] )},"TOTAL - VENDORS")}+
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [VENDOR] )},"Child 4")}+
{EXCEPT({TM1SORT({TM1FILTERBYLEVEL( {TM1SUBSETALL([VENDOR])},0)},ASC)},{[VENDOR].[Child 4]})}