Active Form MDX Order Question

[abeyk]

Hi, first time posting here. Have done some searching but haven’t been able to find much help.
Building an active form on a cube with 8 dims (6 title dims, 1 row dims, and 1 column dim)


Row dim outline for report

Row Dim1	Row Dim2	Row Dim3
Current Purchases	Auto	Line Numbers (Index 1, 2, etc.)
	Manual	Line Numbers (Index 1, 2, etc.)
Prior Purchases	Auto	Line Numbers (Index 1, 2, etc.)
	Manual	Line Numbers (Index 1, 2, etc.)
Current Sales	Auto	Line Numbers (Index 1, 2, etc.)
	Manual	Line Numbers (Index 1, 2, etc.)
Prior Sales	Auto	Line Numbers (Index 1, 2, etc.)
	Manual	Line Numbers (Index 1, 2, etc.)
Royalties	Auto	Line Numbers (Index 1, 2, etc.)
	Manual	Line Numbers (Index 1, 2, etc.)

Problem is will need to Order the Index line numbers based on one of the elements in the column dim, for each combination of Row dim 1 and 2. Maybe I'm not doing this correctly but I've only been able to get the order function to work by defining the other 2 row dimensions specifically, so I am having to create 10 individual active form reports. I have 10 TM1RPTViews. Currently, I have hardcoded Row dim 1 and Row dim 2 on the active form, so that I can define these as title dimensions in the tm1rptview formula. These were generated by mdx before, but I changed this hoping to reduce the performance impact, which is not a problem, since I need these sections broken out and shown always.

Example Order MDX used for row dim3:

Code: Select all

="{ORDER ({TM1FILTERBYLEVEL( {TM1SUBSETALL( [netOutLines] )}, 0)}, [netOutConf].([netOutLoadType].[Auto],[netOutTransactionType].[Current Sales],[netOut_msr].[SeqNbr]),ASC)}"

We had 3 active forms on one sheet before, but because of the requirement to order each section, I've had to break it into 10. This has worked well, but obviously the time to rebuild the report has increased slightly. For a large customer, it has gone from 9 sec to 18 sec. I know its crazy, but our user cares. Would just like to know if I can improve this in anyway. Thanks and I appreciate any input provided.


Abey

[tomok]

Problem is will need to Order the Index line numbers based on one of the elements in the column dim, for each combination of Row dim 1 and 2.

This is not possible in a single active form. The sort order on the row dims cannot be different for different combinations of other row dim elements. The sort order will always be the same for the entire report.

[abeyk]

Problem is will need to Order the Index line numbers based on one of the elements in the column dim, for each combination of Row dim 1 and 2.

This is not possible in a single active form. The sort order on the row dims cannot be different for different combinations of other row dim elements. The sort order will always be the same for the entire report.

Thanks for confirming!

Abey

[abeyk]

I created this thread today, because I was concerned of the performance hit on using alt+f9 (rebuild). For this specific report, once a user selects a customer, theres really no need for the active form to be rebuilt. I just need certain values recalculated. From some research today I discovered f9(workbook) and shift+f9(activesheet). F9 works, but takes as long as a rebuild for me since I have multiple tabs, and from I've seen shift+f9 doesn't really work for active forms. It just reloads the old value.

Then I decided to create a hyperlink/button to run activesheet.calculate and wow, it works perfectly! Cells recalculated in just over a second! Even recalculates totals!

Found this old thread: http://www.tm1forum.com/viewtopic.php?p=14359 , and in the last post a user states there is an outstanding enhancement request to get shift+f9 to work. This thread is from 2010. Has this request never been answered by ibm? Or am I missing another shortcut?


Abey