Data Order in Descending using Expression

[tm1_user]

Hi

I am having a View and the requirement is to sort the data in descending order.
I have sorted on CubeViewer and recorded the Expression.

The sorted View is as follows:

Sorted View.docx

Sorted View

(49.05 KiB) Downloaded 298 times

The Expression is as follows:

Expression.docx

Expression

(61.63 KiB) Downloaded 286 times

Now when i drill down the View/expand 'S Series 2WD', the data beyond the level displayed is not sorted.
The Drill Down View is as follows:

Drill down View.docx

Drill down View

(49.19 KiB) Downloaded 264 times

Is there any way I can modify the Expression to apply the Descending sorting when further drilled down.

Thanks

[declanr]

No, the second you expand or collapse any item you cause the "subset" in that dimension to become a manually defined list of elements instead of an MDX driven one.

[tm1_user]

Thanks Declan for the quick reply.

Is there any other method to achieve the data in the sorted order?

Cheers

[tm1_user]

Hi

Can someone throw light on this if we can achieve by Active Form.

Cheers

[declanr]

Same answer as before; there is no difference between a subset editor/cubeviewer/active form. When you expand or collapse an element in your subset your cause it to stop being dynamic.

That being said I have never been inclined to perform an "order" on anything that contains elements at different levels of the consolidation; since you will always (assuming it's a simple weighted consolidation) end up with all your high level consols at one side and all the nodals at the other. Therefore you would completely lose sight of which elements consolidate into which others.
If you took it back to basics of what you are trying to achieve (i.e. a business use of this) then someone might be able to point you in a different direction; as there is a lot you can do with active forms if you get creative.

[tm1_user]

Hi Declan,

The business requirement is to show the elements with the data in the descending order when we drill down a consolidated level without disturbing the nodal order.

[tomok]

Hi Declan,

The business requirement is to show the elements with the data in the descending order when we drill down a consolidated level without disturbing the nodal order.

That functionality DOES NOT EXIST. I've seen this same question asked numerous times before and no one has ever come up with a workaround, at least that they've shared with this board. It is OK to tell your users something just cannot be done. It's not the end of the world.

[tm1_user]

Thanks Declan and Tom.

[dharav]

@TM1_User

I just want to share with my experience,

As we can not apply the your required functionality in existing TM1 environment, It would be better to drill through from external data base.

What we do:

=> We maintain data in SQL Server for data validation due to spread back allocation process.
=> We also stored data which comes from MIS at detailed level
=> In tm1 environment, we set up drill through at detail level through external data source:
1) It retrieves query at required space with required sorting.
2) We don't need to maintained detail level data in to TM1.
3) Performance is flawless as it should be.

You can utilize the pivot table format when you retrieved detailed level (through drill through) data from external database. We just do drill down for the actual entries.

I hope it may help you.

Thanks

Dharav

[paulsimon]

Hi

Some possible solutions to the drill down sort issue

1) Define another dimensions with the elements physically sorted in the correct order (best to edit }DimensionProperties). Then use this in another cube and rule across the data from the original cube. Define a Drill Process on the first cube that opens a view on the second cube.

2) Define a Drill process on the cube that uses TI to create a static subset that has the elements in the right order on the row dimension.

3) If you are using Excel rather than TM1 Web then you can use VBA. Handle the Double Click event get the children of the element using ELCOMP and then sort them in the right order, insert them into the sheet and copy down DBRW

Having said that I have never really had this complaint from a user. I didn't look at the screenshots. Why is it an issue? Is it because the dimension is sorted by Element Name which is a Code, but they want it to be sorted by the Alias Description if they expand when that Alias is shown?

Regards

Paul Simon

[tm1_user]

Thanks Dharav and Paul for the response.

Paul,

To clarify your question ,the requirement is to show the data in sorted order when we drill down a consolidated level without disturbing the nodal order.
The details are in the attachment "Drill down View.docx" in my initial mail.


Cheers