Excel Add-In Version 7.2.0
March 2, 2017 - Build 5810
The supported versions of Excel include the following: Excel 2010, Excel 2013, and Excel 2016. Although not explicitly supported, there are currently no known issues when using the add-in with Excel 2007.
The supported versions of Essbase include 9.3.0 and higher.
This release was preceded by the following 7.2 pre-releases:
-
7.2.0.5691, December 1, 2016
-
7.2.0.5706, December 14, 2016
-
7.2.0.5782, February 10, 2017
The release notes represent 7.2.0.5810 and all the pre-releases, and contain the following sections:
Fixes
-
When non-numeric text appears in cells located to the right of a cell (or cells) that contains a numeric value, the non-numeric text is not retained when a retrieve operation is performed. For example, “abcdefg” in I3 is not retained unless a retrieve range, which includes the cell, is selected before the retrieve is performed.
To be consistent with the Classic Essbase Add-In behavior, the non-numeric text is now retained without the need to select a retrieve range. It is treated by Essbase as an unknown member, and will be reported as an unknown member on the client when the display of unknown members is enabled. #1684 [7.2.0.5691]
-
When a user moves the Essbase Connections, Essbase Options, or Unknown Members task pane to the top or bottom of the application window, then closes and subsequently reopens Excel, the following error occurs: The specified width is invalid or cannot be set because of the current dock position. #1685 [7.2.0.5691]
-
In cases where an Essbase member name appears to be numeric (or somewhat numeric, such as 123E2000), but cannot actually be converted to a number, Essbase does not return the member name in a retrieve operation, but instead repeats the member name from the cell directly above the pseudo-numeric value. #1692 [7.2.0.5706]
-
When the local system region format settings define the decimal symbol as a comma, the number is not correctly interpreted by the send operation. For example, if the number entered into a cell is 12345,67, the send operation assigns the value as 1234567. #1693 [7.2.0.5706]
-
When the secondary mouse button is enabled, right clicking on a row or column header does not display the Excel context menu. This behavior is not consistent with the Classic Essbase Add-In. #1698 [7.2.0.5782]
-
When the active cell represents a data cell, clicking the Zoom In button on the ribbon or from the context menu does not display the Linked Objects Browser when Linked Object Browsing is enabled. This behavior is not consistent with the Classic Essbase Add-In. #1699 [7.2.0.5782]
-
When a linked worksheet object is opened/edited from a PowerPoint slide, the following error occurs: Unable to handle Application_WindowActivate event. Object reference not set to an instance of an object. This error can also be encountered when the PowerPoint process is executing Excel automation, but without displaying the Excel application. #1700, #1701 [7.2.0.5782]
-
When Excel 2013 is launched from PowerPoint to open/edit a linked worksheet object, the following error occurs: The taskpane has been deleted or is otherwise no longer valid. #1703 [7.2.0.5782]
-
When the Missing Label or the No Access Label contains a formula, such as =VALUE("0"), the following error is encountered when the associated sheet is activated:
Unable to handle Application_SheetActivate event.
Input string was not in a correct format.
This issue was encountered with a workbook that had previously been used with the Classic Add-In. The incompatibility has been addressed, but also note that the Dodeca Add-In will allow the Missing Label to be set to the number 0, so the use of a formula is not necessary. #1722 [7.2.0.5782]
-
When a workbook contains external references (i.e. links) and Undo/Redo is enabled, undoing/redoing an operation displays an Excel prompt similar to the following: This workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have. If a broken link is detected, the following Excel prompt (or similar) is displayed: We can’t update some of the links in your workbook right now. You can continue without updating their values, or edit the links you think are wrong. This issue has been addressed, but with a caveat. If a broken link is detected, the Open dialog is displayed by Excel to allow the user to select a file that corrects the broken link. #1704 [7.2.0.5782]
-
When client-side spreadsheet-related errors occur during the processing of retrieve operation results, the workbook calculation mode is set to Manual. #1696 [7.2.0.5782]
-
When an undo or redo is performed, the status bar retains the previous status and progress text. #1729 [7.2.0.5782]
-
When a workbook is Saved As a different filename, Double-Clicking and Secondary Button functionality cannot be used to initiate an Essbase operation until the file is closed and reopened. #1735 [7.2.0.5810]
-
When a retrieve operation is performed, and the used range as computed by Excel contains empty columns to the right of (or empty rows below) the actual range of non-empty cells, the number of rows retrieved is less than the actual number of “used” rows. The difference between the actual number of rows and the retrieved number of rows is a multiple of 10,000. #1742 [7.2.0.5810]
Essbase Calculations
The ability to run Essbase calculations is supported with this release. The user interface consists of the Essbase Calculations dialog and the Essbase Calculations Status task pane. Both are opened from the ribbon. #1531 [7.2.0.5782]
Essbase Calculations Dialog
The Essbase Calculations dialog contains the following:
Essbase Connection
A read-only field that displays the server/application/database associated with the active sheet.
Available Calculations
This section presents the available calculations for the Essbase connection associated with the active sheet.
Runtime Variables
When a calculation is selected from the list, any runtime substitution variables defined for the script are displayed in the Runtime Variables section. When allowed by a variable definition, the user is able to specify a value. When the script does not contain runtime variables the following is displayed in the Runtime Variables section:
The runtime variables definitions are validated based on the following assumptions:
-
A variable is defined as one of three types: member, string, number
-
A member variable must specify a dimension and a choice (single or multiple).
-
When a member variable is set to “POV”, the value is extracted from the active sheet.
-
When a member variable is not set to “POV”, and is set to either a default or nothing, the user is able to select the member(s) from the Member Selection dialog. In the screenshot above, the Product variable allows the user to select the member(s) by clicking the button to the right of the value field to open the dialog.
-
A string variable allows the user to key-in a member(s), and may not be set to “POV”. The variable must specify a dimension.
-
A number variable allows the user to key-in a numeric value, and may not be set to “POV”.
“Run Calculation” button
Clicking the button executes the selected calculation.
“Run Calculation in the background” checkbox
A calculation can be executed from the client either on the main thread or on a background thread. When the Run Calculation button is clicked, the add-in sends the calculation request to the server either on the main thread or on a background thread, and then waits for the response. The difference is whether the user is able to interact with the Excel application while the calculation is running on the server.
When run on the main thread, the application is suspended or “frozen” until the calculation completes, which prevents any user interaction. When run on a background thread, the user is able to interact with the application, such as performing Excel or Essbase operations, while the calculation is running on the server.
Refer to the Essbase Calculations Dialog Configuration section below for information on how an administrator can control the visibility and/or default setting for the checkbox.
Essbase Calculations Status Task Pane
The Essbase Calculations Status task pane presents information about each calculation that is executed from the add-in. This includes the associated database, status (Started, Completed, Failed, and Unknown), start/end times, and, for a calculation that failed, the error message. The runtime variables, if any, and associated values are also presented. And, for a completed calculation, the messages generated by the database during the calculation.
The calculation information is retained in the Calculations History, which is persisted across Excel sessions. This allows calculations that were run in previous sessions of Excel to be viewed in the status task pane. The Calculation History menu allows all calculations, completed or failed calculations, to be removed from the history.
Essbase Calculations History Configuration
By default, the total number of calculations retained in the history is unrestricted. The Essbase Calculations History Configuration dialog, which is opened using the Calculations group dialog launcher, allows the user to specify the maximum number of calculations retained in the history. The dialog is opened by clicking the Calculation group dialog launcher.
Essbase Calculations Dialog Configuration
As indicated in the description of the “Run Calculation in the background” checkbox above, the visibility, enabling, and default checked status of the checkbox can be configured for a deployment. And, whether the default calculation is included in the list of available calculations can also be configured. This screenshot shows the related settings:
Log Request and Response XML on the Client
The ability to capture all xml transactions between the Dodeca Add-In and the dodeca and dodeca-essbase services has been enhanced to allow for capturing the xml files on a client machine. The ability to capture the xml files on the server continues to be supported, but client-side logging is generally easier to administer. It also has the advantage of capturing only the transactions for the local machine. [7.2.0.5810]
By default, the “Log request and response XML on the client” option is available in the Essbase Options task pane.
To prevent the availability, the option can be disallowed in the deployment configuration, by setting showInTaskPane attribute for the option to “false”:
When the request and response XML logging on the client is enabled, an output folder for the .xml files must be specified. After the user chooses a folder from the Browse for Folder dialog, the selected path is displayed, but can be changed by clicking the Client Log Path field.
To capture the dodeca service xml transactions, which occur at start up for the purpose of validating the license and user, the user must first enable the “Log request and response XML on the client” option and specify the .xml file folder, and then exit Excel and restart.
| The logging capability is not intended to be used at all times, but rather on an as needed basis to capture the information entering and leaving the server primarily for debugging services. Logging can quickly generate a large number of files. |
Office UI Styling
For a more consistent look and feel with the Office themes (referred to as Color Scheme in Office 2010 and Office Theme in Office 2013 and 2016), the Dodeca add-in controls that are displayed in task panes have been revised to use colors that are compatible with the active Office theme. [7.2.0.5782]
For example, the following screenshots show the various task panes with the Office 2016 themes.
Retain Formulas on Retrieval Performance Optimization
Prior to this release, the difference between the retrieval times when retaining formulas versus not retaining formulas could be significant depending on the total number of cells within the retrieve range. The processing for retaining formulas has been optimized such that the difference is now a factor of how many cells contain formulas, which is typically a small percentage of the total number of cells, and can result in comparable retrieval times regardless of whether or not formulas are retained. #1688 [7.2.0.5691]
Undo/Redo Configuration
As an enhancement to the single operation flashback capability provided by the Classic Essbase Add-In, the Dodeca Add-In supports the ability to restore the results of an operation across multiple operations by stepping back or forward an operation at a time or by selecting a specific operation from a history of operations.
By default, a snapshot of the operation is saved after an operation is performed. It was requested that for a standard retrieve operation, that a configuration setting be supported to control whether a snapshot is also taken before the retrieve. The pre-retrieve snapshot allows for any manual edits or format changes done after the previous operation to be restored by selecting or stepping back/forward to the pre-retrieve in the operation history.
The setting is referred to as RetrieveSnapshotSavePolicy. #1674 [7.2.0.5691]
The configuration file contains the setting. This example shows the setting as configured to save a snapshot both before and after a standard retrieve operation is performed.
The pre-retrieve operation is labeled accordingly in the operation history.
Undo/Redo Enabled Option
The Undo/Redo Enabled option is now presented on the Global tab (instead of the Display tab) of the Essbase Options task pane, which more accurately reflects the scope of the setting, since the option is applied globally unlike the Display options, which are applied at the sheet level. #1705 [7.2.0.5782]
Unknown Members
-
When the "Display Unknown Members" option is enabled and unknown members are detected, the Unknown Members task pane is now automatically opened. This represents a change to the previous behavior, which displayed an "Open Unknown Members Task Pane" button in the status bar. This approach was problematic, because when the status bar was not open, the user was unaware of the existence of unknown members. #1502 [7.2.0.5782]
-
For some layouts, when an unknown member is detected by Essbase, the member is flagged as unknown, but the unknown member is replaced on the sheet with a known member from an adjacent cell. [7.2.0.5691]
To get a better understanding, let’s take a look at how the member layout determines the way unknown members are handled by Essbase. This simple layout contains a single dimension in row orientation with two unknown members.
When the data is retrieved, no values are returned for the rows containing Bad Product #1 and Bad Product #2. And, when the display of unknown members is enabled, each unknown member is presented along with address of the cell that contains the unknown member.
The layout becomes more complex when there are multiple dimensions in row orientation.
In this example, Market, Product, and Measures are in row orientation. The spreadsheet contains three unknown members for the Product dimension.
When the data is retrieved, Essbase automatically fills in the last valid member detected for the dimension, which is Colas in B3. The unknown members are reported, but without any corresponding cell location.
