Essbase Excel Add-In users often asked why their Essbase Add-In options randomly change.  

Some of the Essbase options are stored globally, and some are stored at a worksheet level.  The global options should not change unless the user changes them.  The options stored at a worksheet level can appear to change unexpectedly. 

Once a sheet is connected to Essbase, the settings are saved.  They will never change unless the user changes them. 

Where the confusion lies is when a new worksheet is connected to Essbase.  When a new worksheet is created, the most recent options are used as the defaults.  For example, assume the last time a retrieve was requested, or a connection was initiated, the worksheet in question had the zoom option set to all levels.  When a new worksheet that has never been used as an Essbase retrieve is used to connect or retrieve data, it would have the zoom option set to all levels.  Users typically don’t look at the options when they retrieve data.  As worksheets are passed around, and different users apply options differently, the options can appear to change for no reason.

The following are the Global options that are set at the user/PC level.  These settings will be used on all worksheets.  If any of these are changed, they are changed for all worksheets.

  • Enable secondary button setting
  • Enable double-clicking setting
  • Enable FlashBack setting
  • Enable retain member selection information setting
  • Specify message level setting
  • Enable display unknown members setting
  • Enable route messages to log setting
  • Enable purge log setting
  • Enable double-clicking to browse Linked Reporting Objects setting
  • Display Member Select Save dialog box setting
  • Enable Navigate Without Data

The remaining options are saved to each worksheet.  These do not change at the worksheet level once saved, unless the users changes them.

  • Set drill level setting
  • Enable Include Selection setting
  • Enable Within Selection Group setting
  • Enable Remove Unselected Groups setting
  • Specify Indent setting
  • Enable suppress missing setting
  • Enable suppress zeros setting
  • Enable suppress underscores setting
  • Specify Alias for missing text
  • Enable update mode setting
  • Enable Retain on Retrieval formula preservation setting
  • Enable adjust columns setting
  • Enable alias names setting
  • Specify alias names table setting
  • Enable template retrieve mode
  • Enable free form/Version 2.x mode
  • Enable auto sort rows setting
  • Enable use styles setting
  • Specify No Access label
  • Enable Retain on Keep Only and Remove Only formula preservation setting
  • Enable Retain on Zooms formula preservation setting
  • Enable formula fill setting
  • Enable member names and alias setting
  • Enable repeat member labels setting
  • Enable sheet options for Query Designer setting
  • Enable Latest Time Period setting
  • Specify Latest Time Period
  • Enable Hybrid Analysis
  • Enables metadata sampling when performing a Zoom In operation
  • Indicates the percentage of the members to retrieve when metadata sampling is enabled
  • Enables display of the qualified name of the duplicate (non-unique) member in the Excel comment box
  • Enables display of the qualified name of the duplicate member in the Excel spreadsheet