"Out of memory" when repeatedly running (F9) large/complex spreadsheet report in Controller 10.1.1308 or later

Technote (troubleshooting)


User launches Excel. User opens spreadsheet (XLS / XLSX file) containing Controller formulae. User clicks "Add-ins - Controller - Log in". User logs into Controller. User clicks F9 - the report works OK.
After repeatedly running a number of F9 refreshes, an error appears.


IBM Cognos Controller Link

Out of memory


More Information:

The number of F9 runs/refreshes will vary depending on the complexity/design of the report. In one real-life customer example:

  • Spreadsheet #1 was absolutely huge (42Mb) and had an enormous number (38) of separate sheets
    • It contained more than a quarter of a million fGetVal formulae (254,071 to be precise).
    • In this case, their spreadsheet only ran once successfully. On the second attempt (second F9 run) the error appeared.
  • Spreadsheet #2 was very large (8Mb)
    • It contained 167,363 fGetVals.
    • In this case, the spreadsheet ran successfully 7 times. On the 8th attempt (eighth F9 run) the error appeared.


There are many possible causes for memory issues when running reports.

  • For more examples, see separate IBM Technote #1406746.

This Technote specifically relates to the scenario where the customer is using Controller 10.1.1308 (or later).
  • In other words, the version of Controller used already contains a fix for the known issue described inside separate Technote #1588281.

In this scenario the cause is a known limitation of Controller, which is triggered when the spreadsheet report is extraordinarily large/complex.
  • Specifically, the number of fGetVals is absolutely huge (for example larger than 50,000).

More Information:
IBM recommend that customers design their spreadsheets using the 'best practice' guidelines that can be found inside Technote #1347497.
  • Specifically (for this issue) the most important limit is the number of fGetVal formulae per Excel spreadsheet
    • IBM recommend a maximum of 10000 fGetVal formulae per spreadsheet
    • IBM have found that many more fGetVals (e.g. 50,000) can work OK in some environments, but it is not recommended.


The issue only affects customers whose spreadsheets are extraordinarily large/complex.

Diagnosing the problem

To count the number of fGetVal formulae in the entire spreadsheet, perform the following:

  1. Open the spreadsheet inside Excel
  2. Click "Ctrl-F" (Find)
  3. Inside "Find what" type: fgetval
  4. Click "Options" and change "Within" to be: Workbook
  5. Click "Find All":

Resolving the problem


Reduce the complexity of the spreadsheet (mainly reduce the number of fGetVal formulae).

  • For example, when the real-life customer's spreadsheet #2 was modified so it only contained 40,000 fGetVals, the spreadsheet did not ever give the error message (even when repeatedly refreshed/re-run 60 times).

Upgrade to a later version of Controller (when released) which contains an enhancement to cope with this known limitation.
  • TIP: To find out when this version is released, subscribe to APAR PM74241.

Related information

1406746 - **Troubleshooting** 'OutOfMemoryException' er
1588281 - "Out of memory / 'System.OutOfMemoryException
1347497 - Controller Excel Report Performance Tips
APAR PM74241

Rate this page:

(0 users)Average rating

Document information

More support for:

Cognos Controller

Software version:


Operating system(s):


Reference #:


Modified date:


Translate my page

Machine Translation

Content navigation