Thursday, January 12, 2006

More Excel Shortcut keys

ctrl + 1 = show format cells dialog box

crtl + 9 = hide row (good for quickly hiding paging dimenions on TM1 slices)

Wednesday, January 11, 2006

Excel F9

Did you know that you can highlight a portion of an Excel formula, press F9 and it will show you the result of the calculation.
I find it really useful when dealing with lots of nested IFs and Indexs etc

Data Spreading Quick Keys

One of the features I use more and more nowadays is the Data Spreading quick keys.
For example I want to put the figure of 50 in 12 months on my budget template. I just type
r>50 into the cell and it repeat the figure 50 to the right until it finds a non DBRW cell.

You can then use other quick keys like P%>110, which is percentage increase right of 10%.

Check out the client guide pdf for all the quick keys and explanations.

M_Clear

If you are using VBA with Excel to do any looping round dimensions or subsets then its a good idea to use the M_Clear macro to clear the memory on the client side.
For example if I execute a TI process through VBA and the TI process updates a subset, if I then try to loop round that subset in VBA code I might get wierd results.
This is where I would use Application.Run("M_Clear") to clear the memory on the client side.

Thursday, January 05, 2006

Excel 12

Have a look at this link to get an idea of how Office 2006 will look:

http://channel9.msdn.com/showpost.aspx?postid=114720

It will be strange to see the Excel TM1 menu in a "Gallery".

Data Validation Lists


If you use Data Validation Lists in your TM1 Excel reports you may have tried setting the list to refer to cells that are not contained in the worksheet that you want the validation list to appear in. In fact when you try to navigate to a different worksheet Excel just refuses to do anything.

You can though get the Data Validation List to use cells on a different worksheet by giving them a Range name and then setting the source to be equal to that Range name. This way you can just have one worksheet to contain all of your lookup values.

MDX

MDX combined with TM1 is a very powerful solution.
MDX may seem complicated to begin with but in fact the basics are pretty easy to get your head around. The best places to start are:

http://www.mosha.com/msolap/mdx.htm

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxbasics_04qg.asp
If anyone wants to learn more about how to use MDX with TM1 then drop me an email at james.wakefield@cortellgroup.com