IBM Support

Controller Excel link registry key behaviour - with respect to Citrix and roaming profiles

Troubleshooting


Problem

Customer deploys Controller on a Citrix (or Terminal) server. The Excel link works OK for the administrative user. However, when many/most other users launch Controller, when they click on the 'Excel link' shortcut they receive an error message. The 'bad' users receive the same error if they try to launch Excel only afterwards.

Symptom

The error will vary slightly depending on environment/scenario. Below are some examples:

  • Example #1
    M:\Program Files\Cognos\ccr\ControllerXL.xll cannot be found
  • Example #2
    Information
    The Excel-link is not installed.
    OK

Cause

'Bad' user's Windows user profiles do not contain the correct/necessary entry(s) inside their portion of the Windows registry.

  • In many cases, this is caused by incorrect configuration of Windows user profiles.
  • In some cases, this is caused by incorrect installation procedure of the Controller client
    • Specifically, problems will arise unless the Citrix server was in 'install mode' during the installation.
    • TIP: For best practice hints and tips on how to install the Controller client (including on Citrix/Terminal servers) see separate IBM Technote #1608353.

More Information on Roaming Profiles:
By default, the Microsoft Windows feature 'roaming profiles' is disabled in Windows. A minority of customers use 'roaming profiles'. Using roaming profiles causes files and registry settings to be transferred between different environments.
  • If the Citrix administrator is not careful, then this can cause the client environment to become misconfigured.

There are 2 completely separate/different types of roaming profiles which can be each enabled independently (within Active Directory):
1. "Standard" (normal) roaming profiles.
  • This affects "normal" client PCs (for example desktop/laptop PCs running Windows XP/Vista/7)
2. "Terminal Server" roaming profiles.
  • Using 'Terminal Services' roaming profiles causes file and registry settings to be transferred ONLY between multiple Citrix/Microsoft Terminal Servers (for example Windows 2003/2008 servers, not between the client PCs themselves)

As stated above, using roaming profiles causes files and registry settings to be transferred between different environments. Below are the Controller-specific files and registry settings that we are interested in:

(1) Controller Files:
  • Most Controller-related files are stored inside the user's Windows profile, inside the folder: %APPDATA%\Cognos\CCR
    • By default, this is the folder: C:\Documents and Settings\<username>\Application Data\Cognos\ccr
  • For example, the user's localised Controller settings are stored inside the file "ccr.config" inside this folder
  • However, because this folder is not used by any other (non-Controller) software, using roaming profiles generally does not cause a problem for the Controller files.

(2) Controller Registry Settings:
  • The Controller 'Excel link' registers inside the user's Windows registry (HKCU). Therefore, if you are using Windows roaming profiles, this will cause an error if Microsoft Excel is launched on a PC/server that does *not* have the Controller Excel link software installed.
  • In other words, by default, the standard Controller Excel link client (ControllerClient.MSI) adds the following registry key *only* in the profile of the user who installed the client:
    [HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options]
    "OPEN"="/R C:\\Progra~1\\Cognos\\ccr\\ControllerXL.xll"

TIP: the exact key will depend on the version of Office (9 = Office 2000, 10=Office XP/2002, 11=Office2003, 12=Office2007 for example) and the install location/folder of Controller client.

The purpose of this registry key is to add the Excel link entry 'Controller' into the main menu bar (i.e. the one with 'File - Edit ... Help') of Microsoft Excel.

More Information on Behaviour of Registry Key:
The behaviour of the registry key varies slightly between different Controller versions:
  • Controller 8.1 RTM:

If a new Windows user logs onto the same PC, then they would *never* see the Excel link. This was because this registry key is not placed there by the installation routine for *all* users, just the one that installed the software.
  • Controller 8.1 MR1 and later (for example 8.2, 8.3, 8.4, 8.5 and 10.1 etc.):

These Controller versions attempt to solve the problem automatically, by adding this key (if it does not already exist) for the logged on user, when they launch something from the main Controller (CCR.EXE) program which invokes Excel
  • However, this 'automatic fix' can fail on some 'locked down' environments, for example some customer's Citrix/Terminal Services environments, causing Scenario #2
  • Also, if the I.T. department have configured roaming profiles, then the *same* user's registry keys will be shared/distributed/used on multiple environments (e.g. multiple Citrix servers). Unless these environments are identical (e.g. MS Office and Controller installed in exactly the same location on each one) then the end users may receive errors (e.g. Scenario #1).

Environment

Errors such as these could potentially occur inside any environment which has network roaming profiles. However it is most common within Citrix and Terminal Server environments.

Resolving The Problem

Scenario #1 (most likely) Problem caused by incorrectly installing the Controller client on the Citrix server
Specifically, it is most likely that the administrator forgot to be in 'install mode' when installing the Controller client (e.g. CCRLocalClient.MSI).

