Dodeca Version 8.0.0

June 18, 2021 - Build 569


This version of the Dodeca uses the .NET Framework, version 4.7.2 and higher on the desktop. The components used in this version of Dodeca are SpreadsheetGear 2017 (8.6.1.102), NetAdvantage 2020, Volume 2 (20.2.30), Aspose.Cells 19.5.0, Syncfusion Essential Studio 17.3.0.26, GdPicture.NET 14.1.0.112, and Xceed Zip 3.6.7363.15420.

This version of Dodeca has two server-side services that run inside a Java Application Server. The Dodeca service is supported and tested on Java 1.8. The Dodeca-Essbase service for all Essbase versions prior to, and including, Essbase 11.1.1.3 are supported and tested on Java 1.5. The Dodeca-Essbase service for all Essbase versions 11.1.1.4 and higher is supported and tested on Java 1.6. Both services are known to run on Java 1.7, 1.8, 1.9, and 1.10.

This release, in general, does not contain enhancements or fixes introduced in version 7.8.8.7644 or higher.

The release notes for this version contain the following sections:


Application

  • Default Administrator’s Toolbar - The application toolbars configuration used when an application’s UseDefaultAdminToolbarsConfiguration property is True has been revised. In previous versions, the Admin menu contained the metadata editors as well as various utilities. The Admin menu has been replaced with the Metadata Editors and Admin Utilities menus. #2599

    image
    image

Application - Dockable Window Tools

The Auto Hide Dockable Window, Show Hidden Dockable Windows, and Show Closed Dockable Windows tools are application-level tools, which operate on all docked control panes in the application window, such as the view selector, and in all selected views or metadata editors. In 8.0, this includes docked control panes in both tabbed windows and floating windows.

These tools have been always supported, but could not be added to a view’s toolbars configuration, since the tools potentially affect docked control panes in other windows. By request, in 8.0, the tools can be added to a view’s toolbars configuration, but is not recommended. #2765

Application - Floating View and Metadata Editor Windows

The DSMS client application is an MDI (Multi-Document Interface) application in which the views and metadata editors are presented in tabbed windows, which can be arranged by the user in one or more tab groups. The application also supports dockable control panes, which can be floated, such as the View Selector in the main application window, and selector controls in a view. Multiple control panes belonging to a given window, such as selector controls in a view, can be grouped and positioned as tabs, stacked, side-by-side or in combinations, and can be either docked or floated. #2543

In 8.0, a view or metadata editor window can also be floated, which allows for better utilization of the available screen real estate when using an external monitor.

Within this context, the term tabbed window refers to a window contained within the main application window and floating window refers to a window that is floating and external to the main application. Tabbed windows in the main application can be grouped and floating windows can also be grouped. And, as shown in the screenshot below, floating windows can also be tabbed in a group and/or displayed side-by-side.

image
Figure 1. Application with both tabbed windows and floating windows.

Note that the floating windows always remain on top of the main application window, so the capability is best suited for use with an external monitor.

  • By default, dragging a tabbed window in the main application window moves the tab to a new tab group or an existing tab group. To enable the ability to drag a tabbed window to a floating window, the Drag Tabbed Windows as Floating option in the Window menu must be checked.

    image

    The Drag Tabbed Windows as Floating option is also available in the context menu of a tabbed view or metadata editor window. The setting applies to all tabbed windows.

    image
  • A tabbed window can be floated by 1) dragging the tab with the left mouse button when the Drag Tabbed Windows as Floating option is checked, 2) double-clicking the tab, or 3) using the Float tool from the view or metadata editor context menu.

  • A floating window can be moved to the active tab group in the main application window by 1) clicking the floating window caption with the left mouse button and dragging the cursor to the position indicator at the center of the application window, 2) double-clicking the floating window caption, 3) using the Move to Active Tab Group in Main Window tool from the context menu.

    image

    When the floating windows are laid out as tabs in a group, dragging or double clicking the caption of the active pane moves all the windows in the group to the main application window.

  • Only one window, either tabbed or floating, is active at any time. When the active window contains a view, the application toolbars reflect the mergeable toolbars of the view.

  • A Workspace Layout cannot contain floating windows. If attempted, the Save Layout as File and Save Layout as Binary Artifact tools inform the user that all floating windows must be moved to a tab group or closed before the layout can be saved.

If an application uses the DodecaTeal_OfficeWhite_Style style library, an updated version of the style library is available that contains changes related to floating windows. To update the style library in a tenant, import the artifact from the style_library.zip, which is installed in the Dodeca Framework metadata folder.

Application Metadata Editor

  • Fixed Issue: The Application Metadata Editor allows special characters, such as the percent sign "%", to be included in the application ID. The use of these characters results in an error and prevents the application from being run. #963

Application Styling

The DodecaTeal_OfficeWhite_Style style library, which was made available in 7.5.0.6430, has been updated to address an issue, which occurs when an application’s ViewSelectorProperties sets the ImageBackColor to a non-default value.

Additional refinements have also been made to the library that are specific to the Application - Floating View and Metadata Editor Windows capability.

To update the style library in a tenant, import the artifact from the style_library.zip, which is installed in the Dodeca Framework metadata folder. #2547

Binary Artifacts Editor

  • A new tool, Export All Excel Files, in the Binary Artifacts Editor supports the ability to export all the Excel files that are stored as binary artifacts to a selected folder. #2495

    image

    The name of a given exported Excel file is the same as the Filename associated with the binary artifact. When the folder already contains a file with the same name as a binary artifact’s Excel file, the filename is appended with (x), where x is a number, such as My Excel File(2).xlsx. This can occur when multiple binary artifacts contain an Excel file with the same name.

    When the All Versions option is enabled, the version number is appended to the filename, such as My Excel File, Version 2.xlsx.

    This capability can be used in conjunction with the Workbook Analyzer utility available from the Admin menu, which supports the ability to analyze and optionally clean all workbooks in a folder. Refer to the Workbook Analyzer section for more information.

  • In 8.0, committing a binary artifact, which does not contain an imported file, to the database is not allowed. If an commit is attempted, the following error is displayed: The binary artifact "<binary artifact ID>" does not contain an imported file. An empty binary artifact cannot be committed to the metadata database. After importing a file, retry the commit. #2624

  • In previous versions, when the metadata repository is an Oracle database, and the user attempts to commit a binary artifact, which does not contain an imported file, an error similar to the following occurs:

    Unable to commit "<binary artifact ID> (version 1)" as Added. Unable to process BinaryArtifactUpdateInsert request. ORA-01400: cannot insert NULL into ("DODECA"."BINARY_ARTIFACT"."CHECKSUM")

    In 8.0, an empty binary artifact cannot be committed to the metadata database. #2409

Cascade Views

  • Delimited String Cascade Source - Essbase Excel and SQL Excel views support the ability to cascade based on the selections of one or more POV selectors. In addition, Essbase Excel views support the ability to cascade based on the results of an MDX query. #2737

    In 8.0, the ability to cascade based on one or more delimited strings is supported for both view types. Each delimited string is associated with a token and is comprised of a list of semicolon-delimited strings in which each string represents a cascade item. When a single delimited string is defined as the cascade source, a sheet is created for each item. When multiple delimited strings are defined as the cascade sources, a cascade sheet is created for each unique combination of cascade items.

    The value of a delimited string cascade source can be defined as a token, which is resolved after the BeforeCascadeSheetsBuild event. A tokenized delimited string allows for a workbook script to set the token value dynamically before the view is cascaded.

    The following example illustrates the use of a single delimited string cascade source, which is tokenized. The SQL view allows the user to select the country/region and the state/province, and cascades the view on the cities which are associated with customer contact information in the database.

    image

    The token [T.City] is the used in the relational query, which retrieves the content for each cascade sheet.

    image
    image

    The workbook script adds the [T.CityNames] token as a semicolon-delimited string of the city names returned by the query that retrieves the names of all the cities in the selected state where a customer is located.

    image
  • In 8.0, when a view’s cascade Source Type is EssbaseMdxScript, the Script ID can be tokenized, which allows for the MDX script used for cascading to be determined at runtime. #2723

    The example view presents a simple demonstration of using a token to set the MDX script ID based on a user’s selection. The Income Statement is cascaded either for the selected market Member Only, for the selected market member’s Children, or for the selected market member’s Level 0 descendants. These cascade options are presented by the Market Cascade Level selector. The cascaded members for each option are generated by an MDX script, which is identified by the value associated with the selected Market Cascade Level.

    image

    The Market Cascade Level selector is a Generic selector, and the selector list is configured as a SelectorComboBox, which uses a NameValuePairsDelimitedString to define the DelimitedString as Member Only;Market_MemberOnly;Children;Market_Children;Level 0;Market_Level_0. Each name/value pair represents the name of the item displayed in the selector dropdown list, such as Children, and the value represents the ID of the associated MDX script, such as Market_Children.

    image

    The Market_Children MDX script contains the [T.Market] token, which is the token associated with the Market selector. Although not shown, the Market_MemberOnly and Market_Level_0 MDX scripts are defined for the Member Only and Level 0 selections, respectively.

    image

    In the View Metadata Editor, the view’s CascadeSources is configured with the Source Type set to EssbaseMdxScript and the Script ID set to [T.MarketCascadeLevel], which is the token associated with the generic MarketCascadeLevel selector.

    image

    The token used for each member returned by the query is [T.MarketMdxMember], which is used in the view’s template for token replacement on each cascade sheet and also used as the CascadeSheetNamePattern value.

    image

    At runtime, with East selected as the Market and Children selected as the Market Cascade Level, the Market_Children MDX script is executed, and a cascade sheet is created for each child member of East.

  • In the View Metadata Editor, when a view’s cascade Source Type is EssbaseMdxScript, the context menu for the CascadeSources property supports the ability to navigate to the MDX script in the Essbase Scripts Editor. #2734

    image

Commentary

  • Server-side Performance Enhancement: As the result of optimized SQL query algorithms implemented in the Dodeca Server, the loading of comments is often significantly faster than with previous versions, and is most noticeable when a large number of comments is retrieved. #2371

    In addition, for an Essbase view configured to execute asynchronously, the comments are loaded on a background thread, so while comments are being retrieved, the user is able to interact with the application.

  • Additional properties have been added to the CommentRange definition, which is used by Excel and ExcelEssbase views to configure the various settings that control the location, behavior, and appearance of related commentary. The new properties, KeyItemsColumnRange and KeyItemsRowRange, facilitate the use of Excel formulas on the sheet to construct the key items string for each cell within a comment range. The key items string is used to uniquely identify the comments associated with a given cell. It is a concatenated string of semicolon delimited key/item pairs, such as Measures=Sales;Market=New York;Product=Cola;Year=Jan;Scenario=Actual.

    The KeyItemsColumnRange property is a named range, which represents a single column containing the key item formulas for each row in the comment range.

    The KeyItemsRowRange property is a named range, which represents a single row containing the key item formulas for each column in the comment range.

    In previous versions, the KeyItemsString property is used to specify the key/item pairs, and is still supported. Although the property value may contain formulas and also leverage the use of formulas on the sheet to construct the key items string, the approach for doing so is not always obvious, and if not optimized, can result in additional overhead and negatively impact performance.

    The KeyItemsColumnRange and KeyItemsRowRange properties are intended to not only simplify the configuration of comments, but to also enforce the use of Excel formulas on the sheet to construct the key items string, which yields the best performance. #2371

    For more information, refer to the following section which describes the Comment Range Editor.

  • The Comment Range Editor assists with the creation of the key items formulas in the KeyItemsColumnRange and KeyItemsRowRange, as described in the previous section, as well as with the configuration of other comment range settings. The editor is opened from the Home tab in the View Template Designer. #2542

    image

    Comment ranges can be added, copied, and deleted. In addition to creating and selecting the comment range sheet address, the key items ranges, and constructing the key items ranges formulas, all of the comment range settings can be viewed and edited.

    Any changes made to the comment ranges in the editor are saved to the view’s metadata using the Save button.

    image

    The Comment Range Editor Help, opened with the Help button, contains more detailed information, including instructions with an example view.

    image
  • The CommentsEnabled property, which has been added to the Excel and Essbase Excel view types, controls whether the comments are loaded when the view is built. As noted in the property description, this setting is applied to all the comment ranges, and is typically used for development/debugging purposes. #2625

    image
  • Comment Audit Log - Comment audit logging is supported in 8.0 to provide the ability to track comment changes, including inserts, updates, and deletes. By default, comment audit logging is not enabled in a deployment. To enable, the dodeca.comments.audit-logging setting should be set to true in the dodeca.properties file. #2647

    image
  • Fixed Issue: When a view is refreshed by using the Refresh View tool after a selector’s selection is changed, the on-sheet comments are not cleared from the cells when there are no persisted comments for the newly selected item. #1399

  • Fixed Issue: Comments are deleted after an existing comment is edited in a cell and then another comment is edited in the Comments Explorer and the Save button is then clicked. #1706

  • Fixed Issue: When the Comments Explorer is used to add or change comments, and the user subsequently attempts to build the view without saving the comments, a warning is displayed that unsaved changes exist, but the user is not given the option to save the comments. #2674

  • Fixed Issue: When a comment range contains multiple cells which represent the same unique comment key and a comment is entered for one of the cells, either directly into the cell or in the Comments Explorer, the other cells are not automatically refreshed to display the comment. #2675

