TM1 Forum

Hello All,

I am trying to create an MDX expression for an active form to show all our sales regions and the sales locations (children) within each. I have the regions in a subset. Basically I want to show as if each [+] was clicked in the subset to expand each region. The sales locations are at varying heirarchy levels however, so doing TM1FILTERBYLEVEL will not work.

DRILLDOWNLEVEL seems like it should work but I have some regions that are not expanding. I think the issue is that these regions have multiple parents and somehow that is confusing things? If I create multiple parents for the other divisions then everything will expand but now the regions do not show up in the same order as in the original subset.

I was looking into using ORDER to sort the regions back the way they started. I'm not sure if I've set up the sort attribute correctly, I think it may just be an alias.

Here is the MDX expression I have: {ORDER( {DRILLDOWNLEVEL( {[Business Unit].[Regions Subset]} )}, [Business Unit].[Sort Attribute], DESC) }

Is there a better way to achieve the same goal of getting all locations to expand and all regions to appear in order?

Using TM1 10.2.2

Hi

I suggest that you get the dimension itself into the right order and then the MDX should exploit this.

In cube }DimensionProperties there are 4 settings related to sorting

SORTCOMPONENTSSENSE
SORTELEMENTSSENSE
SORTCOMPONENTSTYPE
SORTELEMENTSTYPE

Select the Dimension in }Dimensions and against that put ASCENDING into the two SENSE ones, put BYNAME into the COMPONENTSTYPE and BYHIERARCHY into the ELEMENTSTYPE.

Then, when you next update the dimension, it should sort in that order.

It is best if you make all the elements in your dimension join up to a single top level element, using 0 weights to avoid double counting.

For the selection issue in your dimension, the only suggestion I can make, instead of using a static subset, is to use attributes and record yourself doing a Select By Attribute to get the initial set of elements. After that the DRILLDOWN should work.

Regards

Paul Simon