Performance - Optimize Office Plus formula files

Hi.

We are using a lot of different and also huge Office Plus Formula files. Last ones can take some time to refresh after changes have been made. To optimize that we like to review these files with focus on performance.

Is there any best practice or tipps how to build up such huge formula files and still keep a good performance? Are there any tricks?

Thanks in advance.

Sylvana

Fax.png

Find more posts tagged with

Infor EPM Discussions

Comments

unknown

Hi Sylvana,

Vit's suggestion to use MDX.EXECUTE in order to retrieve the data in slices instead cell by cell is quite smart. I will definitely check our reports for redesign candidates!

But the are two requrements that must be given:

1. You have to use the "new" O+ formula engine and not the OLAP Add-in with DBGET & Co. But as you are planing a redesign you could easily change the engine if necessary.

2. You can't use it for planning reports because you have no write back.

@Vit: Please correct me when I'm wrong.

Regards
Karsten

Vit Holy

In Reply to Sylvana Neydek:

Hi.

We are using a lot of different and also huge Office Plus Formula files. Last ones can take some time to refresh after changes have been made. To optimize that we like to review these files with focus on performance.

Is there any best practice or tipps how to build up such huge formula files and still keep a good performance? Are there any tricks?

Thanks in advance.

Sylvana

Hello Sylvana,

This is tricky question, cause best approach is to actually 'minimize' the file to get efficient calculation time. The more formulas/sheets/named ranges, etc. the less performant report you'll have. In general, the goal should be to use several single-purpose reports (working with each separately if feasible) than one all-in-one-solution report.

Anyway, the design heavily depends on the use-case. From O+ side of things I can give you only general idea how to use formulas... so if you need to retrieve huge amount of data, I will recommend MDX.EXECUTE formula that can be used for filling sheet with values and further processing/calculations (combined with *LOOKUP functions, etc.). For other cases CELL.GETC in combination with (M)MEMBER* formulas would be probably the best candidate.

HTH,
Vit

Vit Holy

Hello Karsten,

Thank you! Good points! Just a small thought to the second point - it is true but not entirely. Yes, MDX.EXECUTE does not support writeback but I can imagine having MDX.EXECUTE to retrieve whole slice of data with UniqueNames and then have second sheet where you use them for writeback (e.g. you can use *lookup formulas to get the cell you want to change and use CELL.WRITE with Unique Names or part of the report would be CELL.GETC formulas).

It is up to report designer but in general I don't like formula reports where user has 20 sheets with 10k of formulas in every sheet but in reality only e.g. 2k formulas are needed for writeback, the rest is just source of information or needed for side calculations.

Regards,
Vit

Sylvana Neydek

Hello Vit, hello Karsten,

Thanks for all the helpful hints and tricks. I will take an file as an example and will compare the pros and cons (effort vs. performance improvement).

In general, our Office Plus users, use the old way via Alea Adhoc-reports to create formula reports. There are different disadvantages using the new formula engine for our existing users. That's why we haven't pushed it the last years. For me as an AppStudio designer the way via ListDesigner ist much more powerful but also comes with inconvenience of use at some points. So I don't think we will generally move to the new Adhoc reports and therefore MDX formulas here before switching to v12 in the next years which means a general change of the user habits anyway.

Thanks for all your input!

Sylvana

Copyright © 2025 Infor. All rights reserved.