IBM Support

All cells return values of blank (0 / zero) when using Optimise2 (ERO)

Technote (troubleshooting)


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.

  • TIP: For more examples, see separate IBM Technote #1612145.
    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:
    • %userprofile%\Appdata\Local\TEMP
    • %userprofile%\Appdata\Local\TMP
  • 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 following instructions are based on Excel 2007.
      • The instructions may need to be modified slightly if you are using a different version of Excel.

      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':

    Related information

    1612145 - ** Troubleshooting ** Values of cells inside

    Document information

    More support for: Cognos Controller

    Software version: 8.5, 8.5.1, 10.1, 10.1.1, 10.2.0, 10.2.1

    Operating system(s): Windows

    Reference #: 1607230

    Modified date: 30 June 2015