IBM Controller integrates with Microsoft Excel through the Excel Link add-in, allowing users to build and refresh financial reports directly in Excel.
For many organisations, this remains an important and familiar way to work. This is especially true where Controller is used on its own (without IBM Planning Analytics) and Excel is the primary reporting interface.
Where Controller is integrated with Planning Analytics, we would typically expect core reporting, dashboards, and analysis to be handled in Planning Analytics, with Excel Link used more for ad hoc reporting or one-off data extracts outside of standard reporting processes.
Either way, Excel Link for Controller remains a supported and established component of the Controller client, and there are no plans for this to change.
However, as Excel reports grow over time, performance can start to suffer. Large files, historic design decisions, and heavy use of Controller formulae can all contribute to slower refresh times and a less responsive reporting experience.
This article shares practical areas to review if your Controller Excel reports are starting to feel sluggish.
Efficient Report Design #
Controller data is typically retrieved in Excel using fGetVal formulae.
A proven approach is to:
-
Place all Controller formulae (such as
fGetVal) on a single front data worksheet -
Feed all presentation or report tabs from this sheet using standard Excel formulas
This means that when the report is refreshed, Controller only needs to update formulae in one location, rather than searching through the entire workbook. This is far more efficient and easier to maintain.
It’s also worth being mindful of volume. IBM generally recommends keeping fGetVal usage to around 10,000 cells per report. Reports can exceed this and still function, but performance will degrade as the number grows.
If reports have become very large over time, consider whether they can be split into smaller, purpose-specific templates rather than one all-encompassing file.
Audit Reports for Historic Design Issues #
Excel reports often evolve by copying previous versions. Over time, this can introduce hidden complexity that impacts performance without being obvious.
Common issues to look for include:
-
Defined names that reference deleted or duplicated worksheets
Check the Name Manager in Excel to identify broken or redundant named ranges. -
Hidden worksheets containing Controller formulae
These may no longer be needed but are still refreshed every time the report runs, slowing performance. -
External data links to other files, servers, or locations
Even unused links (often hidden) increase the resources needed during refresh.
A periodic clean-up of legacy reports can often deliver noticeable performance improvements with very little effort.
Enhanced Reporting Optimisation (ERO) #
Excel Link reporting can generate a large number of individual data requests between Excel and the Controller database. This “chatty” communication could lead to slower refresh times on larger reports.
Enhanced Reporting Optimisation (ERO) improves this by batching data requests together, reducing the number of round-trips required.
It’s worth noting that product improvements over time have reduced the performance gap between ERO being enabled and disabled. As a result, ERO is now less critical than it once was.
That said, recent testing shows that on IBM SaaS environments, enabling ERO can still deliver around a 15% improvement in report run time, so it may be worth considering for heavily used or complex reports.
-
For on-premise Controller installations, ERO can be enabled via configuration (see the relevant IBM documentation or ask us to help).
-
For IBM SaaS customers, this should be requested via an IBM Support ticket. (Our Shaf can help with this)
Once enabled, cell A1 of the lead worksheet must contain the value Optimise2 for this enhancement to take effect.
Regular Database Maintenance #
Regular optimisation of the Controller database is recommended.
We suggest:
-
Scheduling routine database optimisation as part of month-end or period-end activities
-
For environments integrated with IBM Planning Analytics via FAP publish, regularly truncating data trickle tables
A common approach is to remove FAP publish data older than 30 days, typically as a monthly task. If left unmanaged, these tables can grow unnecessarily and impact performance.
Attention should also be given to the XACCLOCK table, which can accumulate historical account locking records over time. Where period locks are in place, retaining detailed historic locking records is often unnecessary and can be safely reviewed.
Other Factors to Consider #
If performance issues persist, there are additional areas that may need investigation, including:
-
Controller server preference settings
-
SQL Server configuration
-
Overall server capacity and resourcing
Seeing slow performance in your Controller Excel reports?
If you’d like to talk through what might be causing it, we’re always happy to help.
