Performance Issue in Excel

rampagews3

All,
When I try to open a multi-tab Excel report (created using a slice not an Active report) it takes forever (more than 5 minutes) to open the report. I notice that in the bottom of the Excel page it says "Calculating..." with a percentage beside it. This percentage moves very slow. We are on Excel 2003 but have Excel 2007 as well.

Any thoughts?

Thanks in advance.
-Dan

wzsycdh

Make sure Excel is in MANUAL calculation mode.

hiba010203

You probably have auto-calc turned on.  Turn it off.

How many tabs do you have?  As a rough rule of thumb I try to stick to a maximum of five.  And you should never have multiple reports that have the same structure but are just showing different cost centres/departements/regions/whatever, that's defeating the purpose of having TM1.

Martin

luozhongli

Just a quick one to thumb up on Martin comments...
These little things thats seems obvious but you have to fight for in some places...

cat-hao

The following applies if there has been an active form in the spreadsheet previously, or a predecessor of the spreadsheet.  When an active form is created, by default there is a parameter that calculates the entire workbook when you open it (even if calculation is set to manual).  The parameter is stored in excel as a name called "TM1REBUILDOPTION" and by default it equals 1.  If you change it to zero then it will no longer recalculate the entire workbook when it is opened.  The parameter stays in the spreadsheet even if the active form is deleted, so the only way to tell if it there or not is to go to the name manager.

The spreadsheet will still take 5 minutes to calculate when you press F9 but at least you will have a chance to set the correct parameters for the report first, or you can press shift and F9 to calculate one sheet at a time.

There are a few things that could impact the performance of the report.  For example, you could check the "VIEW" formula in each of your sheets because if it has not been set to the correct parameters then the DBRW formulae will retrieve values from the server one cell at a time instead of in batches.