To solve this:

  1. Ensure no users logged onto 'bad' Citrix server
  2. Take a backup copy (snapshot) of that server (as a precaution)
  3. Uninstall the Controller client (from Add/Remove Programs)
  4. Launch a command prompt, and type: change user /install
  5. Re-install the Controller client by simply double-clicking on relevant MSI file (for most customers this is CCRLocalClient.MSI)
  6. Launch Controller client, and click 'Excel link' icon (inside client) to check that Excel launched, Excel link worked (no errors)
  7. Close Controller client (and Excel)
  8. Launch a command prompt, and type: change user /execute
  9. Test with new users.

Scenario #2 (reasonably likely) Problem caused by using roaming profiles across non-identical Citrix servers


The best solution will depend on the specific customer's environment. Choose one of the following solutions (whichever is most appropriate for your environment):
  • Solution #1 - Do not use roaming profiles.
  • Solution #2 - Ensure that all environments (e.g. Citrix servers) are configured identically.
    • In other words, all Citrix servers would have the same software (including Controller) installed in the same location, so that the same Windows profile will work inside any environment (because all environments are identical).
  • Solution #3 - If each Citrix server is configured *differently*, then add a logon script (for example modify the top of USRLOGON.CMD on each Citrix/Terminal Server) to add the missing registry key to the appropriate place.
    • See below for the steps to achieve this (solution #3):
  1. Logon to Citrix server as an administrator
  2. Launch NOTEPAD.EXE
  3. Edit the file C:\Windows\system32\usrlogon.cmd
  4. Near the top, add a line to launch a VBS script (for example: C:\utils\Controller8_Excel_2003_link_reg_key.vbs)
  5. Repeat for each Citrix server
  6. On each Citrix server, use NOTEPAD.EXE to create a file (for example 'C:\utils\Controller8_Excel_2003_link_reg_key.vbs') with the following contents:
    ' This script written 20th Nov2006
    ' By Richard.Collins@Cognos.com
    ' to set Controller8_Excel_link_reg_key ON
    ' [specifically for Controller v8 MR1
    ' but should work on other versions]
    ' NOTE: Only tested on WinXP SP2, Office 2003
    '
    ' *** WARNING *** you must modify the path for different
    ' versions of Office, and/or differing Office installation
    ' locations
    '=====================================================
    'ALWAYS examine VBS files before running them
    'If you choose to use this, you do so at your own risk
    '=====================================================
    'Declare and Set Windows Scripting Host Shell Object
    DIM WshShell
    SET WSHShell = WScript.CreateObject("WScript.Shell")

    'Bypass Errors
    On Error Resume Next

    'uncomment out the next line if you want to change citrix to install mode
    'wshshell.run "change user /install",1,True

    ' THE NEXT LINE IS THE IMPORTANT PART

    wshShell.RegWrite "HKCU\Software\Microsoft\Office\11.0\Excel\Options\OPEN", "/R C:\Progra~1\Cognos\ccr\ControllerXL.xll"

    'uncomment out the next line if you want to change citrix to normal mode
    'wshshell.run "change user /execute",1,True

    'End of Script

Naturally:
  • You might need to change the contents of the above for each Citrix server, if there was a different version of MS Office installed, or it was installed in a different location.
  • In addition, if there was an environment (e.g. a specific Citrix server) which did *not* have Controller installed, you would modify the script so that it added a *blank* entry (so that ControllerXL.XLL was not loaded).

Scenario #3 (rare) - typically only occurs in 'locked down' environments
The best solution will depend on the specific customer's environment. Choose one of the following, whichever is most appropriate for your environment:
  • Solution #1 - If each Citrix server is configured *differently*, then add a logon script (for example modify the top of USRLOGON.CMD on each Citrix/Terminal Server) to add the missing registry key to the appropriate place.
    • See section above for steps.
    • Solution #2 - Ensure that all environments (e.g. Citrix servers) are configured so that the default user profile already has the add-in registered for all new users.
    • See below for the steps to achieve this:
    1. Ensure no users logged onto the Citrix server
    2. Logon to the Citrix server as an administrator
    3. Launch Excel
    4. If the 'Controller Excel Add-in' appears in the menu bar, unregister it by clicking on 'Tools - Addins' and unticking 'IBM Cognos 8 Controller Excel link'
    5. Start - Run - 'CMD' <enter>
    6. Change user /install
    7. Launch Excel
    8. Tools - Add-ins
    9. Browse to add-in (e.g. C:\Program Files\Cognos\CCR\controllerxl.xll)
    10. Choose 'yes' to replace existing add-in
    11. OK
    12. Exit Excel
    13. Change user /execute
    14. Afterwards, logon as a brand-new 'test' user and check/tested that the Excel link is there.

    TIP:
    • By using the 'change user' command, we have told the Citrix server that we want the Excel add-in to be added to the registry of ALL end-user's profiles (not just the 'administrator' account that we were logged on as).
    • You may need to look at separate IBM Technote 1347817 to ensure that all non-Controller users do not get an error message when they launch Excel.

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

    Historical Number

    1031065

    Document Information

    Modified date:
    15 June 2018

    UID

    swg21370810

    Page Feedback