Order by in MDX

Hi all,

I'm stuck in MDX definition to retrieve values from a dimension in List-Designer Appstudio V12.

I have this custom MDX:

Distinct({FilterEQ({ {AddCalculatedMembers(Descendants([JahrMonat].[Alle Jahre].[1], [JahrMonat].[Level 3], Self))} }, 1, ([YYYY_MM].[Level 3].AllMembers))})

Works fine and retrieves all periods like:

201501

201502

...

202203

I'm sure it must be an easy task to get this list sorted descending so that the most actual period comes a first element.

Tried all i found in various MDX-sites like:

Order(..., [JahrMonat].CurrentMember,DESC)

But nothing works, still retrieving the oldest period first.

Does anybody now the trick how to get the MDX-list definition ordered in a descending way?

Thanks for any tips,

Ralf

Find more posts tagged with

Infor EPM Discussions

Comments

martin-reischl

Hi Ralf,

I see 2 issues hier.

First, probably foremost, you are just using DESC, which means that you order only within the existing. Use BDESC to "break hierarchies" and just sort the resulting list, no matter to which parents the elements belong.

Second, you are not saying which property you want to use for sorting. You should spceficy a property, like [JahrMonat].CurrentMember.Properties("Caption"), or [JahrMonat].CurrentMember.Properties("Member_Name")

Give this a try:

Order(..., [JahrMonat].CurrentMember.Properties("Member_Name"),BDESC)

Regards, Martin

Ralf Zerfass

Hi Martin,

I think your suggestion was one of my attempts already tried, but not sure, so tried again. Unfortunately the result is still not sorted as expected:

What's wrong with the statement?

Too bad, the StructureSet-Property of a simple list that is defined using the list designer does not show how to use the Order-Function correclty. neither I've found any helpful documentation.

If you had time for a remote session I'd appreciate, please let me know.

Regards,

Ralf

Ralf Zerfass

Hi,

got it!

Have to set the "hierarchized" property of the list to false, then it works, so I'm happy.

Thanks for your help!

However, in my opinion it should also work with a hierarchical list definition and the DESC, just in case I'd like show the years in the list result as well and not only the base months.

Ralf

martin-reischl

Yes, this makes sense. The "hierarchized" option takes the result of whatever you did, and forces it back into the original dimension order. This is by design, to avoid duplicates and ragged lists which look like hierarchies but don't belong together. You don't have to like it but there is a reason why they did it.

Regards,

Martin

unknown

Just out of curiosity, why are you using MDX to accomplish this? If the end result is a simple listing of Periods in a data bound list object (combobox, hyperblock, slice, ect), this can be accomplished using the list designer settings via filtering on level number and sorting with no need to break the hierarchy.

Ralf Zerfass

I wondered that this question didn't come earlier :-)

This is because the periods are released for different groups at different times. So Management has earlier access to the actual figures of a period than the rest. Kind of permission management.

However, I cannot use a DAC on the JahrMonat-Dimension, because of course one should see the budget values for the whole year. Using an MDAC seemed to be too complicated because this is used for multiple cubes.

Challenge is to retrieve only those periods the user is allowed to see without having a DAC on the dimension.

So I created an extra cube with the JahrMonat and an additional period-dimension (YYYY_MM) and this additional dimension has the DAC.

When inventing this long time ago in V11 I couldn't find another way. using the standard list designer to get exactly what I wanted, so I changed to MDX.

As the look and feel ot the selection boxes for list views changed in V12 (very big so the current element was no longer visible without scrolling) the users wanted to reverse the sort order to have the actual period always at the top of the list.

Maybe there is an easier way today, but I thought it shouldn't be a big deal to reverse sort in MDX.

Ralf

0712131256598360.doc

John Robson

Ralph

In V12 have you considered adding a numeric attribute called say SortOrder to your custom dimension? If you do then with standard v12 function you can sort by the attribute value - breaking hierarchies - when you want reverse order. The sort can be activated by an action which set a report variable value - see screen shot - where a report variable has a boolean value e.g. True. I set Jan 2014 to have a sort order of 12, Feb 2014 to 11 etc. hence the ASC

Ralf Zerfass

Hi John,

There is no need for an extra sort attribute as the caption itself can be used to sort (202001, 202002 etc.).

Ralf

Copyright © 2025 Infor. All rights reserved.