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 (22.214.171.124), NetAdvantage 2020, Volume 2 (20.2.30), Aspose.Cells 19.5.0, Syncfusion Essential Studio 126.96.36.199, GdPicture.NET 188.8.131.52, 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 184.108.40.206 are supported and tested on Java 1.5. The Dodeca-Essbase service for all Essbase versions 220.127.116.11 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 18.104.22.16844 or higher.
The release notes for this version contain the following sections:
Default Administrator’s Toolbar - The application toolbars configuration used when an application’s
UseDefaultAdminToolbarsConfigurationproperty 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
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
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.
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.
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.
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.
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.|
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
The DodecaTeal_OfficeWhite_Style style library, which was made available in 22.214.171.12430,
has been updated to address an issue, which occurs when an application’s
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
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
The name of a given exported Excel file is the same as the
Filenameassociated 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
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
BeforeCascadeSheetsBuildevent. 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.
The token [T.City] is the used in the relational query, which retrieves the content for each cascade sheet.
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.
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.
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
DelimitedStringas 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.
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.
In the View Metadata Editor, the view’s
CascadeSourcesis 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.
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
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
CascadeSourcesproperty supports the ability to navigate to the MDX script in the Essbase Scripts Editor. #2734
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
CommentRangedefinition, 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,
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.
KeyItemsColumnRangeproperty is a named range, which represents a single column containing the key item formulas for each row in the comment range.
KeyItemsRowRangeproperty 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
KeyItemsStringproperty 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.
KeyItemsRowRangeproperties 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
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
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.
The Comment Range Editor Help, opened with the Help button, contains more detailed information, including instructions with an example view.
CommentsEnabledproperty, 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
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-loggingsetting should be set to true in the dodeca.properties file. #2647
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
The Dodeca Framework About dialog has been revised to display additional information related to the Tenant, Application, User, and client. #2682
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.
In 8.0, the following Essbase connection properties can be tokenized:
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
To allow for testing a tokenized Essbase connection in the Essbase Connections Editor,
TestTokens property defines tokens for an Essbase connection, which are used
by the Test Connection tool to replace the tokenized properties with the associated
In version 126.96.36.19993, 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
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 188.8.131.5293, 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
property as a part of data drill-through configuration.
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.
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.
The test tokens mimic drilling through on the cell representing Actual Diet Cola Sales in New York for January.
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.
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 184.108.40.20693, conversion rules can also be defined for the OpenViewForDataCellsToolController.
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 220.127.116.1121 Release Notes.
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 TopEssbase option is supported.
EssPropertiessetting is also supported, and as with other options, can be excluded from being presented in the Essbase Options dialog.
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
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
DoEssbasePerformanceLoggingproperty is not displayed for an Essbase connection in the Essbase Connections Editor. #2448
The Depends On and Depended On By tools have been added to the Essbase Scripts Editor. #2745
Dodeca has supported Essbase text measures (aka SmartLists) since version 18.104.22.1680 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
The dropdown lists are implemented using Excel data validation.
The display of text list names instead of the associated number is controlled by
EssProperties.UseSmartLists metadata property. Both the
EssProperties.UseSmartLists_ShowDropDownList properties must be set to True
for the dropdown lists to be available.
The settings are also available in the Essbase Options dialog.
In previous versions, when the value of the
SheetContextMenuIDproperty 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
RequestSheetContextMenuIDevent links. To be valid, the ID must be the key of a PopupMenuTool in the view’s toolbars configuration, i.e. ViewToolbarsConfigurationID. #2688
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.
When the cell is empty or no match is detected, all of the available list items are displayed in the dropdown list.
The auto-complete capability is supported for Excel, SQL Excel, and Essbase Excel views, and is configured in the metadata by the
AutoCompleteRangedefines 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.
ListSourceTypeindicates the source of the list of available values presented in the dropdown list: Undefined, SQLPassthroughDataSet, DelimitedString, MdxScript, and Range.
SQLPassthroughDataSet - The list is retrieved by a relational database query defined by the
SQLPassthroughDataSetID. If specified, the
DataTableNameis 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
ItemColumnNameis the name of the column in the data table that contains the cell values. If not specified, the first column is used. The
ItemColumnNamevalues can be tokenized. The
SelectSQLof the SQLPassthroughDataSet can also be tokenized.
When using a SQLPassthroughDataSet as the list source type, it is recommended to set the
SQLPassthroughDataSet.SessionCachingEnabledproperty 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
DelimitedStringproperty. The value can be tokenized.
MdxScript - The list is retrieved by an MDX query using the specified
MdxScriptIDvalue 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
RangeSourceSheetRangeNamein either the view’s workbook or the specified
RangeSourceSheetRangeNamevalue can be tokenized.
ListLoadPolicyindicates 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
ListSourceTypeis MdxScript and the
In the example SQL view, an
AutoCompleteRangeis used to define the values that can be entered as a Job Title, as shown in the screenshot above.
SheetRangeNameis AutoComplete.JobTitle.Range, which is a named range that references a column in the
DataSheetRangeNameas configured in the view’s
ComparisonTypecontrols whether the items displayed in the dropdown list represent the available items that StartsWith or Contains the entered text value. The
ListSortPolicyis Ascending, Descending, or None, which is the order returned by the source.
ListDropDownWidthcontrols 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
MaxListItemsDisplayedis 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.
ItemNotInListPolicycontrols 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.
ErrorMessageTextForLimitToListcan 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.
ConfirmMessageTextForAddToListcan 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.
Using an MDX Script with an AutoCompleteRange
ListSourceTypecan 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
For an Excel and SQL Excel view, the
EssbaseLoginServiceObjectTypeIDmust be specified, and the
AliasTablesettings default to False and Default, respectively. The value of the
EssbaseConnectionIDcan be tokenized.
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
EssbaseConnectionIDassigned to the view.
UseSpecifiedConnection - Uses the specified
UseRetrieveRangeConnection - Uses the Ess.Retrieve.Connection.<id> named constant associated with the retrieve range in the template, which contains the cell.
For an Essbase Excel view, the
NonEssbaseCellDoubleClickPolicyproperty 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.
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.
AutoCompleteRangeis defined for each column with the
ListSourceTypedefined as OnDemand.
So, for example, when the cell enters edit mode in a cell in the State / Province column, the
BeforeAutoCompleteRangeListLoadevent is raised, and the workbook script sets the [T.CountryRegion] to the value in the Country / Region cell in the same row.
The list is loaded from the results of the relational query defined by the
SQLPassthroughDataSetIDassociated with the StateProvince
AutoCompleteRange. The select SQL statement contains the [T.CountryRegion] token, which is replaced before the query is executed.
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.
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.
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.
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.
SetWorkbookOptionsworkbook 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
AutoCalculateEnabledproperty set to True, the computed values displayed in the status bar are inaccurate when any cells in the selected range contain the
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.
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 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.
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
EcryptSavedExcelFileproperty must be set to True to enable this behavior.
As with Excel, a lost or forgotten password cannot be recovered. #2581
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.
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.
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
EncryptSavedExcelFileproperty can be set to True. This property is supported for an Excel, SQLExcel, and all Essbase view types.
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.
When the View Template Options - Create an Excel File option is selected, the new Excel file can be encrypted by specifying a password.
Clicking the Help button to the right of the password field displays this message box:
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.
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:
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.
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
EncryptSavedExcelFileproperty 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.
If the active workbook is already encrypted, this message box is displayed:
Open Workbook tool: If the opened workbook is encrypted, the Excel Password dialog is displayed (again, assuming the password has not previously been used.)
When a view is exported to Excel and either the view template is encrypted or the
EncryptSavedExcelFileproperty is true, the password is required to import the view (assuming the same password has not already been used in the session.)
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.
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
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)
All files (*.*)
The same filter is also displayed by the Open File dialog used by the Open Workbook tool in the View Template Designer.
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
Fixed Issue: The workbook script methods,
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 Serveris 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
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.
A list of the 10 most recently used character strings is retained across sessions for each metadata editor.
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
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
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.
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
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.
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
AutoBuildOnOpensetting should be used. If the tool argument is set to True or False, the opened view’s
AutoBuildOnOpensetting 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:
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
OpenViewworkbook script method.
Introduced in version 22.214.171.12406, 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 126.96.36.19921, 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.|
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
AttributesPolicyproperty 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
AttributesPolicyproperty 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
AttributesPolicyproperty is set to NoAttributes.
Added support for the ability to navigate from a selector list in the Selector List Metadata Editor to the associated
SelectorIDin the Selector Metadata Editor. #1975
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
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
property identifies the Excel binary artifact.
RangeName, which can be tokenized, identifies the workbook or sheet-level
range from which the cell values are extracted.
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.
RangeCellSelectorItemPolicy property controls whether each cell or each pair
of cells within the range is treated as a selector item.
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
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.
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
is not supported for an EssbaseRange selector list.
Fixed Issue - When the
SelectorControlProperties.ItemsSortedis 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
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
SelectorControlPropertiesfor an EssbaseSelectorTreeView selector list. #2700
The following caveats apply when
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
The Search capability cannot be used to find and select a descendant of a shared parent member.
In previous versions, the view
SelectorLastUsedItemContextproperty 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
DefaultSelectionPolicyof LastUsedItem. For example, if the
SelectorLastUsedItemContextis 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
As in previous versions, the
SelectorLastUsedItemContextproperty can be overridden on a selector-by-selector basis in the
SelectorConfigurationdialog with the Last Used Item Context setting.
In previous versions, when the selector list assigned to a selector has a
DefaultSelectionPolicyof 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
SelectorLastUsedItemContextand 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.
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.
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.
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.
Or, by selecting from a list of previously used conditions.
The view has a generic selector, WhereCondition, which has a token named [T.WhereCondition].
And, the token is used in the WHERE clause of the query.
The default selector list WhereConditionInput defines the
as a SelectorTextBox and the
SelectorListObjectTypeID as Text.
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
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:
Letters, Numbers and Spaces:
Letters, Numbers, Underscore, Dash, and Spaces:
For Unicode, any word character, including Unicode Letters, Numbers, Underscore, and Spaces:
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
The display of the search tool is optional, and is controlled by the
setting for a selector list’s
SelectorControlProperties. By default, the search
tool is not displayed.
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.
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.
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.
When the Alias table is changed, the items are refreshed and the search is reapplied.
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
EssbaseLoginServiceObjectTypeIDproperty. 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
EssbaseLoginServiceObjectTypeIDis opened before the view that uses the Sign On tool, the credentials are available and used to automatically sign on.
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
UseTransactionsproperty. The property is set in the Query Editor, which is accessed from the SQL Passthrough DataSet Metadata Editor. #2436
ConnectStringproperty, which was used in pre-5.0 Dodeca versions to specify connection information using Hibernate syntax, was deprecated in Dodeca 5.0, when the
SQLConnectionIDproperty was introduced. Support for the
ConnectStringproperty 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
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
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:
Metadata Editor status icons, which indicate whether a metadata instance is new, edited, or deleted:
Shared view icons, which are displayed in the View Selectors to indicate whether a shared view is shared with noone, everyone, or specific people:
Various other dialogs, such as the Workbook Script Debugger:
Starter Kit toolbars configurations, such as the following:
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.
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
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
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 188.8.131.5230, support was added for the ability to select a defined range name from a dropdown list in the DataTable Range Editor for the
DataBlockSheetRangeNameproperties and in the RowSortAndGroupByInfo Editor for the
In 8.0, this capability is supported for all the other properties that represent a defined range name, which include the following:
KeyItemsColumnRange, `KeyItemsRowRange, which display both workbook and sheet-level defined range names.
CascadeSheetConfig Editor (as well as the equivalent View properties):
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 184.108.40.20630. 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:
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
CommentRangesproperty, 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
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"
In previous versions, for both the ViewSelectorExplorerBar and ViewSelectorTree, the Edit Excel Template option is displayed on the context menu when the
ViewSelectorProperties.AllowEditViewMetadataproperty 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
AllowOpenExcelBinaryArtifactEditorproperty has been introduced, which now controls whether the Edit Excel Template option is displayed on the context menu. #2481
When a view selector’s
AllowEditViewMetadataproperty 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
ViewToolbarsConfigurationIDproperty, in the Toolbars Configuration Editor. #2525
A new setting,
AllowOpenViewHierarchyEditorhas 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
Cut, Copy, Paste, and Format Cells options have been added to the context menu in the View Template Designer. #2762
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
EssbaseConnectionIDis 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
View Template Designer for Excel and SQL Excel Views
When the view template is saved, a confirmation is displayed in the status bar. #1506
Saved the current version of the view template:
Saved the view template as a new version:
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Another tab is also added to the Workbook Analyzer dialog.
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.
|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.
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.
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 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
ViewIDin the Definition properties. #2192
An example of a range selection list, which contains all the named ranges in the referenced Excel template:
And, for a DataSetRangeName argument:
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
The Find/Replace functionality has been refined and the dialog has been replaced with a dockable control. #2175
The results can also be filtered:
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
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
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
ScriptSetproperty in the Definition section.
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.
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
ScriptSetcannot 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
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.
When the application’s
WorkbookScriptDebuggingEnabledproperty 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.
WorkbookScriptDebuggingEnabledproperty, 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
DebugModesetting of a given workbook script controls whether debugging is enabled for the workbook script. The
WorkbookScriptDebuggingEnabledalso controls whether the Workbook Script Method Exception dialog provides an active Script ID link.
Before an application is migrated to a production environment, the
WorkbookScriptDebuggingEnabledshould 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.
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.
Function Resolution Details:
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.
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 220.127.116.1170, the DoInsert argument was added to the
BuildRangeFromScriptmethod 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.
This infrastructure was implemented in anticipation of additional deprecated elements, but to date, the
BuildRangeFromScriptInsert argument and the
Worksheetoverload 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.
New Event Link: The
AfterCascadeSummarySheetBuildevent 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
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
ClipboardPasted, event links have been added for clipboard cutting and copying, including:
ClipboardCuttingevents can be cancelled by using the
The event argument is ClipboardAddress, which is the address of the copied or cut range. #2098
New Event Links: The
AfterDataSetRangeBuildevent 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
AfterDataSetRangesSavecan be used with a SQL or Essbase Excel view, and occur before and after all the view’s data set ranges are saved. The
BeforeDataSetRangesSaveevent can be cancelled using the
New Event Link: The
CommentExceptionOccurredevent 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
BeforeAutoCompleteRangeListLoadcan be used in conjunction with the
AutoCompleteRangefunctionality 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
New Function: The
IsValidRangefunction returns True/False to indicate whether the specified value can be resolved to a valid range address. #2686
NameOrAddress - A defined range name or an address to be evaluated.
New Function: The
Evaluatefunction returns True/False to indicate whether the specified value can be resolved to a valid range address. #2686
Formula - A string containing a formula to be evaluated by Dodeca.
New Function: The
TimeZoneInfofunction returns the value of the specified property of the local timezone. #2277
PropertyName - The name of the time-zone property. The default is G.
The standard name of the local time-zone.
The UTC offset in hours for the local time-zone.
The UTC offset in minutes for the local time-zone.
The local time converted to UTC.
Whether DaylightSavingTime is in effect at the current time.
The standard name for DaylightSavingTime in the local time-zone.
The start date/time of DaylightSavingTime in the local time-zone.
The end date/time of DaylightSavingTime in the local time-zone.
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
2019-06-30T15:29:58.26 → 3:29:58.26 P
d MMM yyyy
2019-06-30T15:29:58.26 → 30 May 2019
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
2019-06-30T15:29:58.26 → 15:29:58.2650
Standard Format Codes:
Code Description Example
Short date pattern.
2019-06-15T13:45:30 → 6/15/2019
Long date pattern.
2019-06-15T13:45:30 → Monday, June 15, 2019
Full date/time pattern (short time).
2019-06-15T13:45:30 → Monday, June 15, 2019 1:45 PM
Full date/time pattern (long time).
2019-06-15T13:45:30 → Monday, June 15, 2019 1:45:30 PM
General date/time pattern (short time).
2019-06-15T13:45:30 → 6/15/2019 1:45 PM
General date/time pattern (long time).
2019-06-15T13:45:30 → 6/15/2019 1:45:30 PM
2019-06-15T13:45:30 → June 15
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)
2019-06-15T13:45:30 → Mon, 15 Jun 2019 20:45:30 GMT
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)
Short time pattern.
2019-06-15T13:45:30 → 1:45 PM
Long time pattern.
2019-06-15T13:45:30 → 1:45:30 PM
Universal sortable date/time pattern.
2019-06-15T13:45:30 → 2019-06-15 13:45:30Z
Universal full date/time pattern.
2019-06-15T13:45:30 → Monday, June 15, 2019 8:45:30 PM
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
substitutefunction 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:
IsMemberCell, and any of the
Fixed Issue: The following error occurs when the
CommentPropertyForCellfunction 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
ReplaceTokensfunction did not include the Text argument. #2696
@RangeContainsRange(<Range1>, <Range2>)function returns FALSE when either of the specified ranges does not exist. In previous versions, the function returned a blank. #2804
In 8.0, the
Processmethods 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
metadatafolder. 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
Processmethod unless the Workbook Script Extensions are imported or deployed.
ExecuteSqlTransactionare 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
AddDataCacheMethod: When the
EssbaseMembersPerGenerationoverload 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.
SetWorksheetOptionsmethods, a color RGB argument has been added for all existing arguments that accept an Excel color index, but not a specific color. For example,
SetBordershas 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
UseMemberNamesAndAliasesForRowsargument controls whether members names and aliases are used for row dimensions. The argument allows for overriding the view’s
UseMemberNameAndAliasesForRowDimensionssetting, 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
In 8.0, a
SQLDataCacheoverload is supported for the
ForEachmethod, which is used in conjunction with a data cache created using the
SQLDataCacheoverload allows access to each column value of the current row from within the procedure called by the
ForEachmethod. 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.SQLPassthroughDataSetoverload is used to add a data cache containing the results of a relational query, which represents customer contact information:
The procedure that is executed by the
ShowMessageBoxto display the current row’s values. The property values are used to create the caption and the message displayed in the message box.
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.
SQLDataCacheoverload also supports an optional argument,
PropertyNamePrefix. If specified, the value is prepended to each property name.
In the example workbook script, if the
PropertyNamePrefixis set to C_, the
ShowMessageBox.Messageargument uses the prefixed property names:
DataCacheoverload has also been enhanced to allow access to all of the column values of the current row from within the procedure called by the
ForEachmethod for a data cache that is created by any of the
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
SQLDataCacheoverloads with respect to the creation of the properties, the following example uses the same data cache, created by the
AddDataCache.SQLPassthroughDataSetoverload, which was used in the previous example.
ForEach.DataCacheoverload’s PropertyName argument value is ColumnName and the
ColumnNumbervalue 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
ForEachmethod 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.
New Method: The
Loadoverload, can be used to reload comments or to load the comments for a comment range, which has the
LoadPolicyset 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
SetDataTableRangesModificationTrackingmethod 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
SaveDataSetRangestool 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
SetDataTableRangesModificationTrackingmethod 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
CopyRangemethod along with the
AfterDataSetRangesBuildevent, 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
SetEssbaseSendRangeModificationTrackingis used to enable or disable modification tracking for Essbase send ranges by setting an Essbase view’s
SendRangeModificationTrackingEnabledrun-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
SendRangeModificationTrackingEnabledrun-time property to. If the value is blank, TRUE is assigned.
New Method: The
Waitmethod pauses the execution of the current procedure for a specified number of seconds using the
Generaloverload 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
SetBordermethod has a new
LineStyleSGoverload. 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.
LineStyleSGoverload 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.
LineStyleSGargument 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.
New Method Overloads: The
CopyRangemethod has two new overloads. The
ToFileAsImageoverloads 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
OpenViewmethod 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
OpenViewmethod for batch report generation.
New Method Arguments: Several arguments have been added to the
SendEmail.SMTPmethod 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.)
New Method Argument: A Cut argument has been added to the appropriate
CopyRangemethod 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
EssbaseRunMaxlmethod. 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
SetWorkbookOptionsmethod. 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.RemoveFromMembermethod 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:
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
AddDataCachemethod 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
OpenViewmethod. 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
EssbaseReportmethod 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
SQLPassthroughExecutemethod 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
SetHiddenMethod - With the
Worksheetoverload, 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
Worksheetoverload has been deprecated and a new overload
OfWorksheethas been introduced, which correctly applies the specified Address argument. #2226
Fixed Issue with
BinaryArtifactOperationsMethod - With the
CreateFromFileoverload, 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
SetSelectorMethod: When a view’s Auto Build tool is turned On and the view’s workbook script uses the
SelectorSelectionChangeevent to call
SetSelectorto set the selection for another selector, the following error occurs: Object reference not set to an instance of an object. #2442
Fixed Issue with
SetWorksheetOptionsMethod: 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
AddDataCacheMethod: With the
EssbaseMdxQueryoverload, 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