Dimension Sort Order

[dharav]

I created the generic process to enter the new elements in the dimension while uploading the actual data. It works except the dimension sort order.

Hierarchy:

Consolidation A
Consolidation B
Consolidation C
90012345678
90012345679
90012345680
90012345675 ----> Process entered his element while uploading actual data. I want this element above 9001234578.

The Epilog section includes:

DimensionSortOrder(pDim, 'ByInput', 'Ascending','ByName' , 'Ascending');

E.g.: 90012345678 (all N level elements are simple elements).

Can anyone throw the light on the above issue to sort the child elements either in ascending order?

Please feel free if you require further information.

Thank You

Dharav

[declanr]

DimensionSortOrder is viewed as a metadata process so it needs to take place in the prolog or metadata tabs otherwise it executes but does nothing.

[dharav]

@declanr: Thanks for correcting me.

I insert the following syntax in MetaData:

DimensionSortOrder(pDim, 'ByInput', 'Ascending','ByName' , 'Ascending');

Result:

Consolidation Elements order remains same - Expected behavior as CompSortType is "ByInput"

Child elements order also remains same - Unexpected behavior as ElSortType is "ByName". The child element should be re-order.

Objective:

The order of consolidation should remain same but the order of child elements should be in ascending order based on the name.

E.G.:

Consolidation A LEVEL 3
Consolidation B LEVEL 2
Consolidation C LEVEL 1

90012345675 ---SORTED
90012345678
90012345679
90012345680

Please feel free if you require further information.

Thank You

Dharav

[declanr]

I may be misunderstanding exactly how you want it ordered as its a little difficult to see but I gather you want the components sorted alphabetically? This would be the first argument which you have as 'byinput' meaning they will show in the order that they were input to their consolidation. Try swapping it to 'byname'.

[dharav]

Hi, Declanr

I could not achieve the my objectives by just placing following dimension sort order function. I implement both dimension sort order function and (attributes based & element Level based) MDX to create subset and sort only parent of child elements in customized order. It works now as i needed.

DimensionSortOrder(DimName, CompSortType, CompSortSense,ElSortType , ElSortSense);

The above function can sort the order of all components under a consolidation if there is only one hierarchy exists.

E.g.:

XYZ ---Level 2

PQR---Level 1
1234---Level 0
2341---Level 0
3421---Level 0

ABC---Level 1
34789---Level 0
23789---Level 0
43789---Level 0

Using the dimensionsortorder function, if i sort elements by name in ASCENDING order than following result would appear



XYZ ---Level 2


ABC---Level 1

23789---Level 0
34789---Level 0
43789---Level 0

PQR---Level 1
1234---Level 0
2341---Level 0
3421---Level 0

I wanted the following results:

XYZ ---Level 2


PQR---Level 1
1234---Level 0
2341---Level 0
3421---Level 0

ABC---Level 1

23789---Level 0
34789---Level 0
43789---Level 0

I achieved that using the attributes. I did following

1) apply dimension sort order function --Which sorts both Level 1 AND Level 0 elements in ascending order
2) Now, use MDX based on attributes which creates the subset based on order (attribute), level (element level) and then drill down all members.
3) In TI, Convert dynamic subset in to Static Subset --Optional

Thanks

Dharav