All cells return values of blank (0 / zero) when using Optimise2 (ERO)
Customer's spreadsheet is configured to use Optimise2 (ERO). After running/refreshing the spreadsheet (via F9 or F10) this fails to bring in any values from Controller.
Problem only affects those reports with Optimise2 enabled.
The spreadsheet contains blanks where you would expect to see figures/values.
- There is no error message or entry in any log file.
There are many different potential causes for the values of Excel reports to go to zero when the report is run.
This Technote specifically relates to the scenario where the cause is that the end user's Windows user profile has misconfigured %TEMP% and %TMP% variables.
In one real-life example, the customer had configured these to be in different locations:
Diagnosing the problem
It is possible to use Microsoft's "Process Monitor" to monitor the client device when running a report. Inside Process Monitor a "Path Not Found" message will appear, linked to the 'bad' end user's Windows user profile path.
Resolving the problem
Ensure that the user's %TEMP% and %TMP% variables are set up correctly.
- Specifically, they should be configured to point to the *same* folder (for example C:\Users\%username%\AppData\Local\Temp).
Disable the use of Optimise2 for this spreadsheet.
- TIP: Optimise2 is designed to simply speed up the refreshing of reports, so the only side-effect of disabling Optimise2 is to slow down the speed of running of this report.
- The instructions may need to be modified slightly if you are using a different version of Excel.
The following instructions are based on Excel 2007.
1. Launch Microsoft Excel
2. Open the 'bad' spreadsheet
3. Click the tab ' Formulas'
4. Click ' Name Manager'
5. Highlight the entry ' Optimise2' (which refers to Sheet1, cell A1)
6. Click ' Delete':