IBM Support

All cells return values of blank (0 / zero) when using Optimise2 (ERO) caused by the end user's Windows user profile has misconfigured %TEMP% and %TMP% variables

Troubleshooting


Problem

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.

Symptom

The spreadsheet contains blanks where you would expect to see figures/values.
  • There is no error message or entry in any log file.

Cause

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.

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

Fix:

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).
 

Workaround:


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.
  • Steps:
    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':

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1;10.2.0;10.1.1;10.1;8.5.1;8.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 May 2019

UID

swg21607230