RESOLVED - Ordering Members within a descendant function

[iBoy]

Hi everyone, hope we're all well?

I'd love some help, if anyone has a spot of time available for me!

Currently when I use descendants I receive the members in the following format:

Level 1
- Level 2 (i)
- Level 2 (ii)
-- Level 3 (i)
-- Level 3 (ii)
- Level 2 (iii)

However I want the consolodation members (Level 1, Level 2 (ii)) to appear underneath their children. 

This is a tricky ask - So I'm open to a solution that inverts the members on the report like below, if possible:

- Level 2 (iii)
-- Level 3 (ii)
-- Level 3 (i)
- Level 2 (ii)
- Level 2 (i)
Level 1

Anyone know a function or expression that can allow this to occur?

[RKMI]

Hi,

I hope this helps,

How to reverse crosstab rows order
By default, crosstab members order is set at Framework Manager or within the cube definition. So totals are always shown within a crosstab at the top position.

If you need to reverse crosstab members order follow the next steps:

Current hierarchy structure  vs  Desired hierarchy structure
Level 4                                          Level 6
  Level 5                                      Level 5
  Level 5                                        Level 6
    Level 6                                      Level 6
    Level 6                                    Level 5
  Level 5                                      Level 5
    Level 6                                  Level 4

Steps:

1 - Create a dataitem and name it "Descendants" with the descendans of a member like
descendants(Member...,5,beforewithmember)
beforewithmember allows the crosstab to show the root nodes.

2 - Create a dataitem and name it 'Ordinal'. Put this expression
Ordinal(Level(currentMember(Hierarchy...)))
3 - Set the order using a dataitem, name it 'Order'. Set properties "Aggregate function" and "Rollup Aggregate function" to 'Count'.
3.1 - If you are using a dimensional model use the following expression
bottomCount([Descendants],50, [Ordinal] )

3.2 - If you are using a relational model use the following expression.
running-count()

4 - Change sorting options of the crosstab member. Drag and drop 'Order' dataitem and set sorting option as descending

Thanks,
RK

[iBoy]

Hey RK, thanks for feedback!

I have come across this, and it's such a shame because it looked so possible, but the results do not come through.  I'm trying to understand the logic applied, but the rows do not appear completely ordered.  I had level one rows sitting between level 2 rows and etc.

Any other suggestions I'll heartily look into, so thanks for that RK!

iBoy

[RKMI]

I tried the same it working at my end. If you did a box type none on order you need to bring into properties for the crosstab and also specify the sorting.

When you follow the steps can you tell me what you see. Try it one step at a time create the Descendant Item view the result in crosstab, then do the same for the next data item. Just to capature each step so that it will help with your debug.

Thanks,
RK

[iBoy]

Hi RKMI,

Thanks for the feedback, I did have another go, steadily attempting each part of the process, the same results showed however.

Attached shows the left hand column showing how the rows come out if I apply descendants.

The right hand column shows what happens if I apply the data items you sent through to me yesterday.

On the crosstab row, I made the properties of the data item with the descendant members link to the Ordinal and Order data items.  I then sorted the data item in the row in the properties box based on the Order data item, and the right column is what I receive from this.

Any steps you think I've missed?

Grateful!
iBoy

[CognosPaul]

I'm a bit late to the party.

There's a supersecret undocumented feature of the hierarchize function. I'm not sure if it works on all data sources, but try the following:

Code Select Expand

hierarchize([Cube].[Time].[Time Hierarchy],post)

It's part of the MDX standard (with support from SSAS and Essbase), but who knows if it will work with DMR or PowerCubes? For some reason the help documentation doesn't bother to list post as a valid parameter, but it works. It may not be the only function with a few undocumented parameters, but let's not get into that now.

[iBoy]

Hey PaulM,

Thanks for the input, this worked perfectly on my dimension.  I now get the intended results as required from beginning of this.

How did you come across it?

iBoy

[CognosPaul]

Embarrassing answer: I found it while trying to solve the question in the post you linked to - but once I found it, I forgot why I was looking for it. And now you all know my terrible secret; I'm a space cadet.

[cognos4321]

I used the expression   "hierarchize([Cube].[Time].[Time Hierarchy],post)" and it displays the hierarchy in the report (row side)exactly how it is in the dimension.
My requirement is to reverse it in order means what's mentioned in the original question

Current hierarchy structure  vs  Desired hierarchy structure
Level 4                                          Level 6
  Level 5                                      Level 5
  Level 5                                        Level 6
    Level 6                                      Level 6
    Level 6                                    Level 5
  Level 5                                      Level 5
    Level 6                                  Level 4

I used sort ascending/descending but that doesn't solve the purpose.

please help.
thanks in advance