Dodeca Framework About Dialog

The Dodeca Framework About dialog has been revised to display additional information related to the Tenant, Application, User, and client. #2682

image

Dodeca Shell

The Dodeca Shell is a command-line utility, which is used to perform actions on the Dodeca repository. The utility connects directly to the repository database, without going through the Dodeca server. An extensive set of commands are supported, including actions related to the repository database, tenants, metadata artifacts, audit logs, comments, licenses, etc.

The Dodeca Shell replaces the Application Setup Utility, JDBC Test Utility, and the Dodeca Backup Utility. #2550, #2650

The Dodeca Shell Reference Guide is available at http://developer.appliedolap.com/docs/dodeca-shell/index.html.

Essbase Connections

In 8.0, the following Essbase connection properties can be tokenized: ServletPath, APSUrl, Server, Application, Database, DataSource. Views, selectors, and workbook scripts can reference an Essbase connection that contains tokenized properties. The tokens can be defined by the application and view token tables. A connection with tokenized properties can also be used in the Essbase Scripts Editor when testing a script as long as the test tokens include the appropriate tokens for the tokenized properties. #2311

A tokenized Essbase Connection should not be used with Application/Security or related functionality (such as HierarchyToRoleMapping when an Essbase authentication service is used.)

To allow for testing a tokenized Essbase connection in the Essbase Connections Editor, the TestTokens property defines tokens for an Essbase connection, which are used by the Test Connection tool to replace the tokenized properties with the associated token value.

image
image

Essbase Excel View - Data Drill-through

In version 6.1.1.3193, which was released on November 10, 2011, the DataDrillthroughMemberToTokenConversionRule property was introduced for the Essbase Excel view type in order to enhance the Intelligent Navigation capability. In particular, the ability to drill-through from a data point in an Essbase Excel view to another view, such as a relational view that presents the associated transactional data within the context of the selected data point. The context, which consists of the contributing member for each dimension that intersects at the data point, is passed to the relational view by way of tokens.

With the initial implementation, the DataDrillthroughMemberToTokenConversionRule property provided a way to derive the members passed to the target view based on a relationship, such as children, descendents, bottom level members, etc., for any given dimension. #1490

For a detailed description of the original implementation of the property, refer to Version 6.1.1.3193, November 10, 2011.

In 8.0, the functionality has been further enhanced to allow for the use of an MDX script to derive the members passed to the target view for any given dimension.

As described in the referenced section, the definition of the conversions rules is supported by the Essbase Excel view’s DataDrillthroughMemberToTokenConversionRule property as a part of data drill-through configuration.

image

In the Member to Token Conversion Rules dialog, the conversion rule type for a dimension indicates whether the members assigned to the token are based on a member relationship or an MDX script. If MDXScript is selected, the script can be selected from a dropdown list of available MDX scripts, which are defined using the Essbase Scripts Editor.

image

The MDX script can contain tokens representing any of the contributing datapoint dimensions, such as [T.Year]. The token for a given dimension is replaced with the contributing member name enclosed in square brackets, such as [Jan].

This example uses an MDX script to assign Market members based on the contributing members for each of the other dimensions.

image
image

The test tokens mimic drilling through on the cell representing Actual Diet Cola Sales in New York for January.

image

When the rule is applied at runtime, the tokens are replaced as follows: [T.Year] with [Jan], [T.Measures] with [Sales], [T.Scenario] with [Actual], and [T.Product] with [100-20]. The token value always represents the member name and not the member alias.

The value of the [T.Market] token passed to the target view contains of the member names returned by the script. By default, the member names are delimited by a semicolon, but a different delimiter, such as a comma, as well as formatting can be specified as a part of the conversion rule definition.

Note

Multiple contributing member names from the same dimension can result from multiple selected data cells. Each is enclosed in square brackets and delimited by a comma, such as [Jan],[Feb],[Mar].

As described in the referenced section from Version 6.1.1.3193, conversion rules can also be defined for the OpenViewForDataCellsToolController.

Essbase Excel View - Miscellaneous

  • In 8.0, the order in which named retrieve and send ranges on a given sheet are processed has changed and is based on the natural order instead of the ascii or alphabetical order. The name of a retrieve or send range must be of the format Ess.Retrieve.Range.X or Ess.Send.Range.X, where X is a unique identifier and is typically a number by convention.

    In previous versions, the following retrieve ranges, for example, are processed in the following order: Ess.Retrieve.Range.1, Ess.Retrieve.Range.10, Ess.Retrieve.Range.2, … Ess.Retrieve.Range.9, such that Ess.Retrieve.Range.10 is processed before Ess.Retrieve.Range.2. With the change in 8.0, the order of processing is Ess.Retrieve.Range.1, Ess.Retrieve.Range.2, … Ess.Retrieve.Range.9, Ess.Retrieve.Range.10. #1906

  • In 8.0, when an Essbase Excel view is cascaded and the cascade source is MDX, additional tokens are created for each cascade item (i.e. each member returned by the MDX query) within the context of a given cascade sheet. These include a token to represent the member name and another for the alias. For example, if the cascade source defines the token as [T.Year] and the alias table as Long Names (in the Sample.Basic database), and the member is Jan, the additional tokens are [T.Year.Name] and [T.Year.Alias] with the values Jan and January, respectively.

    For additional information regarding tokens and MDX cascade sources, refer to Essbase Excel View in the 7.8.5.7321 Release Notes.

Essbase Functionality

  • Ancestor On Top - Essbase 19c supports the ability for a user to set whether ancestors are positioned at the top or the bottom of related child members in the results of a zoom operation. #2493

    To enable this capability, an administrator must set the Essbase server-side application config setting, SSANCESTORONTOP, to True.

    In 8.0, an ancestor position Ancestor On Top Essbase option is supported.

    image
    image

    An AncestorOnTop EssProperties setting is also supported, and as with other options, can be excluded from being presented in the Essbase Options dialog.

    image

Essbase Member Select

  • For an ASO cube, the Member Select dialog no longer includes the Attribute Calculation dimension in the Dimension dropdown list. ASO cubes only support the Sum attribute calculation. #2458

  • Fixed Issue: The Member Select dialog, which is typically used only in an Essbase Ad hoc view, fails to complete the expansion of a parent member in the tree when the parent has a significantly large number of child members, such as several hundred thousand. #2393

Essbase Service

  • The Essbase service no longer supports Essbase Peformance Logging. To reflect this change on the client, the default Administrator toolbar’s Utility menu does not include the Log Essbase Performance to Server options and the DoEssbasePerformanceLogging property is not displayed for an Essbase connection in the Essbase Connections Editor. #2448

Essbase Scripts Editor

  • The Depends On and Depended On By tools have been added to the Essbase Scripts Editor. #2745

Essbase SmartLists

Dodeca has supported Essbase text measures (aka SmartLists) since version 5.0.0.2260 by optionally displaying the text list names instead of the related ID or number for data cells associated with a text list. In 8.0, the functionality has been enhanced to support the ability to optionally display a dropdown list containing the text list names, which allows a user to view and select from the valid values. #1958

image

The dropdown lists are implemented using Excel data validation.

image

The display of text list names instead of the associated number is controlled by the EssProperties.UseSmartLists metadata property. Both the EssProperties.UseSmartLists and EssProperties.UseSmartLists_ShowDropDownList properties must be set to True for the dropdown lists to be available.

image

The settings are also available in the Essbase Options dialog.

image
When UseSmartLists_ShowDropDownList is True, the connection for which the SmartList values defined, must be configured with the ExtendedCubeInfoEnabled property set to True; otherwise, no dropdown lists will be displayed.
image

Excel-based Views

  • In previous versions, when the value of the GridContextMenuID, GridContextMenuIDForDrillthroughSheet, or SheetContextMenuID property of an Excel, SQL, or any Essbase view type is not valid, no error message is displayed. The property value can be assigned either in the view’s metadata or by a workbook script in the RequestContextMenuID and RequestSheetContextMenuID event links. To be valid, the ID must be the key of a PopupMenuTool in the view’s toolbars configuration, i.e. ViewToolbarsConfigurationID. #2688

Excel Behavior

  • Auto-Complete - In Excel, when the user is typing into a cell, the auto-complete functionality attempts to anticipate what the user is intending to type and automatically matches existing values within the same column and fills in the cell value based on the closest match. This allows a value that has previously been entered to be subsequently entered without completely retyping the value. Alternatively, the user may pick the value from a dropdown list, which is similar to, but not the same as a data validation list, which does not find matching items when the user is typing. #2708

    In 8.0, the Dodeca Framework supports a capability similar to the Excel auto-complete functionality. The list of available cell values is generated from a list source, which can be configured as a predefined delimited string, a relational query, an MDX query, or a sheet range within an Excel binary artifact. As the user is typing into the cell, the values in the list of available values, which start with or contain (as configured) the current value in the cell, are presented in a dropdown list. The dropdown list is automatically updated with each entered key.

    image

    When the cell is empty or no match is detected, all of the available list items are displayed in the dropdown list.

    image

    The auto-complete capability is supported for Excel, SQL Excel, and Essbase Excel views, and is configured in the metadata by the AutoCompleteRanges property. An AutoCompleteRange defines the range of cells for which the configuration applies as well as the source from which (and when) the list of available values is generated along with various aspects of the presentation and behavior.

    image

    The ListSourceType indicates the source of the list of available values presented in the dropdown list: Undefined, SQLPassthroughDataSet, DelimitedString, MdxScript, and Range.

    image

    SQLPassthroughDataSet - The list is retrieved by a relational database query defined by the SQLPassthroughDataSetID. If specified, the DataTableName is the name of the data table returned by the SQLPassthroughDataSet that contains the cell values. If not specified, the first data table is used. The ItemColumnName is the name of the column in the data table that contains the cell values. If not specified, the first column is used. The SQLPassthroughDataSetID, DataTableName, and ItemColumnName values can be tokenized. The SelectSQL of the SQLPassthroughDataSet can also be tokenized.

    When using a SQLPassthroughDataSet as the list source type, it is recommended to set the SQLPassthroughDataSet.SessionCachingEnabled property to True unless the query is data-dependent or if the results are likely to change during a session. The results of a tokenized query can be cached.

    DelimitedString - A semi-colon delimited list of strings, which is defined by the DelimitedString property. The value can be tokenized.

    MdxScript - The list is retrieved by an MDX query using the specified MdxScriptID. The MdxScriptID value and the associated query can be tokenized. The section, Using an MDX Script with an AutoCompleteRange, provides more details.

    Range - Based on the RangeSourcePolicy, which is either ViewWorkbook or ExcelBinaryArtifact, the list is extracted from the RangeSourceSheetRangeName in either the view’s workbook or the specified ExcelBinaryArtifact. The RangeSourceSheetRangeName value can be tokenized.

    The ListLoadPolicy indicates when the list of values is generated:

    OnViewOpen - The list is generated when the view opens. This is the policy to use when the list source is not tokenized and is not dependent on the context of the active cell.

    OnViewBuild - The list is generated when the view builds. This is the policy to use when the list source contains tokens, such as selector tokens.

    OnDemand - The list is generated when the user starts typing into a cell in the SheetRangeAddress. This is the policy to use when the list is dependent on the context of the active cell. For example, in an Essbase Excel view, when the ListSourceType is MdxScript and the MdxScriptConnectionPolicy is UseRetrieveRangeConnection.

    In the example SQL view, an AutoCompleteRange is used to define the values that can be entered as a Job Title, as shown in the screenshot above.

    image

    The SheetRangeName is AutoComplete.JobTitle.Range, which is a named range that references a column in the DataSheetRangeName as configured in the view’s DataSetRanges.

    image

    The ComparisonType controls whether the items displayed in the dropdown list represent the available items that StartsWith or Contains the entered text value. The ListSortPolicy is Ascending, Descending, or None, which is the order returned by the source.

    The ListDropDownWidth controls whether the width of the dropdown list is the ColumnWidth or the computed AutoSize width, which is the width of the column or the longest item, whichever is greater. The MaxListItemsDisplayed is the maximum number of items displayed in the visible portion of the dropdown list. When the total number of matching cell values exceeds the maximum, a vertical scrollbar is displayed.

    The ItemNotInListPolicy controls whether a cell value can only be set to a value in the list of available values. With LimitToList, a cell value is limited to the values in the list. With AddToList, a cell value can be set to a value that is not in the list, and the value is added to the list base on the AddToListPolicy. With Ignore, a cell value can be set to a value that is not in the list, and no error or confirmation is displayed.

    The ErrorMessageCaptionForLimitToList and ErrorMessageTextForLimitToList can be used to customize and override the default caption and text of the error displayed when the value is limited to the list and the user enters a value that is not in the list.

    image

    The ConfirmMessageCaptionForAddToList and ConfirmMessageTextForAddToList can be used to customize and override the default caption and text of the confirmation displayed when the value is not limited to the list and the user enters a value that is not in the list.

    image

    Using an MDX Script with an AutoCompleteRange

    The ListSourceType can be set to MdxScript for any of the view types that support the auto-complete functionality. This allows for an Excel or SQL Excel view to present member names or aliases as the available cell values within an AutoCompleteRange.

    For an Excel and SQL Excel view, the EssbaseConnectionID and EssbaseLoginServiceObjectTypeID must be specified, and the UseAliases and AliasTable settings default to False and Default, respectively. The value of the EssbaseConnectionID can be tokenized.

    image

    For an Essbase Excel view, the Essbase connection used by the MDX script is controlled by the MdxScriptConnectionPolicy, which includes the following options:

    UseViewConnection - Uses the EssbaseConnectionID assigned to the view.

    UseSpecifiedConnection - Uses the specified EssbaseConnectionID.

    UseRetrieveRangeConnection - Uses the Ess.Retrieve.Connection.<id> named constant associated with the retrieve range in the template, which contains the cell.

    image
    For an Essbase Excel view, the NonEssbaseCellDoubleClickPolicy property must be set to EnterEditMode to allow for the auto-complete functionality to be activated when edit mode is entered by double-clicking a cell in the range.

    Workbook Script BeforeAutoCompleteRangeListLoad Event Link

    The workbook script event link, BeforeAutoCompleteRangeListLoad, allows a workbook script to customize the list of cell values for the active cell within the context of other cell values. The event occurs before the list of cell values is generated or loaded from the list source. #2784

    To demonstrate the use of the event link, the following example implements a use case, which is conceptually similar to dependent selectors. Within a given row, the first column represents a country or region, the next column represents a state or province within the selected country or region, and the last column represents a city in the selected state or province.

    image

    An AutoCompleteRange is defined for each column with the ListSourceType defined as OnDemand.

    image

    So, for example, when the cell enters edit mode in a cell in the State / Province column, the BeforeAutoCompleteRangeListLoad event is raised, and the workbook script sets the [T.CountryRegion] to the value in the Country / Region cell in the same row.

    image

    The list is loaded from the results of the relational query defined by the SQLPassthroughDataSetID associated with the StateProvince AutoCompleteRange. The select SQL statement contains the [T.CountryRegion] token, which is replaced before the query is executed.

    image
    image

    The workbook script detects when the cell value that is depended on by the active cell has not been entered, displays an error message, and cancels the event.

    image

    The last example illustrates the settings required to emulate the Excel auto-complete behavior, which populates the dropdown list with existing values in the same column as the active cell.

    image
  • Tab Ratio: A new property, TabRatio, has been added for Excel, SQL Excel, and Essbase Excel views. The value controls the ratio of the width of the worksheet tabs to the width of the horizontal scrollbar as a number between zero and one. By default, the value is .6.

    image

    For example, if set to .75, the width of the area for worksheet tabs is 75% and the width of the scroll bar area is 25% of the total width of the view window.

    image

    The SetWorkbookOptions workbook script method also supports a TabRatio argument

  • Fixed Issue: Using conditional formatting, when the Format Style is set to Icon Sets and the Type to Percentile, all the cells display the icon associated with the lowest value. #2266

  • Fixed Issue: When an Excel, SQL Excel, or Essbase Excel view is configured with the AutoCalculateEnabled property set to True, the computed values displayed in the status bar are inaccurate when any cells in the selected range contain the SUBTOTAL formula. #1511

