Best Practices for TM1 Excel Reporting

[rfielden]

Any documentation about best practices for designing excel reports for use in TM1 web?

[rfielden]

Does any one know if Macros should work?

[lotsaram]

You have to remember that TM1 Web is NOT Excel it is html. No VBA or ActiveX controls are supported (with the exception of action buttons). The documentation may be sketchy on many things but it is pretty clear on this.

A few rules of thumb.
- limit websheets to single tab wherever possible
- limit sheets to no more than 300 rows x 30 columns (or no more than 10,000 calculated cells)
- check the used range of the sheet and delete unwanted empty rows and columns (no point having web render more than it needs to)
- limit cell formulas to pure TM1 formulas (no Excel calculation engine, any pure Excel calcs are being done in html, so alot of them will be a drag)
- be wary of the format of graphics (in earlier versions TM1 web only liked png but now in 9.4 it seems to prefer jpg)
- Try to limit SUBNM to title elements and be wary of using (large) arrays of SUBNM formulas for dynamic row and column headers (SUBNM is very "chatty" with the server)

These are only rules of thumb but provided that you keep within boundaries of sheet size and limiting non-TM1 calculations TM1 web can yield very good performance (eg. less than 1 sec rendering and refresh times). TM1 web is not Excel and was not designed to be, often people make the mistake of trying to access large Excel files via TM1 web (multi-tab, 1000s of rows, etc.) without changing the design of the sheets for use on the web. Rather unsurprisingly poor performance will result.