Troubleshooting
Problem
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.
Symptom
IBM Cognos Controller Link
Out of memory
[OK]
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.
Cause
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 (reference APAR PM74241) 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.
Environment
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
Fix:
Upgrade to Controller 10.2.2021 or later.
Workaround:
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).
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21610976