Excel File Encryption

Excel supports file-level, workbook-level, and worksheet-level protection. At the file-level, an Excel file can be encrypted, which requires a password to encrypt and to open the file.

In Excel, the file is encrypted by selecting File > Info, then Protect Workbook and choosing Encrypt with Password. and then entering the password.

image

As a historical reference regarding the type of encryption used by Excel version, this is an excerpt from https://en.wikipedia.org/wiki/Microsoft_Office_password_protection:

Excel and Word 2010 still employ AES and a 128-bit key, but the number of SHA-1 conversions has doubled to 100,000[2] further increasing the time required to crack the password.

Office 2013 (Access, Excel, OneNote, PowerPoint, Project, and Word)uses 128-bit AES, again with hash algorithm SHA-1 by default.

Office 2016 (Access, Excel, OneNote, PowerPoint, Project, and Word) uses 256-bit AES, the SHA-1 hash algorithm, and CBC (Cipher Block Chaining) by default.

Dodeca Excel File Encryption Overview

In 8.0, Dodeca optionally supports two levels of Excel file encryption:

  • An encrypted Excel file can be used as the view template for an Excel, SQLExcel, and ExcelEssbase view. When the view is initially built, the user is prompted for the password. In the View Template Designer, the user is also prompted for the password. During a session, passwords are retained in memory to eliminate the need for the user to reenter a given password. When the view is saved to an Excel file by saving as or exporting to Excel, opening in Excel, or attaching the view as an Excel file to an email, the encryption and password are retained, and the password must be entered when the file is opened in Excel.

  • An Excel, SQLExcel, and all Essbase view types can be configured to encrypt the saved Excel file when the view is saved as, exported to, or opened in Excel, or attached to an email as an Excel file. The user must provide the password, which is then required by Excel when the file is opened. The EcryptSavedExcelFile property must be set to True to enable this behavior.

As with Excel, a lost or forgotten password cannot be recovered. #2581

SpreadsheetGear and Excel File Encryption

Dodeca uses the SpreadsheetGear for .NET components, which consists of a spreadsheet user-interface control as well as an API, which is used for all Excel functionality. As such, the extent of the Excel file encryption capabilities within Dodeca are dependent on the level of support provided by SpreadsheetGear.

SpreadsheetGear supports reading and writing encrypted workbooks (.xlsx and .xlsm) with the new Agile Encryption used by Excel 2013 and Excel 2016, including default SHA-512 encryption for workbooks. The SpreadsheetGear API can be used to open encrypted Excel files (.xlsx and .xlsm) created in Excel 2013 and later. And, the Excel file is encrypted when the SpreadsheetGear API is used to save a workbook with a password.

Excel File Encryption Behavior and Considerations

  • An encrypted Excel file (within the constraints of the SpreadsheetGear capability) can be used as the view template.

  • When the view template is encrypted, any Excel file that is created by saving the view to an Excel file, including when saving the view as, opening in, exporting to Excel, or attaching as an Excel file to an email, retains the encryption.

  • Importing an encrypted Excel file from 1) the Binary Artifact Editor, 2) the view selector’s Import View Template context menu tool, or 3) the Excel View Wizard does not require entering the password.

  • When the view template is encrypted, the user is required to enter the password when the view is initially built. Clicking Cancel effectively cancels the build.

    image
  • Passwords entered by the user are retained in memory and are then used to attempt to open a template before prompting the user for the password. The user should never be prompted for the same password within a session.

  • When the View Template Designer is launched for an encrypted view template, the Excel Password dialog appears before the designer is opened. If canceled, the designer is not opened.

  • If encryption is only required for an Excel file that is created when the view is saved to an Excel file, the EncryptSavedExcelFile property can be set to True. This property is supported for an Excel, SQLExcel, and all Essbase view types.

    image

    The Open In Excel, Save As Excel, Export to Excel, Open in Excel, and Send to Email Recipient as Excel Attachment tools will prompt the user for a password to encrypt the saved Excel file.

    image

Excel View Wizard and Excel File Encryption Behavior

  • When the View Template Options - Create an Excel File option is selected, the new Excel file can be encrypted by specifying a password.

    image

    Clicking the Help button to the right of the password field displays this message box:

    image
  • With the View Template Options - Import an Excel File option, when the selected Excel file is encrypted, the encryption is noted under the file name.

    image

View Template Designer and Excel File Encryption Behavior

image
  • If the view template is encrypted and the password has not been used in the session, the Excel Password dialog is displayed before the View Template Designer is opened. If canceled, the designer is not opened.

  • Save Template and Save Template As New Version tools: If the persisted view template is encrypted, but the active workbook is not encrypted, and the user attempts to save, the following message is displayed:

    image

    If the active workbook is also a file type for which encryption is not supported, such as a .xls file, the message indicates the file format will be changed.

    image
  • Versions tool: Since some versions of the view template may be encrypted and other versions not, selecting a version from the Versions list will show the Excel Password dialog when an encrypted version of the view template is selected for which the password has not previously been used in the session.

  • Open In and Save As Excel tools: If the view template is not encrypted and the view’s EncryptSavedExcelFile property is True, the tools will show the Excel Password dialog to obtain a password from the user to encrypt the Excel file.

  • Encrypt tool: Supports encrypting the view template, if not already encrypted, or changing the password of an encrypted template.

    image

    If the active workbook is already encrypted, this message box is displayed:

    image
  • Open Workbook tool: If the opened workbook is encrypted, the Excel Password dialog is displayed (again, assuming the password has not previously been used.)

Import View from Excel and Excel File Encryption Behavior

  • When a view is exported to Excel and either the view template is encrypted or the EncryptSavedExcelFile property is true, the password is required to import the view (assuming the same password has not already been used in the session.)

Workbook Script Debugger and Excel File Encryption Behavior

  • The Save As Excel and Open In Excel tools enforce the EncryptSavedExcelFile property.

Exporting an Excel File as a Binary Artifact

For the user-interface components that support exporting an Excel file from an Excel binary artifact, the filter displayed in the Save File dialog’s file type dropdown list has been corrected to reflect the format of the encoded Excel file. #2524

The components include the Export tool in the Binary Artifacts Editor, and the Export Excel Template in the View Selector Explorer Bar and View Selector Tree context menus.

The correction has also been made to the Save As Excel tool when saving a view as an Excel file.

Importing an Excel File as a Binary Artifact

For the user-interface components that support importing an Excel file as an Excel binary artifact, the filters displayed in the Open File dialog’s file type dropdown list have been corrected to reflect the file types supported for an Excel template. #2524

image

The components include the Import tool in the Binary Artifacts Editor, the Import Excel Template in the View Selector Explorer Bar and View Selector Tree context menus, and the View Wizard.

The file type filters include:

  • Excel Workbook (*.xlsx) and Excel 97-2003 Workbook (*.xls)

  • Excel Macro-enabled Workbook (*.xlsm)

  • CSV (Comma-delimited)(*.csv)

  • All files (*.*)

The same filter is also displayed by the Open File dialog used by the Open Workbook tool in the View Template Designer.

Metadata Dependency Explorer

  • Optimized the scanning of binary artifacts when searching for Depended On By dependencies. #2614

  • When searching for Depended On By dependencies and an error is encountered on the server when analyzing a binary artifact, the error is presented in the Metadata Dependency Explorer below any dependencies. At present, the only anticipated error will occur when an Excel binary artifact contains an encrypted (i.e. password-protected) file, since an encrypted file cannot be analyzed. The Excel binary artifacts are only analyzed when a Depended On By search is performed for an Essbase connection. #2666

    image
  • Fixed Issue: The workbook script methods, CopyFromBinaryArtifact.Range, BuildRangeFromScript.ImportRange, and OpenBinaryArtifact.General, require the specification of an Excel binary artifact ID. The VersionPolicy argument indicates whether the latest version or the specified version should be used by the method. If no value is specified, the default is Latest. If the value is Specific, another argument indicates the specific version.

    When the dependency analyzer evaluates the workbook script for dependencies, if no version is specified, an error similar to the following occurs: #2461

    The search failed with the following error: Error using analyzer com.appliedolap.dodeca.deps.analyzers.WbsMethodAnalyzer@3685d047 to analyze artifact My_Workbook_Script/WORKBOOKSCRIPT/1

    In 8.0, the appropriate version of the Excel binary artifact, based on the argument values is returned as the dependency. #2620

  • Fixed Issue: When the Log Application Request and Response XML to Server is enabled, the Metadata Dependency Explorer displays the following error when searching for Depends On dependencies: The search failed with the following error: Root element is missing. #2510

