Troubleshooting
Problem
User opens a spreadsheet report (XLSX file). User logs into Controller (Excel link), and presses F9 to run (refresh) the report. This takes a very long time.
Specifically, it gets stuck in the 'Analyzing sheet, please wait...' phase
- During this time, EXCEL.EXE runs at 100%
Symptom
Example:
In one real-life example, the report took over an hour before the user had to manually 'end task' Excel to stop the report from running.
Cause
There are many known causes for slow Excel link reports.
- TIP: See separate IBM Technote #1347497 for more examples.
This Technote specifically relates to the scenario where the cause is a limitation of Controller, with respect to the speed of the function fCompCurr .
Scenario #1 - too many fCompCurr formulae:
In one real-life example, the 'bad' report had 1968 fCompCurr formulae.
Scenario #2 - the fCompCurr formulae refer to multiple (different) periods:
Having multiple fCompCurr formulae with different periods can give performance problems.
In one real-life example, the 'bad' report had separate fCompCurr using multiple periods. For example:
- in one sheet, fCompCurr used period 1602
- in a different sheet (inside the same XLSX file) fCompCurr used period 1807
However, (by checking table xbolvkdall) the customer did not have any currency change (over periods) for any of their companies, therefore (in fact) they could have used the same period for all of their fCompCurr formulae.
Diagnosing The Problem
Scenario #1
Count the total number of fCompCurr formulae in your report.
1. Inside Excel, click CTRL-F
2. Change 'Find what' to be: fCompCurr
3. Click 'Options'
4. Change 'Within' to be 'Workbook'
5. Click 'Find All':
Resolving The Problem
Scenario #1
Reduce the total number of fCompCurr formulae in your report.
Steps:
In one real-life example, the 'bad' spreadsheet had several sheets. In 4 of those sheets, there was a column of identical fCompCurr formulae. The solution was to:
- Keep the identical formulas on only one Excel sheet
- For the other 3 sheets, reference the column from the first sheet.
=> This reduced the number of fCompCurr formulae (which got processed) by 75%.
Scenario #2
Modify the fCompCurr formulae (in your report) to have references to as few different periods as possible.
Example:
In one real-life example, it was possible to modify the 'bad' spreadsheet to use the same period (for fCompCurr formulae) across all the sheets.
- This was possible because the customer did not have any currency change (over periods) for any of their companies, therefore the value returned was be the same (irrespective of the period).
Specifically, the customer:
1. Redesigned the spreadsheet report, so that it only retrieved the company currencies (fCompCurr formulae) on the first sheet (page)
2. Replaced the other fCompCurr formulae (on the other sheets) with vlookups.
Related Information
Was this topic helpful?
Document Information
Modified date:
22 October 2018
UID
ibm10733275