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
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).
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:
- Open the spreadsheet inside Excel
- Click "Ctrl-F" (Find)
- Inside "Find what" type: fgetval
- Click "Options" and change "Within" to be: Workbook
- 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.