企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 464|回复: 4

Performance Issue in Excel

[复制链接]

73

主题

397

帖子

567

积分

高级会员

Rank: 4

积分
567
QQ
发表于 2014-3-16 21:38:37 | 显示全部楼层 |阅读模式
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
回复

使用道具 举报

76

主题

403

帖子

586

积分

高级会员

Rank: 4

积分
586
QQ
发表于 2014-3-16 23:29:04 | 显示全部楼层
Make sure Excel is in MANUAL calculation mode.
回复 支持 反对

使用道具 举报

96

主题

400

帖子

617

积分

高级会员

Rank: 4

积分
617
QQ
发表于 2014-3-17 00:26:06 | 显示全部楼层
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
回复 支持 反对

使用道具 举报

69

主题

368

帖子

521

积分

高级会员

Rank: 4

积分
521
QQ
发表于 2014-3-17 00:59:52 | 显示全部楼层
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...

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

78

主题

397

帖子

582

积分

高级会员

Rank: 4

积分
582
QQ
发表于 2014-3-17 01:42:18 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-10-20 19:35 , Processed in 0.159602 second(s), 13 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表