Metadata Editors

  • Several of the metadata editors have always supported the ability to filter the metadata items based on a type. For instance, in the View Metadata Editor, the views can be filtered based on view type to show only those views of the selected type, such as ExcelEssbase. #2556

    In 8.0, an additional filtering capability is available, which is the ability to filter the metadata items based on whether the item text matches keyed-in characters. The filter option controls whether the current character string must match the start, end, or be contained by the displayed text for the item.

    For example, in this screenshot of the View Metadata Editor, only ExcelEssbase views with a name that contains "product" are displayed. The items are automatically filtered as each character is entered.

    image

    A list of the 10 most recently used character strings is retained across sessions for each metadata editor.

    image
  • The Application, View, and View Property Set Metadata Editors allow for the metadata instances to be displayed by the ID or the Name. By default, the Name is displayed. In previous versions, this setting was not retained across sessions, so if an administrator prefers to see the ID, the ID button would have to be clicked each time the editor is opened. In 8.0, the setting is retained and restored. #1562

    image
  • In previous versions, the only option supported by the metadata editors for committing new, modified, or deleted metadata instances was to commit all the changes to the metadata repository at once. In 8.0, it is possible to commit only the selected metadata instance. For instance, this screenshot of the View Metadata Editor shows the Commit Selected and Commit All tools. #2160

    image

    In the Workbook Script Editor, the tool in the toolbar, which previously committed the changes for all workbook scripts, has been modified to commit only the workbook script presented in the editor.

    image
  • Fixed Issue: In the Metadata Editors, items are not sorted based on the natural order. For example, in the Binary Artifacts Editor and the Essbase Outline XML Binary Artifacts Editor, when all versions are displayed and include, for example, Version 1, 2, and 10. This also occurs when ID’s or names contain numbers, such as View1, View2, and View10. As such, Version 10 and View10 should appear in the list after Version 2 and View2, respectively. #2558

Metadata - Importing and Exporting Metadata from a Local Zip File

  • Imported Date and Imported By - In addition to the Created Date, Created By, Updated Date, and Updated By fields that are maintained for metadata binary artifacts, additional fields have been added to retain the most recent date and identity of the import of a binary artifact. #1496

    When a binary artifact is imported using the Import Metadata from Local Zip File tool in the client, the current user is assigned to the Imported By field.

    When a binary artifact is imported using the Dodeca Shell, the Imported By value is system by default. The optional --imported-by parameter can be used to specify a value, which is assigned to the Imported By field.

Open View Tool Controllers

  • An AutoBuildOnOpen tool argument has been added to the OpenViewForDataCellToolController and OpenViewForMemberCellToolController tool controllers. By default, the value is Default, which indicates that the opened view’s AutoBuildOnOpen setting should be used. If the tool argument is set to True or False, the opened view’s AutoBuildOnOpen setting is overridden and the specified tool argument value is used. #2499

  • A ShareSelectorTokens and a ShowViewAsActiveWindow tool argument has been added to the following Open View tool controllers:

    • OpenViewToolController

    • OpenViewUsingActiveWorksheetAndSelectorStateToolController

    • OpenViewUsingActiveWorksheetStateToolController

    • OpenViewUsingSelectorStateToolController

    • OpenViewUsingViewStateToolController

      The ShowViewAsActiveWindow tool argument has also been added to the OpenViewForDataCellToolController and OpenViewForMemberCellToolController.

      The ShareSelectorTokens argument controls whether the current view’s selector tokens are added as tokens to the opened view. When added, the tokens are used to set default selections, when applicable, and can be referenced in workbook scripts, SQLPassthroughDataSets, etc. as used by the opened view. #1752

      The ShowViewAsActiveWindow argument controls whether the opened view’s window becomes the active window. By default, the opened view’s window is activated. The ability to control the activation of the window allows for use cases for which retaining the current view’s window as the active window is preferable. #2613

      To support the same option when a view is opened by a workbook script, a ShowViewAsActiveWindow method argument has been added to the OpenView workbook script method.

Relational Essbase Outline Caching

Introduced in version 6.0.0.3106, the Relational Essbase Outline Caching capability provided the ability to store an Essbase outline, represented by an Essbase connection metadata instance, in a relational database. This functionality has been removed in 8.0, since it was complex to set up and maintain, and alternatives now exist. #2521

This is a potential BREAKING CHANGE. The Relational Essbase Outline Caching was used primarily to provide full-text search for Essbase members. As of version 7.8.5.7321, a new Essbase search algorithm was implemented that does not require a relational outline cache. It may also be possible that some customers have used the cached outlines for retrieving Essbase outline information into a grid within a Dodeca view. The same functionality can be achieved by using the relational extract functionality of the Next Generation Outline Extractor.

Relational Views

  • When a SQL view with a long-running relational query is building, the user is able to perform another task in the application, such as activating another window, since the query runs in the background. In previous versions, upon completion of the build, the window with the long-running query would automatically become activated. In 8.0, the window is not automatically activated in order to allow the user to continue uninterrupted with the current task and then decide when to change the active window. #1826

  • Fixed Issue: In SQL views and (the relational functionality in) Essbase views, when primary keys are entered by the user into a data table range on the sheet, duplicate primary keys are not detected on the client when a save is attempted. #2563

ScriptEssbase View

  • An AttributesPolicy property has been added to the ScriptEssbase view type. The property controls whether the server performs a retrieve on the results returned by the script in order to obtain attributes for the member and data cells, which are then returned with the results to the client. By default, the value is set to NoAttributes. #2308

    The AttributesPolicy property was added to allow for the report script used by a ScriptEssbase view to suppress row and column headings.

    Attributes should not be included if the report script suppresses page and column headings using suppageheading and supcolheading.

    For example, for this layout, the report script uses suppageheading and supcolheading and the AttributesPolicy property is set to NoAttributes.

    image

Selector List Metadata Editor

  • Added support for the ability to navigate from a selector list in the Selector List Metadata Editor to the associated SelectorID in the Selector Metadata Editor. #1975

    image

Selector List - Range Selector List

The Range selector list supports the ability to extract the selector item strings from a sheet range within an Excel binary artifact. This selector list type can be associated with a Generic selector, which includes the SelectorComboBox and SelectorListBox. #1739

An EssbaseRange selector list can be associated with an Essbase selector.

In addition to the standard selector list properties, the Range selector list includes the RangeSourcePolicy, which controls whether the view’s Excel binary artifact, or a specified Excel binary artifact, is opened to obtain the selector item data. When the policy is set to ExcelBinaryArtifact, the ExcelTemplateBinaryArtifactID property identifies the Excel binary artifact.

image

The RangeName, which can be tokenized, identifies the workbook or sheet-level range from which the cell values are extracted.

If the ExcelTemplateBinaryArtifactID is specified, the RangeName can be selected from a dropdown list of all the named ranges in the template workbook. For a sheet-level range, the range name should be qualified with the sheet name.

The RangeCellSelectorItemPolicy property controls whether each cell or each pair of cells within the range is treated as a selector item.

image

A selector item has a value and display text. The display text is presented in the selector control. The value is used for the selector’s token. With the EachCellAsSelectorItem option, each cell represents a selector item. The value and display text are the same as the cell’s value.

With the CellPairsAsSelectorItemValueAndDisplayText option, each pair of cells in a row is treated as a selector item, where the first column’s cell value is used as the item value and the cell in the adjacent column is used as the display text.

Conceptually, the EachCellAsSelectorItem option is similar to the DelimitedString selector list, whereas the CellPairsAsSelectorItemValueAndDisplayText option is similar to the NameValuesPairDelimitedString selector list.

As an example of the CellPairsAsSelectorItemValueAndDisplayText option, each row in the Numbers range contains two pairs of cells, The first cell’s value in each pair, such as 1, is used as the selector item’s value, and the adjacent cell’s value, such as One, is used as the text displayed in the selector tool and control.

image

By default, the SessionCaching property is True, since the cell values are not likely to change over the course of a session.

For an EssbaseRange selector list, each cell value within the range is the name of Essbase member and represents an Essbase selector item. The RangeCellSelectorItemPolicy is not supported for an EssbaseRange selector list.

Selectors - Fixes

  • Fixed Issue - When the SelectorControlProperties.ItemsSorted is True for the selector list associated with a SelectorListBox, the items are sorted, but items containing numeric characters are not sorted in natural order. In 8.0, items are sorted as shown in the image on the right. #2663

    image

Selectors - Essbase Selector TreeView

  • ExpandSharedParent - A shared parent member is a shared member whose associated base member has children. In the Essbase selector treeview, a user is able to expand the node representing a shared member parent to view the descendants of the base member. The behavior is optional and is controlled by a new property, ExpandSharedParent, which has been added to the SelectorControlProperties for an EssbaseSelectorTreeView selector list. #2700

    The following caveats apply when ExpandSharedParent is True:

    When the shared parent member’s base member is not included in the branch(es) of the dimension represented in the treeview, a descendant of the shared parent member cannot be restored as a default selection when the DefaultSelectionPolicy is LastUsedItem.

    The Search capability cannot be used to find and select a descendant of a shared parent member.

Selectors - Last Used Items

  • In previous versions, the view SelectorLastUsedItemContext property supports the following options: None, ByView, BySelector, and ByLabel. The property controls the caching of the item(s) selected when the view is built or refreshed. The cache is used to determine the default selected item(s) for the selectors whose selector list has a DefaultSelectionPolicy of LastUsedItem. For example, if the SelectorLastUsedItemContext is ByView, the default selections reflect the last used items for the most recently built instance of the same view.

    In 8.0, an additional context, BySelectorList, supports the ability to default the selections to the most recently cached selections for a selector using the same selector list. #2758

    image

    As in previous versions, the SelectorLastUsedItemContext property can be overridden on a selector-by-selector basis in the SelectorConfiguration dialog with the Last Used Item Context setting.

  • In previous versions, when the selector list assigned to a selector has a DefaultSelectionPolicy of LastUsedItem and the selector list is used by selectors configured with both of the Selection Policy options, which include SingleItem and MultipleItems, the default selection is not retained for the selector when switching from a view with a Selection Policy of MultipleItems to a view with a Selection Policy of SingleItem.

    For example, if the Market selector is configured to set the default selection to the last used item(s), and is used by a view that only allows for a single Market member to be selected, and is also used by a view that allows for multiple Market members to be selected, the following behavior will be exhibited: If the view with the MultipleItems selection policy for the Market selector is built for Florida, New Hampshire, and New York, and then the view with the SingleItem selection policy is opened, there will be no default selection for the Market selector.

    In 8.0, to address this behavior, the cache of selected items is retained by a combination of the view or selector’s SelectorLastUsedItemContext and the selector’s Selection Policy. #2757

    The Cache Viewer, which is opened from the Admin menu, can be used to view the last used items. The highlighted cache item represents the last used items for the 4_Market selector. The cache item path or key is constructed based on the view’s SelectorLastUsedItemContext, which is BySelector, and the selector’s Selection Policy, which is MultipleItems.

    image

Selectors - Multiple Item Selection Enhancements

The SelectorListBox, EssbaseSelectorListBox, EssbaseSelectorTreeView, and SQLSelectorTreeView controls support the ability to allow multiple selected items. The user-interface has been improved to be more user-friendly and consistent with familiar and commonly used selection techniques. #1417, #1611

To highlight multiple items (or nodes):

  • Use of the Shift and Control keys

  • Use of lasso selection, which allows a dragging selection for multiple items. The drag operation is initiated by clicking the left (primary) mouse button on an item and then moving the cursor over other items while continuing to press the mouse button.

To check or uncheck highlighted items (or nodes):

  • Using the left mouse button, click the checkbox by one of the highlighted items, which toggles the check state for the item and also sets the check state of the other highlighted items accordingly.

  • Use the space bar to toggle the check state of the active item, which also sets the check state of the other highlighted items accordingly.

For example, in the screenshot below, the members were first highlighted by clicking WALDEN - CO, pressing the Shift Key, then clicking ARNOLD - KS. KS was excluded or un-highlighted by pressing the Control Key, and then clicking KS. Then, the checkbox by WALDEN - CO was clicked, which automatically checked (selected) all the highlighted members.

Alternatively, using the lasso selection technique to highlight multiple items, the mouse can be clicked on WALDEN - CO, then held down and dragged to over ARNOLD - KS, and then released. Then, any highlighted members can be un-highlighted using the Control Key technique.

image

When all the highlighted items are branch nodes, the various tools, including the Expand/Collapse Branch, Select/Clear Branch, and Select/Clear Children tools, can be performed for multiple branch nodes simultaneously.

Selectors - New SelectorTextBox Control

The SelectorTextBox is a new selector control type, which allows a user to key-in free-form text. It can be assigned to a Generic selector type. Unlike other selector controls, the concept of selector items being generated by a selector list and selected by the user is not applicable. The selector list associated with a selector textbox provides the ability to customize the behavior of the selector, such as how the default value is resolved as well as optional input validation. #1673

As an example use case, this SQL Excel view is configured with a selector textbox to allow a user to filter the rows retrieved by the relational query by keying in a SQL WHERE clause condition.

image

Or, by selecting from a list of previously used conditions.

image

The view has a generic selector, WhereCondition, which has a token named [T.WhereCondition].

image

And, the token is used in the WHERE clause of the query.

image

The default selector list WhereConditionInput defines the SelectorControlObjectType as a SelectorTextBox and the SelectorListObjectTypeID as Text.

image

The SelectorControlProperties include the following:

HasMRUList controls whether an MRU (most recently used) list of text strings is retained and displayed in the dropdown list. The MaxMRUItems indicates the maximum number of text strings that are retained.

TextBoxWidth controls the width of the textbox, but does not limit the number of characters that can be entered. The width is specified in pixels. Depending on the size of the individual characters, the default width of 200 will show approximately 28 characters.

