MDX Dimension order

Post Reply
DYNA
Posts: 22
Joined: Mon Jul 29, 2019 10:09 am
OLAP Product: PLANNING ANALYTICS (TM1)
Version: 2.0.6
Excel Version: Excel for office 365
Location: SOUTH AFRICA

MDX Dimension order

Post by DYNA »

Good Day Everyone,

Apologies if I am asking a basic question. I may be trying to accomplish something that is not possible.

I have a dimension with multiple consolidated level,

Dimension Structure

-OTHER
-Project
-account
-Other account


The OTHER level is the top level followed by a project, account is the leaf level, and other account is the leaf level under the OTHER level.

I'm trying to display the other account at the top of the Project consolidated level using MDX expression.

Please note that there are multiple levels like project.

Thank you in advance
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX Dimension order

Post by MarenC »

Hi,

I would create a text attribute called something like sortorder, and then use this to order by. The Mdx would then be:

ORDER({TM1SUBSETALL( [dimension name] )}, VAL([dimension name].currentmember.properties("Sortorder")) )

The sort order values would be 1 through to n number of elements in dimension, so your first elements sort order would be 1 etc

Maren
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX Dimension order

Post by burnstripe »

{Union({[dimname].[other],{tm1filterbylevel({[dimname].[other].children},0)}}
, {tm1drilldownmember({[dimname].[other]}, all, recursive)}
)}

This could also do the trick without any attributes/indexes, hopefully there's no typos as I'm not at a pc currently to test.
Post Reply