ValidationRegExPattern allows the specification of a RegEx pattern, which is used to validate the input when the textbox is exited.

The format of the pattern is based on the .NET regular expression (Regex) syntax. Some examples include:

Letters and Spaces:

^[a-zA-Z ]+$

Letters, Numbers and Spaces:

^[a-zA-Z0-9 ]+$

Letters, Numbers, Underscore, Dash, and Spaces:

^[a-zA-Z0-9_- ]+$

For Unicode, any word character, including Unicode Letters, Numbers, Underscore, and Spaces:

^[\w ]+$

When the ValidationRegExPattern is specified, the ValidationMessage is displayed to the user when the validation fails.

In 8.0, a search capability has been added to the SelectorListBox and EssbaseSelectorListBox controls. #1964

image

The display of the search tool is optional, and is controlled by the ShowSearchTool setting for a selector list’s SelectorControlProperties. By default, the search tool is not displayed.

image

The following are some specifics about the search behavior:

  • The search tool is activated either by clicking with the mouse or by using the Ctrl+F shortcut keys. The selector control must be active for Ctrl+F to activate the tool.

  • The specified string is applied as each character is keyed in and is based on whether the Starts with or Contains option is selected.

    image
  • When the search tool is active, pressing the Enter key exits the tool and saves the specified text to a list of most recently used search strings, which is available in a dropdown list. The list contains a maximum of the 10 most recently used strings, and is persisted across sessions.

    image
  • When the search tool is active, pressing the Escape key clears the search string and exits the search tool.

  • If the selector is configured to allow multiple selections, the Select All and Clear All buttons select or clear only the items shown (i.e. the filtered items.)

  • For the EssbaseSelectorListBox, after a search is applied, toggling the Show Names / Show Aliases option does not clear the results, which allows for filtered member names to be viewed as aliases and vice versa. For example, after searching for cola with the Show Aliases option enabled, Show Names was then enabled.

    image
  • When the Alias table is changed, the items are refreshed and the search is reapplied.

Sign On and Set Active Essbase Connection Tool

  • The Sign On and Set Active Essbase Connection tool, which is typically used with an Essbase Adhoc view, has been modified to automatically attempt to sign on when the Sign On control is opened and credentials already exist for the view’s login service. If successful, the hierarchy of server(s), applications, and databases is displayed. This prevents the user from having to specify the credentials again. #2494

    An Essbase view’s login service is identified by the view’s EssbaseLoginServiceObjectTypeID property. The first time a login service is used during a session, the credentials are retained. When the login service is subsequently used, such as by another view for a different connection, the same credentials are used to attempt to connect.

    Similarly, in 8.0, when a view with the same EssbaseLoginServiceObjectTypeID is opened before the view that uses the Sign On tool, the credentials are available and used to automatically sign on.

SQL Passthrough DataSets

  • Database Transactions - The ability to execute a query’s insert, update, and delete statements in a transaction is supported in 8.0. The use of transactions is controlled by the query’s UseTransactions property. The property is set in the Query Editor, which is accessed from the SQL Passthrough DataSet Metadata Editor. #2436

    image
  • The Query ConnectString property, which was used in pre-5.0 Dodeca versions to specify connection information using Hibernate syntax, was deprecated in Dodeca 5.0, when the SQLConnectionID property was introduced. Support for the ConnectString property was eventually phased out, but continued to be presented in the Query Editor. In 8.0, the property is no longer presented in the client. #2725

SQL Passthrough DataSets Metadata Editor

  • Fixed Issue: When a SQL Passthrough DataSet properties are Categorized, each category can be collapsed or expanded. The categories include Information and Misc. When the Query Editor is opened, and a Query is selected, the expanded/collapsed state of the Misc category in the SQL Passthrough DataSets Metadata Editor, if different, is changed to the expanded/collapsed state of the Misc category in the Query Editor when the Query Editor is closed. #2658

Tool (and Other) Images

The images (or icons) used for tools and for the various user-interface components within the client have been updated to present a clearer and overall more consistent and current look. #1989

The following components reflect the changes to the images:

  • Built-in toolbars, including the Essbase View Template Designer, Excel View Template Designer, PDF View, and Outline Viewer View.

    Refer to the View Template Designer section for screenshots of the new tool images used in the View Template Designer ribbons.

  • View status icons, which appear on the view tabs and in the status bar:

    image
  • Metadata Editor status icons, which indicate whether a metadata instance is new, edited, or deleted:

    image
  • Shared view icons, which are displayed in the View Selectors to indicate whether a shared view is shared with noone, everyone, or specific people:

    image
  • Various other dialogs, such as the Workbook Script Debugger:

    image
  • Starter Kit toolbars configurations, such as the following:

    image

How to Update Toolbars Configurations to Use the New Images

Existing toolbars configurations will not automatically use the updated images.

Existing toolbars configurations can be updated to replace the standard tool images with the new images through the Toolbars Configuration Editor. The update can be applied to either a specific toolbars configuration or all toolbars configurations in the tenant by using the Update Images or Update All Images tool. Any images assigned to custom tools, which do not use the same key as a standard tool, are retained.

image

Users Manager

To allow for being able to see the Tenant, Application, and User ID values when the user scrolls to the right, the Users Manager dialog has been revised to freeze the first three columns, so these columns remain fixed when scrolling. #2369

View Hierarchy Metadata Editor

  • Fixed Issue: When the last leaf node in the hierarchy is not a child of a root node, the Find command fails to locate a node that appears in the tree above the currently selected node. #2526

  • Fixed Issue: When there is only a single root node, the Find command fails to locate a node that appears in the tree above the currently selected node. #2526

View Metadata Editor

  • Automatic Refresh: A view metadata instance can be changed and saved from the View Template Designer by assigning a different version of the template to the view or (in 8.0) by using the Comment Range Editor to edit the comment ranges. If the View Metadata Editor is open when a change is committed from the designer, and the View Metadata Editor is subsequently activated by the user and the modified view is the selected view, the properties are automatically refreshed to reflect the saved changes. #2586

  • In Version 7.5.0.6430, support was added for the ability to select a defined range name from a dropdown list in the DataTable Range Editor for the DataSheetRangeName and DataBlockSheetRangeName properties and in the RowSortAndGroupByInfo Editor for the GroupTemplateSheetRangeName property. #2662

    In 8.0, this capability is supported for all the other properties that represent a defined range name, which include the following:

    CommentRange Editor: Address, KeyItemsColumnRange, `KeyItemsRowRange, which display both workbook and sheet-level defined range names.

    CascadeSheetConfig Editor (as well as the equivalent View properties): SummarySheetSummaryRangeAddress, SummarySheetIncludeSheetAddress, and SummarySheetIsSummarySheetAddress - The property values can be specified as either a range reference or a defined range name, and a named range can be either sheet-level or workbook-level, which are displayed in the dropdown list.

    DataTable Range Editor: GroupStartCell - This property was overlooked when the capability was implemented in Version 7.5.0.6430. This property value can be either a cell address or a defined range name. The values displayed in the dropdown list include only sheet-level defined range names.

    AutoComplete Range Editor: SheetRangeName and RangeSourceRangeName - The property values are a workbook or sheet-level defined range name.

  • Fixed Issue: When the view properties are Categorized, each category can be collapsed or expanded. For a view that supports a CommentRanges property, one of categories is Behavior. When the Comment Range Editor is opened, and a Comment Range is selected, the expanded/collapsed state of the Behavior category in the View Metadata Editor, if different, is changed to the expanded/collapsed state of the Behavior category in the Comment Range Editor when the Comment Range Editor is closed.

    The same issue occurs with the DataSetRanges Editor when the view supports a DataSetRanges property. #2658

  • Fixed Issue: When multiple views have very similar ID’s, which contain a percent sign (%), such as Ops Costing as a % of Sales and Ops Costing as % of Sales, attempting to select Ops Costing as % of Sales in the View Metadata Editor results in the following error: #2454

    Unable to get the metadata instance from the encoded binary artifact. Unable to get metadata details. Multiple binary artifacts exist for "SAMPLE.VIEW.Ops Costing as % of Sales.1"

View Selectors

  • In previous versions, for both the ViewSelectorExplorerBar and ViewSelectorTree, the Edit Excel Template option is displayed on the context menu when the ViewSelectorProperties.AllowEditViewMetadata property is set to True. The option opens the Binary Artifacts Editor and sets the Excel template’s binary artifact instance as the active instance. To decouple this option from the AllowEditViewMetadata property, the AllowOpenExcelBinaryArtifactEditor property has been introduced, which now controls whether the Edit Excel Template option is displayed on the context menu. #2481

  • When a view selector’s AllowEditViewMetadata property is set to True, an Edit toolbars configuration item is added to the context menu to allow the user to navigate from a view in the view selector to the view’s associated toolbars configuration, as defined by the ViewToolbarsConfigurationID property, in the Toolbars Configuration Editor. #2525

  • A new setting, AllowOpenViewHierarchyEditor has been added to the ViewSelectorExplorerBar and ViewSelectorTree to allow an administrator to navigate from a view in the view selector to the View Hierarchy Metadata Editor and, when possible, to the corresponding view item in the appropriate hierarchy. #2482

  • In previous versions, when the Find dialog, which is used in the ViewSelectorTree, is initially opened, the Match case and Match entire name options are checked. In 8.0, the options are not checked. In all versions, the checked status of Search for and Options are cached and then restored when the dialog is subsequently opened within the same session or another session. #2822

View Template Designer

  • Cut, Copy, Paste, and Format Cells options have been added to the context menu in the View Template Designer. #2762

    image

  • Fixed Issue: Under certain conditions, in the View Template Designer for an Essbase Excel view, the Undo and Redo Ad hoc Operation tools do not become enabled when an Essbase operation is performed and the Undo/Redo Enabled option is checked. This occurs when 1) the view’s EssbaseConnectionID is set, 2) the active sheet does not contain named retrieve ranges with associated named connections, and 3) the Sign On and Set Active Connection control is not used to connect explicitly. #2429

  • The View Template Designer toolbars have been replaced with a ribbon. The Home tab is displayed as the active tab when the designer is opened. #2466

    View Template Designer for Essbase Views

    image

    View Template Designer for Excel and SQL Excel Views

    image

  • When the view template is saved, a confirmation is displayed in the status bar. #1506

    Saved the current version of the view template:

    image

    Saved the view template as a new version:

    image

  • The Double-Clicking and Secondary Button mouse tools have been added to the Essbase View Template Designer ribbon. The tools function similarly to the equivalent tools in the Dodeca Excel Add-In for Essbase, and allow the user to control whether mouse actions, such as double-clicking, perform an Essbase operation instead of the default Excel behavior. #1491

    image

    When the Double-Clicking tool is active, to initiate a retrieve operation, double-click the left mouse button on a non-member cell. For a zoom-in operation, to zoom down, double-click the left mouse button on a member cell. To zoom across, also press the Shift or Alt key when double-clicking. To zoom-out, double-click the right mouse button on a member cell. The Secondary Button tool must also be active when using the right mouse button.

    When the Secondary Button tool is active, to pivot, activate the member cell, then press the right mouse button and drag the member to the new location. To zoom-out, double-click the right mouse button on a member cell. The Double-Clicking tool must also be active when double-clicking the right mouse button.

Workbook Analyzer

The workbooks used by customers as view templates in Dodeca are often spreadsheets which have been used in legacy systems, and evolved over time, sometimes many years, and consequently contain artifacts from various sources. It is all too common for these workbooks to have a myriad of defects including invalid references, orphan links to external references, thousands of custom styles originating from a past Excel macro virus, and space-consuming custom properties inserted by SmartView. The result is a bloated file, sometimes megabytes in size, which can significantly degrade performance and undermine reliability. And, all too often, these problems are far more pervasive and extensive than companies realize. #2438, #2477

To assist customers with identifying and eliminating these workbook defects, a new utility, referred to as the Workbook Analyzer, has been developed for use in the Dodeca client. The utility analyzes a workbook (or all workbooks in a folder) and reports the findings.

In addition, the workbook can optionally be cleaned of any detected defects, including broken (or all) external references, defined names containing an invalid reference (#REF! error), custom properties, and custom styles. And, using a syntax similar to a SQL WHERE clause, a filter can be specified to identify defined names to be removed.

Although not able to include full screenshots of the analysis results for actual customer workbooks, which would provide more realistic examples, the following shows the type and layout of information included in the report.

Various sheet counts, including all sheets, all worksheets, visible, hidden, and very hidden worksheets as well as the maximum, minimum, and average cells counts per sheet are included.

image

The number of custom styles and the number of links, or external references, are included as well as a list of the links, which indicates the path of the reference, and linked cells, if appropriate.

image

The counts of defined names, visible and hidden names, and visible and hidden names with invalid references are included, along with a list of defined names. For each name, the visibility, scope, and reference are provided.

image

The Workbook Analyzer functionality can be accessed from the View Template Designer and the Admin menu. The View Template Designer provides a highly interactive environment that allows navigating between the analysis report and the template workbook as well as the cleaned version(s) of the workbook. The cleaned workbook can be saved as the view template or as a new version of the view template.

The Workbook Analyzer utility, which is accessed from the Admin menu, provides the ability to run the analysis and cleaning for a selected workbook from the file system or for all the workbooks in a selected folder. The analysis report can then be viewed in Excel.

Using the Workbook Analyzer in the View Template Designer

The Workbook Analyzer dialog is opened from the File tab in the View Template Designer ribbon.

image

The active workbook is analyzed, and the dialog presents the analysis report. The dialog can remain open while the user interacts with the rest of the user-interface in the client, which allows the ability to examine the workbook and use other tools, such as the Names Manager, in the View Template Designer.

image

The Open in Excel tool opens the report in Excel, which can then be saved to the file system as needed.

At this point, the Clean tool can be used on the analyzed workbook. Several options are supported for controlling the actions performed when the workbook is cleaned.

  • All links can be removed or only orphaned (unused) links.

  • All custom properties can be removed. Custom properties are inserted by SmartView to store information, including retrieved data, in the workbook. If the workbook was previously used in SmartView, these properties likely exist and inflated the the size of the original workbook.

    For more information regarding the use of custom properties by SmartView, refer to this blog post by Tim Tow: https://timtows-hyperion-blog.blogspot.com/2013/12/smart-view-internals-exploring-plumbing.html

  • All custom styles can be removed. A workbook may have been the target of an Excel macro virus years ago, which generated potentially thousands of custom styles. If the analysis report indicates an unreasonably high number of custom styles, the workbook was likely a target of the macro.

  • All defined names containing an invalid reference (#REF! error) can be removed.

  • The Remove from where option represents a text string used to filter and select the names to be removed based on criteria related to the Name, Visible, Scope, and RefersTo values listed in the Range Names section of the analysis report. The syntax of the string is similar to a SQL WHERE clause, and is documented in the Syntax Help task pane.

    Pressing the F1 key or clicking the question mark to the right of the field opens the Syntax Help task pane, which is docked along the right side of the dialog.

    image

    Syntax Help

    The syntax for the Remove names where criteria is similar to SQL. The specified criteria is used to select which defined names are to be removed.

    Column Names

    Name, Visible, Scope, and RefersTo are the valid column names. Visible values include True and False. Scope values include 'Workbook' or a specific sheet name.

    Literals

    String values are enclosed within single quotes ' ', and must be used when specifying a value for the Name, Scope, or RefersTo column.

    If the string contains single quote ', the quote must be escaped by another single quote. For instance, when a sheet name contains a single quote and is used as a Scope value.

    Comparison Operators

    The Equal "=" and Not equal "<>" operators are used to include values based on a comparison expression.

    The IN and NOT IN operators are used to include only the values that match or do not match an item in a comma-delimited list.

    For example, scope IN ('Sheet1', 'Sheet4') includes sheet-level names for Sheet1 and Sheet4.

    The LIKE and NOT LIKE operators are used to include only names that match or do not match a pattern with wildcards. The wildcard character is * or %, and can be at the beginning of a pattern '*value', at the end 'value*', or at both '*value*'. A wildcard cannot be used in the middle 'va*lue'.

    For example, Name LIKE '*account*' includes names that contain the word account.

    Boolean Operators

    The AND, OR and NOT operators are used to concatenate expressions. NOT as precedence over the AND, and AND has precedence over OR. Parentheses can be used to control the precedence.

    For example. Name LIKE 'abc*' AND (scope='Workbook' OR scope='Sheet1')_ includes names that start with abc_ and are either workbook-level or sheet-level for Sheet1.

Caution: Removing hidden names by specifying visible=false, for example, should be done with discretion. and after examining the hidden names in the analysis report or the Name Manager in the View Template Designer. Some hidden names, such as those starting with _xL and others which are not as easily identifiable, may have been created by Excel, and removing them can potentially break or corrupt the workbook.

After the cleaning is complete, the cleaned version of the workbook can be opened in the View Template Designer by clicking Yes when prompted, or by using the Open Cleaned Workbook tool.

image

When opened, the workbook becomes the active workbook in the View Template Designer. The Active Workbook tool can be used to activate the original workbook, or any open workbook, as needed.

image

Another tab is also added to the Workbook Analyzer dialog.

image

The Cleaned Workbook Information tab contains the same type of information as the Workbook Information tab, but also includes an indication of what was removed.

For example, this workbook was the target of the Excel macro virus, and over 33,000 custom styles were removed, along with 144 links.

image

In the View Template Designer, a workbook can also be opened from the file system. When opened, it becomes the active workbook, which can then be analyzed, cleaned, saved to the file system, and/or saved as the view template.

Using the Workbook Analyzer from the Admin Menu

The Workbook Analyzer utility is opened from the Admin menu.

image

From the dialog, a selected workbook from the file system or multiple workbooks in a selected folder can be analyzed and cleaned. The analysis results report and the cleaned workbooks are written to the specified locations. The analysis report can automatically be opened in Excel or opened from the file system.

image

The options for cleaning are the same as described previously, except for the addition of the following options: Remove empty trailing rows and Remove empty trailing columns

Empty trailing rows are the rows of a worksheet’s used range that follow the last row of the worksheet that has a value in at least one cell. Empty trailing columns are the columns of a worksheet’s used range that follow the last column of the worksheet that has a value in at least one cell.

The used range of a worksheet may include empty trailing rows and/or columns. This is not necessarily an incorrect state, but often these empty rows and columns are not deliberate and can cause confusion regarding the used range. They will also increase the size of the file unnecessarily, sometimes very significantly. The typical reason that these trailing empty rows and columns are included in the used range is that they have had formats set on them. The formatting may be deliberate, perhaps for future input into those rows and columns or by some other design.

If the formatting of these empty trailing rows and columns is not deliberate, then using the Remove empty trailing rows and/or Remove empty trailing columns options can be selected to remove them when a workbook is cleaned.

Workbook Script Editor

  • Workbook Script Reference View - In 8.0, a view can be associated with a workbook script. The referenced view’s Excel template is then used to populate dropdown lists for the selection of sheet names and range names, as well as to show the workbook’s color palette in color pickers. The view’s properties are used to populate DataSetRangeName selection lists.

    The view is specified as the ViewID in the Definition properties. #2192

    image

    An example of a range selection list, which contains all the named ranges in the referenced Excel template:

    image

    And, for a DataSetRangeName argument:

    image
    When a View Wizard is used to create a view and to create the associated workbook script, the view is automatically assigned to the workbook script as the reference view. #2744
  • For all method arguments that represent an Excel color index, the dropdown list of index values presented in the editor has been changed to include both the index number as well as the color name represented by the index. #2459

    image

  • The Find/Replace functionality has been refined and the dialog has been replaced with a dockable control. #2175

    image

    The results can also be filtered:

    image

  • Automatic Refresh: A workbook script can be changed and saved from the Workbook Script Debugger. If the Workbook Script Editor is open when a change is committed from the debugger, and the Workbook Script Editor is subsequently activated by the user and the modified workbook script is selected, the editor is automatically refreshed to reflect the saved changes. #2586

  • Changing a method: In previous versions, once a method is selected from the dropdown list, the dropdown list is no longer available, and the method cannot be changed. If the wrong method was selected, the method must be deleted and a new method added, and then the intended method selected from the dropdown list.

    In 8.0, to allow for a method to be more easily changed, the method dropdown list remains after a method is selected. The default overload arguments are automatically presented, and the overload can be changed, as well as the method. Once the user clicks another row, the method dropdown list is no longer available, but the method can still be changed by selecting the Select a different method from the context menu, which restores the dropdown list. #2504

  • Missing Workbook Script Methods: On occasion, a customer has a requirement for workbook script functionality, such as a method, that is not supported in the framework. With the Dodeca extensibility model, we are able to make the functionality available by providing a module, which contains the implementation assembly and configuration files. The module can be imported as metadata. Once the module is imported into the tenant, the custom method is available for use. When the Dodeca framework is subsequently upgraded, the components must also be upgraded.

    In previous versions, if this step is overlooked, the custom method is not recognized by the Dodeca framework. When a workbook script, which contains the method, is displayed in the Workbook Script Editor, the method is not included and there is no indication that an unknown method was detected. And, when a view is opened that uses the workbook script, no error is displayed.

    In 8.0, an unrecognized method is highlighted in the Workbook Script Editor, and changes to a workbook script containing an unrecognized method cannot be saved. An attempt to open a view that references the workbook script will fail with an error message, which indicates the first unrecognized method that is detected in the workbook script. #2672

  • Expand All Procedures Tool - In previous versions, the tool used to Expand all procedures and all of their methods was only displayed in the toolbar when the Procedures grid had the focus. In 8.0, the tool is always displayed to allow for expanding the procedures and methods regardless of which grid has the focus. #2715

    image

  • Fixed Issue: In the Workbook Script Editor, if a column header is clicked in the Procedures control, and then another column header is clicked to sort the column, the following error occurs: grdProcedures_BeforeSortChange failed with the following error: Key not found, Parameter Name: key. #2462

  • Fixed Issue: In the Formula Editor dialog, the mouse scroll wheel does not work when attempting to scroll the dropdown list of Functions. #2043

  • Fixed Issue: When the Workbook Script Editor is in the mode where the active method’s arguments are displayed on the side, methods that have been disabled are not formatted to reflect the disabled status (i.e. grayed out.) #2542

  • Fixed Issue: When the Hide empty arguments button is used, checkbox arguments do not become hidden when they have previously been checked and then unchecked. And, if the active row represents an empty argument, the row stays active when hidden. #2698

  • In previous versions, method arguments could be sorted by clicking the sort dropdown arrow button located on the right side of the Argument header. Once sorted, which typically occurs by accident, the arguments cannot be restored to the default order. The dropdown arrow button has been removed. #2797

Workbook Scripting

  • A Workbook Script Set supports the ability to associate multiple workbook scripts, which are executed at run-time in a pre-defined order. By design, each workbook script contains a Workbook Script Set, which consists of itself as the Main workbook script, and to which other workbook scripts can be added.

    When the Main workbook script is assigned to a view, all of the workbook scripts in the Workbook Script Set become associated with the view. When multiple workbook scripts link a procedure with the same event, the procedures are executed in the same relative order as the workbook scripts within the Workbook Script Set. #1624

    To modify Workbook Script Set for the active workbook script in the Workbook Script Editor, click the Open […​] button to the right of the ScriptSet property in the Definition section.

    image

    In this simple example, three workbook scripts, WBS_A, WBS_B, and WBS_C are associated with the workbook script Main. Workbook scripts are easily added, removed, and ordered. Since Main defines the Workbook Script Set, it cannot be removed, but can be positioned within the set as appropriate.

    image
    A workbook script, which is included in another workbook script’s ScriptSet, cannot contain other workbook scripts. For instance, in the previous example, WBS_A’s ScriptSet cannot include other workbook scripts, since WBS_A is included in Main’s ScriptSet. The Workbook Script Editor does not detect the violation, ut an error will occur at run-time.
  • Workbook Script Method Exception Dialog - In previous versions, workbook script error messages were presented in a message box. To provide a better user-interface for identifying and resolving workbook script errors, the message box has been replaced with the Workbook Script Method Exception dialog, which presents the error message and related workbook script information in a more readable and understandable format. #2754

    image

    The Script ID link is used to navigate to the workbook script in the Workbook Script Editor. The method, presented in the dialog, is automatically selected and expanded.

    image
    When the application’s WorkbookScriptDebuggingEnabled property is True, the Script ID is an active link. When the property is False, the Script ID is an inactive label, which prevents end-users from opening the Workbook Script Editor and accessing the workbook script.
    image

    The WorkbookScriptDebuggingEnabled property, which is also available in previous versions, is used to control whether workbook script debugging is enabled for the application.

    If enabled at the application level, then the DebugMode setting of a given workbook script controls whether debugging is enabled for the workbook script. The WorkbookScriptDebuggingEnabled also controls whether the Workbook Script Method Exception dialog provides an active Script ID link.

    Before an application is migrated to a production environment, the WorkbookScriptDebuggingEnabled should be set to False.
  • Workbook Script Trace Logging - The workbook script trace logging capability has been improved and enhanced. In previous versions, trace logging was enabled for a workbook script through the DoTracing setting in the script definition. This setting has been removed, and trace logging is controlled instead through the Utilities menu in the Admin application main toolbar. #1598

    • Workbook script trace logging is enabled and disabled for the session by toggling the Do Workbook Script Trace Logging tool in the Utilities menu.

    • By default, trace logging is disabled when the application is started.

    • When enabled, trace logging is performed for any workbook script that is run.

    • The location of the WorkbookScriptTrace.log file is set with the Trace Logging Path tool, and the path is retained across sessions.

    • The log entries are cumulative across sessions. To clear the log, click the Delete Workbook Script Trace Log tool.

      image

      With trace logging enabled and after opening a view and doing any actions that execute procedures in a workbook script, the contents of the log can be examined by clicking the Open Workbook Script Trace Log tool, which opens the Workbook Trace Log viewer. The log file is a text file with tab-delimited entries, but is displayed in the viewer as a spreadsheet with outlining and auto-filtering enabled.

      Initial View:

      image

      Method Details:

      image

      Function Resolution Details:

      image

      The viewer also supports various commands to provide the ability to, for instance, open the workbook in Excel, print, adjust the scale, and format the cells. When the Workbook Script Trace Log viewer is closed, if any changes have been made, a prompt is displayed to allow the spreadsheet to be saved as an Excel file. The changes are only saved to the Excel file and do not affect the actual content of the log file.

      Ribbon Tabs:

      image
  • Workbook Script Deprecation - It is sometimes necessary for a workbook script element (including functions, event links, methods, method overloads, and method arguments) to be deprecated in order to discourage the use of the element. This is typically due to an issue or behavior related to the element, which cannot be fixed because of the potential impact on existing workbook scripts. To address the underlying issue, a new element is usually introduced, such as a new method argument, which corrects the flawed behavior. #2229

    A deprecated element will continue to be supported and will have no impact on the run-time behavior of workbook scripts which use the element.

    As an example, in 7.7.0.6870, the DoInsert argument was added to the BuildRangeFromScript method to address an issue with the existing Insert argument. The Insert argument was effectively deprecated, but any workbook script using the argument will continue to behave as before.

    In 8.0, the Workbook Script Editor visually distinguishes a deprecated element. or a deprecated method argument, the argument label is displayed in red, and the argument description contains information related to the reason for the deprecation and the recommended alternative.

    image

    This infrastructure was implemented in anticipation of additional deprecated elements, but to date, the BuildRangeFromScript Insert argument and the SetHidden method’s Worksheet overload are the only elements to have been deprecated.

    Going forward, when elements, which would typically appear in a dropdown list, such as a method, are deprecated, the Show/Hide deprecated items tool controls whether the elements are visible in the list.

    image
  • New Event Link: The AfterCascadeSummarySheetBuild event link can be used to execute a workbook script procedure after a summary sheet is built for a cascaded SQL or Essbase Excel view. The creation of a summary sheet for a cascaded view is controlled by the view’s SummarySheetAdd or CascadeSheetConfig properties.

    The event argument is CascadeSheetNames, which is a forward-slash "/" delimited list of the names of the sheets created by the cascading. #1791

  • New Event Links: In addition to the existing ClipboardPasting and ClipboardPasted, event links have been added for clipboard cutting and copying, including: ClipboardCopying, ClipboardCopied, ClipboardCutting, and ClipboardCut. The ClipboardCopying and ClipboardCutting events can be cancelled by using the CancelEvent method.

    The event argument is ClipboardAddress, which is the address of the copied or cut range. #2098

  • New Event Links: The BeforeDataSetRangeBuild and AfterDataSetRangeBuild event links be used with a SQL or Essbase Excel view, and occur before and after a data set range is built. The event properties include DataSetRangeName, DataSetID, and SheetName. #1581

  • New Event Links: The BeforeDataSetRangesSave and AfterDataSetRangesSave can be used with a SQL or Essbase Excel view, and occur before and after all the view’s data set ranges are saved. The BeforeDataSetRangesSave event can be cancelled using the CancelEvent method. #2402

  • New Event Link: The CommentExceptionOccurred event link allows a workbook script to handle a failure when comments are loading, saved, etc., which is primarily caused by a network connectivity issue. The event has an Exception event property. #2476

  • New Event Link: The BeforeAutoCompleteRangeListLoad can be used in conjunction with the AutoCompleteRange functionality to customize the list of cell values for the active cell within the context of other cell values. The event occurs before the list of cell values is generated or loaded from the list source. #2784

    The event can be cancelled, which allows the workbook script to detect any conditions that should prevent the user from editing the cell value. And, if so, the ShowMessageBox method can be used to notify the user.

    Refer to the Auto-Complete section for more information about auto-complete ranges and an example of using the BeforeAutoCompleteRangeListLoad event link.

Workbook Scripting Functions

  • New Function: The IsValidRange function returns True/False to indicate whether the specified value can be resolved to a valid range address. #2686

    Usage
    @IsValidRange(<NameOrAddress>)
    Parameters
    • NameOrAddress - A defined range name or an address to be evaluated.


  • New Function: The Evaluate function returns True/False to indicate whether the specified value can be resolved to a valid range address. #2686

    Usage
    @Evaluate(<Formula>)
    Parameters
    • Formula - A string containing a formula to be evaluated by Dodeca.


  • New Function: The TimeZoneInfo function returns the value of the specified property of the local timezone. #2277

    Usage
    @TimeZoneInfo(<PropertyName>, [<TimeFormat>])
    Parameters
    • PropertyName - The name of the time-zone property. The default is G.

      PropertyName Description

      S[tandardName]

      The standard name of the local time-zone.

      UtcOffsetH[ours]

      The UTC offset in hours for the local time-zone.

      UtcOffsetM[inutes]

      The UTC offset in minutes for the local time-zone.

      T[oUniversalTime]

      The local time converted to UTC.

      I[sDaylightSavingTime]

      Whether DaylightSavingTime is in effect at the current time.

      DaylightN[ame]

      The standard name for DaylightSavingTime in the local time-zone.

      DaylightS[tart]

      The start date/time of DaylightSavingTime in the local time-zone.

      DaylightE[nd]

      The end date/time of DaylightSavingTime in the local time-zone.

      DaylightD[elta]

      The adjustment in hours for DaylightSavingTime in the local time-zone.

    • TimeFormat - Optional. A custom format string or a standard format code.

      Examples of custom format strings, which are for en-US:

      TimeFormat Example Result

      h:mm:ss.ff

      2019-06-30T15:29:58.26 → 3:29:58.26 P

      d MMM yyyy

      2019-06-30T15:29:58.26 → 30 May 2019

      H:mm:ss.f

      2019-06-30T15:29:58.26 → 15:29:58.2

      dd MMM HH:mm:ss

      2019-06-30T15:29:58.26 → 30 May 15:29:58

      H:mm:ss.ffff

      2019-06-30T15:29:58.26 → 15:29:58.2650

      Standard Format Codes:

      Code Description Example

      d

      Short date pattern.

      2019-06-15T13:45:30 → 6/15/2019

      D

      Long date pattern.

      2019-06-15T13:45:30 → Monday, June 15, 2019

      f

      Full date/time pattern (short time).

      2019-06-15T13:45:30 → Monday, June 15, 2019 1:45 PM

      F

      Full date/time pattern (long time).

      2019-06-15T13:45:30 → Monday, June 15, 2019 1:45:30 PM

      g

      General date/time pattern (short time).

      2019-06-15T13:45:30 → 6/15/2019 1:45 PM

      G

      General date/time pattern (long time).

      2019-06-15T13:45:30 → 6/15/2019 1:45:30 PM

      M.m

      Month/day pattern.

      2019-06-15T13:45:30 → June 15

      O,o

      Round-trip date/time pattern.

      2019-06-15T13:45:30 → 2019-06-15T13:45:30.0000000-07:00 (Local)

      2019-06-15T13:45:30 → 2019-06-15T13:45:30.0000000Z (Utc)

      R,r

      RFC1123 pattern.

      2019-06-15T13:45:30 → Mon, 15 Jun 2019 20:45:30 GMT

      s

      Sortable date/time pattern.

      2019-06-15T13:45:30 → 2019-06-15T13:45:30 (Local)

      2019-06-15T13:45:30 → 2019-06-15T13:45:30 (Utc)

      t

      Short time pattern.

      2019-06-15T13:45:30 → 1:45 PM

      T

      Long time pattern.

      2019-06-15T13:45:30 → 1:45:30 PM

      u

      Universal sortable date/time pattern.

      2019-06-15T13:45:30 → 2019-06-15 13:45:30Z

      U

      Universal full date/time pattern.

      2019-06-15T13:45:30 → Monday, June 15, 2019 8:45:30 PM

      Y,y

      Year month pattern.

      2019-06-15T13:45:30 → June, 2019

  • Fixed Issue: Any function that has multiple string arguments fails with a Missing argument(s) error when the function is called with multiple strings arguments enclosed in double quotes. #2555

    For example, the following use of the substitute function should return Cola','Diet Cola','Caffeine Free Cola

@substitute("Cola;Diet Cola;Caffeine Free Cola", ";", "','")
  • Fixed Issue: For the following functions, when the Address argument value is qualified with a sheet name, such as Sheet1!$A$1, an Invalid range reference error occurs: CellFillColor, CellIsHidden, CellIsLocked, Checksum, ColumnWidth, CommentPropertyForCell, ExcelComment, HasExcelComment, IsDataCell, IsMemberCell, and any of the DataPoint and MemberCell functions. #2696

  • Fixed Issue: The following error occurs when the CommentPropertyForCell function is called and the CellAddress property is an invalid cell address: Object reference not set to an instance of an object. #2696

  • Fixed Issue: The help for the ReplaceTokens function did not include the Text argument. #2696

  • The @RangeContainsRange(<Range1>, <Range2>) function returns FALSE when either of the specified ranges does not exist. In previous versions, the function returned a blank. #2804

Workbook Scripting Methods

  • In 8.0, the CallWebService, OpenApplication, and Process methods are supported as Workbook Script Extensions instead of as built-in methods. Because there is a potential security risk associated with the use of these methods, the availability of the methods requires the explicit inclusion of the extensions either by importing as a module into each tenant, or by including as a component in the deployment. The module can be imported from the wbs_extensions.zip, which is installed in the DodecaFramework metadata folder. The component can be included when creating the deployment in the ClickOnce Prep Utility, which is the recommended approach. #2648

    This represents a BREAKING CHANGE for any workbook script that uses the CallWebService, OpenApplication, or Process method unless the Workbook Script Extensions are imported or deployed.
  • The AddDataCache.SqlScript overload, BuildRangeFromScript.SqlScript overload, and ExecuteSqlTransaction are no longer supported. #2566

    While this represents a potential BREAKING CHANGE, we are not aware of any customers using these overloads and method, which were previously replaced with similar, yet more enhanced functionality through the SQLPassthroughDataSet technology.
  • Fixed Issue with AddDataCache Method: When the EssbaseMembersPerGeneration overload is used to add member information to a data cache, member information from the level that matches the given number or name is returned instead. #2680

    This represents a potential BREAKING CHANGE; data caches produced by workbook script procedures that utilize the AddDataCache.EssbaseMembersPerGeneration overload will change. Please review any procedures that utilize this overload.
  • For the SetBorders, SetConditionalFormat, SetFill, and SetWorksheetOptions methods, a color RGB argument has been added for all existing arguments that accept an Excel color index, but not a specific color. For example, SetBorders has a TopColor argument, which specifies an Excel color index. A TopColorRGB argument has been added to allow for specifying the color as an RGB value instead of an Excel color index. #2460

  • A new argument has been added to the EssbaseSend method: The UseMemberNamesAndAliasesForRows argument controls whether members names and aliases are used for row dimensions. The argument allows for overriding the view’s UseMemberNameAndAliasesForRowDimensions setting, which is used when the argument value is not specified. The argument is primarily used when multiple send ranges exist, and require a different value for the setting. #2637

    image
  • In 8.0, a SQLDataCache overload is supported for the ForEach method, which is used in conjunction with a data cache created using the AddDataCache method’s SQLPassthrougDataSet overload. The SQLDataCache overload allows access to each column value of the current row from within the procedure called by the ForEach method. The value of a given column for the current row is available in a workbook script property, which has the same name as the column. #2449

    In the following example, the AddDataCache.SQLPassthroughDataSet overload is used to add a data cache containing the results of a relational query, which represents customer contact information:

    image

    The procedure that is executed by the ForEach method calls ShowMessageBox to display the current row’s values. The property values are used to create the caption and the message displayed in the message box.

    image

    In the first iteration of the ForEach loop, the property values represent the values for the columns in the first row in the data cache.

    image

    The SQLDataCache overload also supports an optional argument, PropertyNamePrefix. If specified, the value is prepended to each property name.

    image

    In the example workbook script, if the PropertyNamePrefix is set to C_, the ShowMessageBox.Message argument uses the prefixed property names:

    image

    The ForEach method DataCache overload has also been enhanced to allow access to all of the column values of the current row from within the procedure called by the ForEach method for a data cache that is created by any of the AddDataCache overloads.

    Prior to 8.0, only a single column value could be accessed. The PropertyName argument specifies the name of the property that is added and which contains the value of the column specified by the ColumnNumber argument. In 8.0, an additional property is added for each column in the row and the property name is appended with a period and the column number, starting with ".1".

    To illustrate the difference between the DataCache and SQLDataCache overloads with respect to the creation of the properties, the following example uses the same data cache, created by the AddDataCache.SQLPassthroughDataSet overload, which was used in the previous example.

    The ForEach.DataCache overload’s PropertyName argument value is ColumnName and the ColumnNumber value is 1. Since the data cache contains 8 columns, 9 properties are created: ColumnName, ColumnName.1, ColumnName.2…​ ColumnName.8.

    As in the previous example, the procedure called by the ForEach method shows a message box. The caption includes the customer’s first and last name, and the message shows the value of each column in the current row.

    image
    image
  • New Method: The CommentOperations method, Load overload, can be used to reload comments or to load the comments for a comment range, which has the LoadPolicy set to None. #1415

    Depending on the requirements, the scope of the load operation can be controlled by the SpecifySheetBy, SheetSpec, and Address method arguments, which allows for 1) loading all comment ranges on all sheets, 2) loading a specified comment range on all sheets, such as for a cascaded view, 3) loading all comment ranges on a specific sheet, 4) loading a specified comment range on a specific sheet, and 5) loading a specified comment range with no sheet specified.

  • New Method: The SetDataTableRangesModificationTracking method allows for a workbook script to control whether changes to cell values in existing rows in a data table sheet range are subsequently detected as unsaved changes when the view is rebuilt or closed.

    By default, when the SaveDataSetRanges tool is included in the toolbar of a SQL view or an Excel Essbase view, any changes to cell values in existing rows in a data table sheet range that are not saved will be detected as unsaved changes when the user attempts to rebuild or close the view. The user is then asked whether the changes should be saved.

    When a workbook script modifies the cell values in a data table sheet range, the SetDataTableRangesModificationTracking method can be used to temporarily disable modification tracking. This prevents the changes by the workbook script from being detected as unsaved changes. #2231

    A view may require the use of formulas in one or more columns within a data table sheet range. While this can be accomplished by using the CopyRange method along with the SetDataTableRangesModificationTracking in the AfterDataSetRangesBuild event, the preferred approach is to include the formulas in the data table sheet range in the view template. When the first row of a data table sheet range contains a formula in a given column, the formula is automatically copied to all cells in the column within the range during the build processing, and the resulting changes to the cell values are not subsequently detected as unsaved changes.
  • New Method: The SetEssbaseSendRangeModificationTracking is used to enable or disable modification tracking for Essbase send ranges by setting an Essbase view’s SendRangeModificationTrackingEnabled run-time property. #1591

    The property controls whether the Send tool detects changes to values within send ranges, which determines whether the tool prompts the user when unsaved changes are detected. When a workbook script needs to modify values within a send range, which should not be detected by the tool, this method can be used to temporarily disable modification tracking, and then enable after the edits are completed.

    Method Arguments
    • Enabled: The value to set an Essbase view’s SendRangeModificationTrackingEnabled run-time property to. If the value is blank, TRUE is assigned.

  • New Method: The Wait method pauses the execution of the current procedure for a specified number of seconds using the General overload or until a specified condition is met using the 'Until' overload. #2279

    Method Arguments - General Overload
    • Seconds: The number of seconds to wait. Specified as a decimal value with a minimum of .0001 seconds.

    Method Arguments - Until Overload
    • UntilCondition: A formula to evaluate after each interval to determine whether to end the wait.

    • IntervalSeconds: The number of seconds between evaluations of the UntilCondition. Specified as a decimal value with a minimum of 0.0001 seconds. If left blank, IntervalSeconds defaults to 1.

    • IntervalProcedure: A procedure to run for each interval before the evaluation of UntilCondition. For instance, this optional procedure might be used to set a property to be evaluated in the UntilCondition.

    • MaxIterations: Limits the number of times the UntilCondition is evaluated and the Wait is terminated. If left blank, MaxIterations defaults to 10.

    • MaxIterationsAction: Indicates what to do if the Wait is terminated because MaxIterations has been reached. If left blank, then ThrowException is used. Valid values include Nothing, DisplayMessage, and ThrowException.

    • MaxIterationsMessage: When MaxIterationsAction is DisplayMessage, specifies the message to display if MaxIterations is reached.

  • New Method Overload: The SetBorder method has a new LineStyleSG overload. The overload addresses a limitation with the support of Excel line styles by the SpreadsheetGear control, which can only render the Excel line styles None, Continuous, and Double. The Excel line styles Dash, DashDot, DashDotDot, Dot, and SlantDashDot are rendered as Continuous. All Excel line styles are maintained and displayed correctly when the workbook is viewed in Excel.

    The LineStyleSG overload provides the line styles None, Dotted, Thin, Medium, Thick, and Double, which render correctly in the SpreadsheetGear control and will look the same when the workbook is viewed in Excel.

    For the SetBorder method All overload, a LineStyleSG argument has been added. #2697

    This screenshot depicts the rendering of the Excel line styles and SpreadsheetGear line styles in the SpreadsheetGear control and when viewed in Excel.

    image
  • New Method Overloads: The CopyRange method has two new overloads. The ToClipboardAsImage and ToFileAsImage overloads copy a range to the clipboard or to the specified file as an image. #2228 +

    Method Arguments - ToClipboardAsImage Overload
    • CopyRange: The address of the range to be copied.

    • ScalePercent: Scale the image with a value between 10 and 400. If left blank, 100 is used. +

    Method Arguments - ToFileAsImage Overload
    • CopyRange: The address of the range to be copied.

    • ScalePercent: Scale the image with a value between 10 and 400. If left blank, 100 is used.

    • ImageFormat: The format of the image file. If left blank, Bmp is used. Valid values include: Bmp, Emf, Exif, Gif, Icon, Jpeg, Png, Tiff, Wmf.

    • ImageFilename: Specify the name of the image file.

    • Folder: Specify the full path to the folder to put the file in. If left blank, the Desktop folder is used.

    • IncrementFilename: Whether to add a subscript, such as (2), to the filename if the specified file already exists. If not set to TRUE, an existing file with the same name will be overwritten.

    • UseDialog: Whether to show the Save File dialog to allow the user to select the folder and filename.

    • DialogResultPropertyName: The name of the script property to hold the Save File dialog result.

    • FullFilePathPropertyName: The full path of the file that is saved.

  • New Method Argument: A ShowViewAsActiveWindow argument has been added to the OpenView method to support the ability to control whether the opened view’s window becomes the active window. #2613

    When the argument is set to False, the opened view’s window does activate for less than second or so (for an asynchronous view) or until the view builds (for a synchronous view) and then the current view’s window reactivates. This behavior is due to implementation constraints related to the use of the OpenView method for batch report generation.

  • New Method Arguments: Several arguments have been added to the SendEmail.SMTP method overload to support the ability to include a snapshot of a range or ranges from the view in the email. #2223

    New Method Arguments
    • SnapshotRange : The range or ranges (delimited by a comma) to snapshot.

    • SnapshotAttachOrEmbed : Indicates whether to attach the snapshot image(s) to the email message or embed them. If embed is used, the IsHtmlMessage argument is forced to TRUE.

    • SnapshotFilename : The name to use for the snapshot file. If not specified, "Snapshot" is used.

    • SnapshotImageFormat : The format of the image file. If not specified, Bmp is used. Supported formats include: Bmp, Gif, Jpeg, Png, Tiff

    • SnapshotInsertToken : If SnapshotAttachOrEmbed is Embed, the snapshot(s) is inserted into the message in place of the specified token. If not specified, the snapshot(s) is appended to the end of the message.

    • SnapshotScalePercent : Scale the snapshot image with a value between 10 and 400 percent. If not specified, 100 is used.

      This screenshot shows the content of an email with an embedded snapshot (along with additional attachments.)

      image
  • New Method Argument: A Cut argument has been added to the appropriate CopyRange method overloads to allow the method to perform a cut operation and automatically clear the copied cells. #2527

  • New Method Argument: A StatementMessagesSheetName argument has been added to the EssbaseRunMaxl method. The existing MessagesSheetName argument is the name of a worksheet, which is added to the current view and will contain the messages generated by the execution of MaxL script that are not related to any specific statement. The StatementMessagesSheetName argument is the name of a worksheet, which will contain the messages related to the execution of a single statement. If the script contains multiple statements, then a sequentially numbered sheet will be added for the messages generated by each statement. In previous versions, the messages related to a given statement were written to the sheet containing the statement results, which is specified by the ResultsSheetName argument. #2451

  • New Method Argument: A TabRatio argument has been added to the SetWorkbookOptions method. The argument value controls the ratio of the width of the worksheet tabs to the width of the horizontal scrollbar as a number between zero and one. For example, if set to .75, the width of the area for worksheet tabs is 75% and the width of the scroll bar area is 25% of the total width of the view window. By default, the value is .6. #2206

  • New Method Arguments: For the SetUDA.RemoveFromMember method overload, new arguments have been added to support the ability to control whether the removal of the specified UDA’s continues after an error is encountered on the server when a given UDA (in the delimited string of UDA’s) is not valid for the member. #2211

    • ContinueOnInvalidUDA: Indicates whether or not to continue removing the UDA’s if a non-existent UDA is included. If blank, the argument value defaults to False.

    • BadUdasPropertyName: (Optional) The name of a workbook script property, which will contain the list of UDA’s included in the delimited string that do not exist. The list is in the form of <member name>;<UDA><newline>.

  • New Method Argument: An OutputRangeName argument has been added to method overloads that import or copy a range. These include the following: CopyFromBinaryArtifact.Range, CopyRange.FromFile, BuildRangeFromScript.ImportRange, and BinaryArtifactOperations.ImportRange. #2348

  • New Method Argument: The DataCache functionality has been enhanced to allow for non-unique or duplicate key values. An AllowDuplicateValues argument has been added to all the AddDataCache method overloads. The argument controls whether the value of the first column in each row in the data cache must be unique. If not specified, the argument value defaults to False. #2343

  • New Method Argument: A ShareSelectorTokens argument has been added to the OpenView method. The argument controls whether the current view’s selector tokens are added as tokens to the opened view. When added, the tokens are used to set default selections, when applicable, and can be referenced in workbook scripts, SQLPassthroughDataSets, etc. as used by the opened view. #1752

  • Deprecated Method Argument (and New Method Argument): The EssbaseReport method IncludeAttributes has been deprecated, and a new argument, AttributePolicy, has been added. #2308

    The IncludeAttributes argument controls whether the server performs a retrieve on the results returned by the script in order to obtain attributes for the member and data cells, which are then returned with the results to the client. An issue exists with the implementation in that argument value is reversed in the request sent to the server, such that True is treated as False and vice versa.

    To avoid introducing a potential breaking change to existing workbook scripts, which may use and set the argument value based on the actual behavior, the IncludeAttributes argument has been deprecated. The new AttributePolicy argument can be used instead to control whether member and data cell attributes are returned with the results. The valid argument values are IncludeAttributes and NoAttributes. By default, when no value is specified (and the IncludeAttributes is not specified), attributes are returned.

    Attributes should not be included if the report script suppresses page and column headings using suppageheading and supcolheading.

    Refer to ScriptEssbase View for an example of excluding attributes when page and column headings are suppressed in the report script.

  • Revised Method Argument Description: The SQLPassthroughExecute method description was revised to avoid a potential issue when a statement contains a semi-colon and the StatementDelimiter is not specified as a non-semicolon character. The method description:

    Executes a delimited list of SQL statements within a single server request. Important: If the Statements argument contains a semicolon ";", then multiple statements should be delimited using a non-semicolon character, such as the pipe character "|", which should be specified as the StatementDelimiter argument. By default, the StatementDelimiter is a semicolon. #2428

  • Fixed Issue with SetHidden Method - With the Worksheet overload, the address of the selected cell is used instead of the specified Address argument as the basis for evaluating the Hidden argument. For instance, if A1 is the active or selected cell on the sheet and is empty, and the Address argument is C3 and contains TRUE, the sheet does not become hidden when the Hidden argument is =@ACell()=TRUE.

    To avoid potentially breaking existing workbook scripts by correcting the behavior, the Worksheet overload has been deprecated and a new overload OfWorksheet has been introduced, which correctly applies the specified Address argument. #2226

  • Fixed Issue with BinaryArtifactOperations Method - With the CreateFromFile overload, a .xlsx extension is added to the specified filename, which results in an error similar to the following: Could not find file c:\Users\Administrator\Documents\MyTextFile.txt.xlsx #2537

  • Fixed Issue with SetSelector Method: When a view’s Auto Build tool is turned On and the view’s workbook script uses the SelectorSelectionChange event to call SetSelector to set the selection for another selector, the following error occurs: Object reference not set to an instance of an object. #2442

  • Fixed Issue with SetWorksheetOptions Method: When the method is used to rename a worksheet in an Essbase Excel view, any Essbase-specific functionality that is dependent on the internal representation of the Essbase data on the sheet does not function as expected. #2778

  • Fixed Issue with AddDataCache Method: With the EssbaseMdxQuery overload, when members in the column axis contain invalid XML characters, an error similar to the following occurs: Unable to send the request. Name cannot begin with the '0' character, hexidecimal value 0x30, Line 1, position 790. #2240