This version of the Dodeca uses the .NET Framework, version 4.7.2 and higher on the desktop. The components used in this version of Dodeca are SpreadsheetGear 2017 (8.6.1.102), NetAdvantage 2020, Volume 2 (20.2.30), Aspose.Cells 19.5.0, Syncfusion Essential Studio 17.3.0.26, GdPicture.NET 14.1.0.112, and Xceed Zip 3.6.7363.15420.
This version of Dodeca has two server-side services that run inside a Java Application Server. The Dodeca service is supported and tested on Java 1.8. The Dodeca-Essbase service for all Essbase versions prior to, and including, Essbase 11.1.1.3 are supported and tested on Java 1.5. The Dodeca-Essbase service for all Essbase versions 11.1.1.4 and higher is supported and tested on Java 1.6. Both services are known to run on Java 1.7, 1.8, 1.9, and 1.10.
This release, in general, does not contain enhancements or fixes introduced in version 7.8.8.7644 or higher.
The release notes for this version contain the following sections:
Default Administrator’s Toolbar - The application toolbars configuration used
when an application’s UseDefaultAdminToolbarsConfiguration
property is True
has been revised. In previous versions, the Admin menu contained the metadata
editors as well as various utilities. The Admin menu has been replaced with the
Metadata Editors and Admin Utilities menus. #2599
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 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.
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.
ID
. The use of these
characters results in an error and prevents the application from being run. #963The DodecaTeal_OfficeWhite_Style style library, which was made available in 7.5.0.6430,
has been updated to address an issue, which occurs when an application’s ViewSelectorProperties
sets the ImageBackColor
to a non-default value.
Additional refinements have also been made to the library that are specific to the Application - Floating View and Metadata Editor Windows capability.
To update the style library in a tenant, import the artifact from the style_library.zip, which is installed in the Dodeca Framework metadata folder. #2547
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 Filename
associated
with the binary artifact. When the folder already contains a file with the same
name as a binary artifact’s Excel file, the filename is appended with (x), where
x is a number, such as My Excel File(2).xlsx. This can occur when multiple
binary artifacts contain an Excel file with the same name.
When the All Versions option is enabled, the version number is appended to the filename, such as My Excel File, Version 2.xlsx.
This capability can be used in conjunction with the Workbook Analyzer utility available from the Admin menu, which supports the ability to analyze and optionally clean all workbooks in a folder. Refer to the Workbook Analyzer section for more information.
In 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 BeforeCascadeSheetsBuild
event. A tokenized delimited
string allows for a workbook script to set the token value dynamically before the
view is cascaded.
The following example illustrates the use of a single delimited string cascade source, which is tokenized. The SQL view allows the user to select the country/region and the state/province, and cascades the view on the cities which are associated with customer contact information in the database.
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 DelimitedString
as Member Only;Market_MemberOnly;Children;Market_Children;Level 0;Market_Level_0.
Each name/value pair represents the name of the item displayed in the selector
dropdown list, such as Children, and the value represents the ID of the associated
MDX script, such as Market_Children.
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 CascadeSources
is configured with the
Source Type set to EssbaseMdxScript and the Script ID set to [T.MarketCascadeLevel],
which is the token associated with the generic MarketCascadeLevel selector.
The token used for each member returned by the query is [T.MarketMdxMember],
which is used in the view’s template for token replacement on each cascade sheet
and also used as the CascadeSheetNamePattern
value.
At runtime, with East selected as the Market and Children selected as the Market Cascade Level, the Market_Children MDX script is executed, and a cascade sheet is created for each child member of East.
In the View Metadata Editor, when a view’s cascade Source Type is EssbaseMdxScript,
the context menu for the CascadeSources
property supports the ability to navigate
to the MDX script in the Essbase Scripts Editor. #2734
Server-side Performance Enhancement: As the result of optimized SQL query algorithms implemented in the Dodeca Server, the loading of comments is often significantly faster than with previous versions, and is most noticeable when a large number of comments is retrieved. #2371
In addition, for an Essbase view configured to execute asynchronously, the comments are loaded on a background thread, so while comments are being retrieved, the user is able to interact with the application.
Additional properties have been added to the CommentRange
definition, which
is used by Excel and ExcelEssbase views to configure the various settings that
control the location, behavior, and appearance of related commentary. The new
properties, KeyItemsColumnRange
and KeyItemsRowRange
, facilitate the use of
Excel formulas on the sheet to construct the key items string for each cell within
a comment range. The key items string is used to uniquely identify the comments
associated with a given cell. It is a concatenated string of semicolon delimited
key/item pairs, such as Measures=Sales;Market=New York;Product=Cola;Year=Jan;Scenario=Actual.
The KeyItemsColumnRange
property is a named range, which represents a single
column containing the key item formulas for each row in the comment range.
The KeyItemsRowRange
property is a named range, which represents a single row
containing the key item formulas for each column in the comment range.
In previous versions, the KeyItemsString
property is used to specify the key/item pairs,
and is still supported. Although the property value may contain formulas and also
leverage the use of formulas on the sheet to construct the key items string, the
approach for doing so is not always obvious, and if not optimized, can result in
additional overhead and negatively impact performance.
The KeyItemsColumnRange
and KeyItemsRowRange
properties are intended to not
only simplify the configuration of comments, but to also enforce the use of Excel
formulas on the sheet to construct the key items string, which yields the best
performance. #2371
For more information, refer to the following section which describes the Comment Range Editor.
The Comment Range Editor assists with the creation of the key items formulas
in the KeyItemsColumnRange
and KeyItemsRowRange
, as described in the previous
section, as well as with the configuration of other comment range settings. The
editor is opened from the Home tab in the View Template Designer. #2542
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.
The CommentsEnabled
property, which has been added to the Excel and Essbase Excel
view types, controls whether the comments are loaded when the view is built. As noted
in the property description, this setting is applied to all the comment ranges,
and is typically used for development/debugging purposes. #2625
Comment Audit Log - Comment audit logging is supported in 8.0 to provide the
ability to track comment changes, including inserts, updates, and deletes. By
default, comment audit logging is not enabled in a deployment. To enable, the
dodeca.comments.audit-logging
setting should be set to true in the dodeca.properties
file. #2647
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: ServletPath
,
APSUrl
, Server
, Application
, Database
, DataSource
. Views, selectors,
and workbook scripts can reference an Essbase connection that contains tokenized
properties. The tokens can be defined by the application and view token tables.
A connection with tokenized properties can also be used in the Essbase Scripts Editor
when testing a script as long as the test tokens include the appropriate tokens
for the tokenized properties. #2311
A tokenized Essbase Connection should not be used with Application/Security
or related functionality (such as HierarchyToRoleMapping
when an Essbase authentication
service is used.)
To allow for testing a tokenized Essbase connection in the Essbase Connections Editor,
the TestTokens
property defines tokens for an Essbase connection, which are used
by the Test Connection tool to replace the tokenized properties with the associated
token value.
In version 6.1.1.3193, which was released on November 10, 2011, the
DataDrillthroughMemberToTokenConversionRule
property was introduced for the
Essbase Excel view type in order to enhance the Intelligent Navigation capability. In
particular, the ability to drill-through from a data point in an Essbase Excel view
to another view, such as a relational view that presents the associated transactional
data within the context of the selected data point. The context, which consists
of the contributing member for each dimension that intersects at the data point,
is passed to the relational view by way of tokens.
With the initial implementation, the DataDrillthroughMemberToTokenConversionRule
property provided a way to derive the members passed to the target view based on
a relationship, such as children, descendents, bottom level members, etc., for
any given dimension. #1490
For a detailed description of the original implementation of the property, refer to Version 6.1.1.3193, November 10, 2011.
In 8.0, the functionality has been further enhanced to allow for the use of an MDX script to derive the members passed to the target view for any given dimension.
As described in the referenced section, the definition of the conversions rules
is supported by the Essbase Excel view’s DataDrillthroughMemberToTokenConversionRule
property as a part of data drill-through configuration.
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.
As described in the referenced section from Version 6.1.1.3193, 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 7.8.5.7321 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 Top
Essbase option is supported.
An AncestorOnTop
EssProperties
setting is also supported, and as with other
options, can be excluded from being presented in the Essbase Options dialog.
DoEssbasePerformanceLogging
property is not displayed for an Essbase connection
in the Essbase Connections Editor. #2448Dodeca has supported Essbase text measures (aka SmartLists) since version 5.0.0.2260 by optionally displaying the text list names instead of the related ID or number for data cells associated with a text list. In 8.0, the functionality has been enhanced to support the ability to optionally display a dropdown list containing the text list names, which allows a user to view and select from the valid values. #1958
The dropdown lists are implemented using Excel data validation.
The display of text list names instead of the associated number is controlled by
the EssProperties.UseSmartLists
metadata property. Both the EssProperties.UseSmartLists
and EssProperties.UseSmartLists_ShowDropDownList
properties must be set to True
for the dropdown lists to be available.
The settings are also available in the Essbase Options dialog.
When UseSmartLists_ShowDropDownList
is True, the connection for which the
SmartList values defined, must be configured with the ExtendedCubeInfoEnabled
property set to True; otherwise, no dropdown lists will be displayed.
GridContextMenuID
, GridContextMenuIDForDrillthroughSheet
,
or SheetContextMenuID
property of an Excel, SQL, or any Essbase view type is not
valid, no error message is displayed. The property value can be assigned either
in the view’s metadata or by a workbook script in the RequestContextMenuID
and
RequestSheetContextMenuID
event links. To be valid, the ID must be the key of
a PopupMenuTool in the view’s toolbars configuration, i.e. ViewToolbarsConfigurationID.
#2688Auto-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 AutoCompleteRanges
property.
An AutoCompleteRange
defines the range of cells for which the configuration
applies as well as the source from which (and when) the list of available values
is generated along with various aspects of the presentation and behavior.
The ListSourceType
indicates 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 DataTableName
is the name of the data table returned by the SQLPassthroughDataSet that contains
the cell values. If not specified, the first data table is used. The ItemColumnName
is the name of the column in the data table that contains the cell values. If not
specified, the first column is used. The SQLPassthroughDataSetID
, DataTableName
,
and ItemColumnName
values can be tokenized. The SelectSQL
of the SQLPassthroughDataSet
can also be tokenized.
When using a SQLPassthroughDataSet as the list source type, it is recommended
to set the SQLPassthroughDataSet.SessionCachingEnabled
property to True unless the
query is data-dependent or if the results are likely to change during a session.
The results of a tokenized query can be cached.
DelimitedString - A semi-colon delimited list of strings, which is defined
by the DelimitedString
property. The value can be tokenized.
MdxScript - The list is retrieved by an MDX query using the specified MdxScriptID
.
The MdxScriptID
value and the associated query can be tokenized.
The section, Using an MDX Script with an AutoCompleteRange, provides more details.
Range - Based on the RangeSourcePolicy
, which is either ViewWorkbook or
ExcelBinaryArtifact, the list is extracted from the RangeSourceSheetRangeName
in either the view’s workbook or the specified ExcelBinaryArtifact
. The
RangeSourceSheetRangeName
value can be tokenized.
The ListLoadPolicy
indicates when the list of values is generated:
OnViewOpen - The list is generated when the view opens. This is the policy to use when the list source is not tokenized and is not dependent on the context of the active cell.
OnViewBuild - The list is generated when the view builds. This is the policy to use when the list source contains tokens, such as selector tokens.
OnDemand - The list is generated when the user starts typing into a cell in
the SheetRangeAddress
. This is the policy to use when the list is dependent on the
context of the active cell. For example, in an Essbase Excel view, when the ListSourceType
is MdxScript and the MdxScriptConnectionPolicy
is UseRetrieveRangeConnection.
In the example SQL view, an AutoCompleteRange
is used to define the values that
can be entered as a Job Title, as shown in the screenshot above.
The SheetRangeName
is AutoComplete.JobTitle.Range, which is a named range that
references a column in the DataSheetRangeName
as configured in the view’s
DataSetRanges
.
The ComparisonType
controls whether the items displayed in the dropdown list
represent the available items that StartsWith or Contains the entered text value.
The ListSortPolicy
is Ascending, Descending, or None, which is the order
returned by the source.
The ListDropDownWidth
controls whether the width of the dropdown list is the
ColumnWidth or the computed AutoSize width, which is the width of the column
or the longest item, whichever is greater. The MaxListItemsDisplayed
is the
maximum number of items displayed in the visible portion of the dropdown list.
When the total number of matching cell values exceeds the maximum, a vertical
scrollbar is displayed.
The ItemNotInListPolicy
controls whether a cell value can only be set to a value
in the list of available values. With LimitToList, a cell value is limited to
the values in the list. With AddToList, a cell value can be set to a value
that is not in the list, and the value is added to the list base on the AddToListPolicy
.
With Ignore, a cell value can be set to a value that is not in the list, and no
error or confirmation is displayed.
The ErrorMessageCaptionForLimitToList
and ErrorMessageTextForLimitToList
can
be used to customize and override the default caption and text of the error displayed
when the value is limited to the list and the user enters a value that is not
in the list.
The ConfirmMessageCaptionForAddToList
and ConfirmMessageTextForAddToList
can
be used to customize and override the default caption and text of the confirmation
displayed when the value is not limited to the list and the user enters a value
that is not in the list.
Using an MDX Script with an AutoCompleteRange
The ListSourceType
can be set to MdxScript for any of the view types that
support the auto-complete functionality. This allows for an Excel or SQL Excel
view to present member names or aliases as the available cell values within an
AutoCompleteRange
.
For an Excel and SQL Excel view, the EssbaseConnectionID
and EssbaseLoginServiceObjectTypeID
must be specified, and the UseAliases
and AliasTable
settings default to False
and Default, respectively. The value of the EssbaseConnectionID
can be tokenized.
For an Essbase Excel view, the Essbase connection used by the MDX script is controlled
by the MdxScriptConnectionPolicy
, which includes the following options:
UseViewConnection - Uses the EssbaseConnectionID
assigned to the view.
UseSpecifiedConnection - Uses the specified EssbaseConnectionID
.
UseRetrieveRangeConnection - Uses the Ess.Retrieve.Connection.<id> named constant associated with the retrieve range in the template, which contains the cell.
For an Essbase Excel view, the NonEssbaseCellDoubleClickPolicy
property
must be set to EnterEditMode to allow for the auto-complete functionality to be
activated when edit mode is entered by double-clicking a cell in the range.
Workbook Script BeforeAutoCompleteRangeListLoad
Event Link
The workbook script event link, BeforeAutoCompleteRangeListLoad
, allows a workbook
script to customize the list of cell values for the active cell within the context
of other cell values. The event occurs before the list of cell values is generated
or loaded from the list source. #2784
To demonstrate the use of the event link, the following example implements a use case, which is conceptually similar to dependent selectors. Within a given row, the first column represents a country or region, the next column represents a state or province within the selected country or region, and the last column represents a city in the selected state or province.
An AutoCompleteRange
is defined for each column with the ListSourceType
defined as
OnDemand.
So, for example, when the cell enters edit mode in a cell in the State / Province
column, the BeforeAutoCompleteRangeListLoad
event is raised, and the workbook
script sets the [T.CountryRegion] to the value in the Country / Region cell
in the same row.
The list is loaded from the results of the relational query defined by the
SQLPassthroughDataSetID
associated with the StateProvince AutoCompleteRange
.
The select SQL statement contains the [T.CountryRegion] token, which is replaced
before the query is executed.
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.
The SetWorkbookOptions
workbook script method also supports a TabRatio argument
AutoCalculateEnabled
property set to True, the computed values displayed in the
status bar are inaccurate when any cells in the selected range contain the SUBTOTAL
formula. #1511Excel 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[2] further increasing the time required to crack the password.
Office 2013 (Access, Excel, OneNote, PowerPoint, Project, and Word)uses 128-bit AES, again with hash algorithm SHA-1 by default.
Office 2016 (Access, Excel, OneNote, PowerPoint, Project, and Word) uses 256-bit AES, the SHA-1 hash algorithm, and CBC (Cipher Block Chaining) by default.
In 8.0, Dodeca optionally supports two levels of Excel file encryption:
EcryptSavedExcelFile
property 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.
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.
If encryption is only required for an Excel file that is created when the view
is saved to an Excel file, the EncryptSavedExcelFile
property can be set to True.
This property is supported for an Excel, SQLExcel, and all Essbase view types.
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.
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.
EncryptSavedExcelFile
property is True, the tools will show the
Excel Password dialog to obtain a password from the user to encrypt the Excel file.Encrypt tool: Supports encrypting the view template, if not already encrypted, or changing the password of an encrypted template.
If the active workbook is already encrypted, this message box is displayed:
EncryptSavedExcelFile
property 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:
The same filter is also displayed by the Open File dialog used by the Open Workbook tool in the View Template Designer.
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, CopyFromBinaryArtifact.Range
,
BuildRangeFromScript.ImportRange
, and OpenBinaryArtifact.General
, require the
specification of an Excel binary artifact ID. The VersionPolicy argument indicates
whether the latest version or the specified version should be used by the method.
If no value is specified, the default is Latest. If the value is Specific,
another argument indicates the specific version.
When the dependency analyzer evaluates the workbook script for dependencies, if no version is specified, an error similar to the following occurs: #2461
The search failed with the following error: Error using analyzer com.appliedolap.dodeca.deps.analyzers.WbsMethodAnalyzer@3685d047 to analyze artifact My_Workbook_Script/WORKBOOKSCRIPT/1
In 8.0, the appropriate version of the Excel binary artifact, based on the argument values is returned as the dependency. #2620
Log Application Request and Response XML to Server
is
enabled, the Metadata Dependency Explorer displays the following error when
searching for Depends On dependencies:
The search failed with the following error: Root element is missing. #2510Several 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.
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.
AutoBuildOnOpen
setting
should be used. If the tool argument is set to True or False, the opened view’s
AutoBuildOnOpen
setting is overridden and the specified tool argument value is
used. #2499A ShareSelectorTokens and a ShowViewAsActiveWindow tool argument has been added to the following Open View tool controllers:
OpenViewUsingViewStateToolController
The ShowViewAsActiveWindow tool argument has also been added to the OpenViewForDataCellToolController and OpenViewForMemberCellToolController.
The ShareSelectorTokens argument controls whether the current view’s selector tokens are added as tokens to the opened view. When added, the tokens are used to set default selections, when applicable, and can be referenced in workbook scripts, SQLPassthroughDataSets, etc. as used by the opened view. #1752
The ShowViewAsActiveWindow argument controls whether the opened view’s window becomes the active window. By default, the opened view’s window is activated. The ability to control the activation of the window allows for use cases for which retaining the current view’s window as the active window is preferable. #2613
To support the same option when a view is opened by a workbook script, a
ShowViewAsActiveWindow method argument has been added to the OpenView
workbook
script method.
Introduced in version 6.0.0.3106, the Relational Essbase Outline Caching capability provided the ability to store an Essbase outline, represented by an Essbase connection metadata instance, in a relational database. This functionality has been removed in 8.0, since it was complex to set up and maintain, and alternatives now exist. #2521
This is a potential BREAKING CHANGE. The Relational Essbase Outline Caching was used primarily to provide full-text search for Essbase members. As of version 7.8.5.7321, a new Essbase search algorithm was implemented that does not require a relational outline cache. It may also be possible that some customers have used the cached outlines for retrieving Essbase outline information into a grid within a Dodeca view. The same functionality can be achieved by using the relational extract functionality of the Next Generation Outline Extractor.
An AttributesPolicy
property has been added to the ScriptEssbase view type.
The property controls whether the server performs a retrieve on the results returned
by the script in order to obtain attributes for the member and data cells, which
are then returned with the results to the client. By default, the value is set
to NoAttributes. #2308
The AttributesPolicy
property was added to allow for the report script used by
a ScriptEssbase view to suppress row and column headings.
Attributes should not be included if the report script suppresses page and column headings using {SUPPAGEHEADING} and {SUPCOLHEADING}.
For example, for this layout, the report script uses {SUPPAGEHEADING} and {SUPCOLHEADING}
and the AttributesPolicy
property is set to NoAttributes.
Added support for the ability to navigate from a selector list in the Selector List Metadata Editor
to the associated SelectorID
in the Selector Metadata Editor. #1975
The Range selector list supports the ability to extract the selector item strings from a sheet range within an Excel binary artifact. This selector list type can be associated with a Generic selector, which includes the SelectorComboBox and SelectorListBox. #1739
An EssbaseRange selector list can be associated with an Essbase selector.
In addition to the standard selector list properties, the Range selector list
includes the RangeSourcePolicy
, which controls whether the view’s Excel binary
artifact, or a specified Excel binary artifact, is opened to obtain the selector
item data. When the policy is set to ExcelBinaryArtifact, the ExcelTemplateBinaryArtifactID
property identifies the Excel binary artifact.
The RangeName
, which can be tokenized, identifies the workbook or sheet-level
range from which the cell values are extracted.
If the ExcelTemplateBinaryArtifactID
is specified, the RangeName
can be selected
from a dropdown list of all the named ranges in the template workbook. For a
sheet-level range, the range name should be qualified with the sheet name.
The RangeCellSelectorItemPolicy
property controls whether each cell or each pair
of cells within the range is treated as a selector item.
A selector item has a value and display text. The display text is presented in the selector control. The value is used for the selector’s token. With the EachCellAsSelectorItem option, each cell represents a selector item. The value and display text are the same as the cell’s value.
With the CellPairsAsSelectorItemValueAndDisplayText option, each pair of cells in a row is treated as a selector item, where the first column’s cell value is used as the item value and the cell in the adjacent column is used as the display text.
Conceptually, the EachCellAsSelectorItem option is similar to the DelimitedString
selector list, whereas the CellPairsAsSelectorItemValueAndDisplayText option is
similar to the NameValuesPairDelimitedString
selector list.
As an example of the CellPairsAsSelectorItemValueAndDisplayText option, each row in the Numbers range contains two pairs of cells, The first cell’s value in each pair, such as 1, is used as the selector item’s value, and the adjacent cell’s value, such as One, is used as the text displayed in the selector tool and control.
By default, the SessionCaching
property is True, since the cell values are not
likely to change over the course of a session.
For an EssbaseRange selector list, each cell value within the range is the name
of Essbase member and represents an Essbase selector item. The RangeCellSelectorItemPolicy
is not supported for an EssbaseRange selector list.
Fixed Issue - When the SelectorControlProperties.ItemsSorted
is True for
the selector list associated with a SelectorListBox
, the items are sorted, but
items containing numeric characters are not sorted in natural order. In 8.0,
items are sorted as shown in the image on the right. #2663
ExpandSharedParent - A shared parent member is a shared member whose associated
base member has children. In the Essbase selector treeview, a user is able to expand
the node representing a shared member parent to view the descendants of the
base member. The behavior is optional and is controlled by a new property,
ExpandSharedParent
, which has been added to the SelectorControlProperties
for
an EssbaseSelectorTreeView selector list. #2700
The following caveats apply when ExpandSharedParent
is True:
When the shared parent member’s base member is not included in the branch(es) of
the dimension represented in the treeview, a descendant of the shared parent member
cannot be restored as a default selection when the DefaultSelectionPolicy
is
LastUsedItem.
The Search capability cannot be used to find and select a descendant of a shared parent member.
In previous versions, the view SelectorLastUsedItemContext
property supports
the following options: None, ByView, BySelector, and ByLabel. The property
controls the caching of the item(s) selected when the view is built or refreshed.
The cache is used to determine the default selected item(s) for the selectors whose
selector list has a DefaultSelectionPolicy
of LastUsedItem. For example, if the
SelectorLastUsedItemContext
is ByView, the default selections reflect the last
used items for the most recently built instance of the same view.
In 8.0, an additional context, BySelectorList, supports the ability to default the selections to the most recently cached selections for a selector using the same selector list. #2758
As in previous versions, the SelectorLastUsedItemContext
property can be overridden
on a selector-by-selector basis in the SelectorConfiguration
dialog with the
Last Used Item Context setting.
In previous versions, when the selector list assigned to a selector has a
DefaultSelectionPolicy
of LastUsedItem and the selector list is used by selectors
configured with both of the Selection Policy options, which include SingleItem
and MultipleItems, the default selection is not retained for the selector when
switching from a view with a Selection Policy of MultipleItems to a view with
a Selection Policy of SingleItem.
For example, if the Market selector is configured to set the default selection to the last used item(s), and is used by a view that only allows for a single Market member to be selected, and is also used by a view that allows for multiple Market members to be selected, the following behavior will be exhibited: If the view with the MultipleItems selection policy for the Market selector is built for Florida, New Hampshire, and New York, and then the view with the SingleItem selection policy is opened, there will be no default selection for the Market selector.
In 8.0, to address this behavior, the cache of selected items is retained by a
combination of the view or selector’s SelectorLastUsedItemContext
and the selector’s
Selection Policy. #2757
The Cache Viewer, which is opened from the Admin menu, can be used to view the
last used items. The highlighted cache item represents the last used items for the
4_Market selector. The cache item path or key is constructed based on the view’s
SelectorLastUsedItemContext
, which is BySelector, and the selector’s
Selection Policy, which is MultipleItems.
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):
To check or uncheck highlighted items (or nodes):
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 SelectorControlObjectType
as a SelectorTextBox and the SelectorListObjectTypeID
as Text.
The SelectorControlProperties
include the following:
HasMRUList
controls whether an MRU (most recently used) list of text strings is
retained and displayed in the dropdown list. The MaxMRUItems
indicates the maximum
number of text strings that are retained.
TextBoxWidth
controls the width of the textbox, but does not limit the number of
characters that can be entered. The width is specified in pixels. Depending on the
size of the individual characters, the default width of 200 will show approximately
28 characters.
ValidationRegExPattern
allows the specification of a RegEx pattern, which is used
to validate the input when the textbox is exited.
The format of the pattern is based on the .NET regular expression (Regex) syntax. Some examples include:
Letters and Spaces:
^[a-zA-Z ]+$
Letters, Numbers and Spaces:
^[a-zA-Z0-9 ]+$
Letters, Numbers, Underscore, Dash, and Spaces:
^[a-zA-Z0-9_- ]+$
For Unicode, any word character, including Unicode Letters, Numbers, Underscore, and Spaces:
^[\w ]+$
When the ValidationRegExPattern
is specified, the ValidationMessage
is displayed
to the user when the validation fails.
In 8.0, a search capability has been added to the SelectorListBox and EssbaseSelectorListBox controls. #1964
The display of the search tool is optional, and is controlled by the ShowSearchTool
setting for a selector list’s SelectorControlProperties
. By default, the search
tool is not displayed.
The following are some specifics about the search behavior:
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.
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.
The Sign On and Set Active Essbase Connection tool, which is typically used with an Essbase Adhoc view, has been modified to automatically attempt to sign on when the Sign On control is opened and credentials already exist for the view’s login service. If successful, the hierarchy of server(s), applications, and databases is displayed. This prevents the user from having to specify the credentials again. #2494
An Essbase view’s login service is identified by the view’s EssbaseLoginServiceObjectTypeID
property. The first time a login service is used during a session, the credentials
are retained. When the login service is subsequently used, such as by another
view for a different connection, the same credentials are used to attempt to connect.
Similarly, in 8.0, when a view with the same EssbaseLoginServiceObjectTypeID
is opened before the view that uses the Sign On tool, the credentials are available
and used to automatically sign on.
Database Transactions - The ability to execute a query’s insert, update, and
delete statements in a transaction is supported in 8.0. The use of transactions
is controlled by the query’s UseTransactions
property. The property is set in
the Query Editor, which is accessed from the SQL Passthrough DataSet Metadata Editor.
#2436
Query
ConnectString
property, which was used in pre-5.0 Dodeca versions
to specify connection information using Hibernate syntax, was deprecated in Dodeca 5.0,
when the SQLConnectionID
property was introduced. Support for the ConnectString
property was eventually phased out, but continued to be presented in the Query Editor.
In 8.0, the property is no longer presented in the client. #2725The 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
In Version 7.5.0.6430, support was added for the ability to select a defined range
name from a dropdown list in the DataTable Range Editor for the DataSheetRangeName
and DataBlockSheetRangeName
properties and in the RowSortAndGroupByInfo Editor
for the GroupTemplateSheetRangeName
property. #2662
In 8.0, this capability is supported for all the other properties that represent a defined range name, which include the following:
CommentRange Editor: Address
, KeyItemsColumnRange
, `KeyItemsRowRange, which
display both workbook and sheet-level defined range names.
CascadeSheetConfig Editor (as well as the equivalent View properties):
SummarySheetSummaryRangeAddress
, SummarySheetIncludeSheetAddress
, and
SummarySheetIsSummarySheetAddress
- The property values can be specified as either
a range reference or a defined range name, and a named range can be either sheet-level
or workbook-level, which are displayed in the dropdown list.
DataTable Range Editor: GroupStartCell
- This property was overlooked when the
capability was implemented in Version 7.5.0.6430. This property value can be either
a cell address or a defined range name. The values displayed in the dropdown list
include only sheet-level defined range names.
AutoComplete Range Editor: SheetRangeName
and RangeSourceRangeName
- The
property values are a workbook or sheet-level defined range name.
Fixed Issue: When the view properties are Categorized, each category can be collapsed
or expanded. For a view that supports a CommentRanges
property, one of categories is
Behavior. When the Comment Range Editor is opened, and a Comment Range is
selected, the expanded/collapsed state of the Behavior category in the View Metadata Editor,
if different, is changed to the expanded/collapsed state of the Behavior category
in the Comment Range Editor when the Comment Range Editor is closed.
The same issue occurs with the DataSetRanges Editor when the view supports a
DataSetRanges
property. #2658
Fixed Issue: When multiple views have very similar ID’s, which contain a percent sign (%), such as Ops Costing as a % of Sales and Ops Costing as % of Sales, attempting to select Ops Costing as % of Sales in the View Metadata Editor results in the following error: #2454
Unable to get the metadata instance from the encoded binary artifact. Unable to get metadata details. Multiple binary artifacts exist for "SAMPLE.VIEW.Ops Costing as % of Sales.1"
ViewSelectorProperties.AllowEditViewMetadata
property is set to True. The
option opens the Binary Artifacts Editor and sets the Excel template’s binary
artifact instance as the active instance. To decouple this option from the
AllowEditViewMetadata
property, the AllowOpenExcelBinaryArtifactEditor
property
has been introduced, which now controls whether the Edit Excel Template option is
displayed on the context menu. #2481AllowEditViewMetadata
property is set to True,
an Edit toolbars configuration item is added to the context menu to allow the
user to navigate from a view in the view selector to the view’s associated toolbars
configuration, as defined by the ViewToolbarsConfigurationID
property, in the
Toolbars Configuration Editor. #2525AllowOpenViewHierarchyEditor
has been added to the ViewSelectorExplorerBar
and ViewSelectorTree to allow an administrator to navigate from a view in the
view selector to the View Hierarchy Metadata Editor and, when possible, to the
corresponding view item in the appropriate hierarchy. #2482Cut, Copy, Paste, and Format Cells options have been added to the context menu in the View Template Designer. #2762
EssbaseConnectionID
is set, 2) the
active sheet does not contain named retrieve ranges with associated named connections,
and 3) the Sign On and Set Active Connection control is not used to connect
explicitly. #2429The 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 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
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.
Syntax Help
The syntax for the Remove names where criteria is similar to SQL. The specified criteria is used to select which defined names are to be removed.
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.
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.
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 ViewID
in 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:
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
A Workbook Script Set supports the ability to associate multiple workbook scripts, which are executed at run-time in a pre-defined order. By design, each workbook script contains a Workbook Script Set, which consists of itself as the Main workbook script, and to which other workbook scripts can be added.
When the Main workbook script is assigned to a view, all of the workbook scripts in the Workbook Script Set become associated with the view. When multiple workbook scripts link a procedure with the same event, the procedures are executed in the same relative order as the workbook scripts within the Workbook Script Set. #1624
To modify Workbook Script Set for the active workbook script in the Workbook Script Editor,
click the Open […] button to the right of the ScriptSet
property in the Definition
section.
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 ScriptSet
cannot include other workbook scripts, since WBS_A is included
in Main’s ScriptSet
. The Workbook Script Editor does not detect the violation,
ut an error will occur at run-time.
Workbook Script Method Exception Dialog - In previous versions, workbook script error messages were presented in a message box. To provide a better user-interface for identifying and resolving workbook script errors, the message box has been replaced with the Workbook Script Method Exception dialog, which presents the error message and related workbook script information in a more readable and understandable format. #2754
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 WorkbookScriptDebuggingEnabled
property is True,
the Script ID is an active link. When the property is False, the Script ID is
an inactive label, which prevents end-users from opening the Workbook Script Editor
and accessing the workbook script.
The WorkbookScriptDebuggingEnabled
property, which is also available in previous
versions, is used to control whether workbook script debugging is enabled for the
application.
If enabled at the application level, then the DebugMode
setting of a given workbook
script controls whether debugging is enabled for the workbook script. The
WorkbookScriptDebuggingEnabled
also controls whether the Workbook Script Method Exception
dialog provides an active Script ID link.
Before an application is migrated to a production environment, the WorkbookScriptDebuggingEnabled
should be set to False.
Workbook Script Trace Logging - The workbook script trace logging capability has been improved and enhanced. In previous versions, trace logging was enabled for a workbook script through the DoTracing setting in the script definition. This setting has been removed, and trace logging is controlled instead through the Utilities menu in the Admin application main toolbar. #1598
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.
Initial View:
Method Details:
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.
Ribbon Tabs:
Workbook Script Deprecation - It is sometimes necessary for a workbook script element (including functions, event links, methods, method overloads, and method arguments) to be deprecated in order to discourage the use of the element. This is typically due to an issue or behavior related to the element, which cannot be fixed because of the potential impact on existing workbook scripts. To address the underlying issue, a new element is usually introduced, such as a new method argument, which corrects the flawed behavior. #2229
A deprecated element will continue to be supported and will have no impact on the run-time behavior of workbook scripts which use the element.
As an example, in 7.7.0.6870, the DoInsert argument was added to the BuildRangeFromScript
method to address an issue with the existing Insert argument. The Insert
argument was effectively deprecated, but any workbook script using the argument
will continue to behave as before.
In 8.0, the Workbook Script Editor visually distinguishes a deprecated element. or a deprecated method argument, the argument label is displayed in red, and the argument description contains information related to the reason for the deprecation and the recommended alternative.
This infrastructure was implemented in anticipation of additional deprecated elements,
but to date, the BuildRangeFromScript
Insert argument and the SetHidden
method’s
Worksheet
overload are the only elements to have been deprecated.
Going forward, when elements, which would typically appear in a dropdown list, such as a method, are deprecated, the Show/Hide deprecated items tool controls whether the elements are visible in the list.
New Event Link: The AfterCascadeSummarySheetBuild
event link can be used to
execute a workbook script procedure after a summary sheet is built for a cascaded
SQL or Essbase Excel view. The creation of a summary sheet for a cascaded view
is controlled by the view’s SummarySheetAdd
or CascadeSheetConfig
properties.
The event argument is CascadeSheetNames, which is a forward-slash "/" delimited list of the names of the sheets created by the cascading. #1791
New Event Links: In addition to the existing ClipboardPasting
and ClipboardPasted
,
event links have been added for clipboard cutting and copying, including:
ClipboardCopying
, ClipboardCopied
, ClipboardCutting
, and ClipboardCut
.
The ClipboardCopying
and ClipboardCutting
events can be cancelled by using the
CancelEvent
method.
The event argument is ClipboardAddress, which is the address of the copied or cut range. #2098
BeforeDataSetRangeBuild
and AfterDataSetRangeBuild
event links be used with a SQL or Essbase Excel view, and occur before and after
a data set range is built. The event properties include DataSetRangeName,
DataSetID, and SheetName. #1581BeforeDataSetRangesSave
and AfterDataSetRangesSave
can
be used with a SQL or Essbase Excel view, and occur before and after all the
view’s data set ranges are saved. The BeforeDataSetRangesSave
event can
be cancelled using the CancelEvent
method. #2402CommentExceptionOccurred
event link allows a workbook script
to handle a failure when comments are loading, saved, etc., which is primarily
caused by a network connectivity issue. The event has an Exception event property.
#2476New Event Link: The BeforeAutoCompleteRangeListLoad
can be used in conjunction
with the AutoCompleteRange
functionality to customize the list of cell values
for the active cell within the context of other cell values. The event occurs
before the list of cell values is generated or loaded from the list source. #2784
The event can be cancelled, which allows the workbook script to detect any conditions that should prevent the user from editing the cell value. And, if so, the ShowMessageBox method can be used to notify the user.
Refer to the Auto-Complete section for more
information about auto-complete ranges and an example of using the BeforeAutoCompleteRangeListLoad
event link.
New Function: The IsValidRange
function returns True/False to indicate whether
the specified value can be resolved to a valid range address.
#2686
@IsValidRange(<NameOrAddress>)
New Function: The Evaluate
function returns True/False to indicate whether
the specified value can be resolved to a valid range address.
#2686
@Evaluate(<Formula>)
New Function: The TimeZoneInfo
function returns the value of the specified
property of the local timezone.
#2277
@TimeZoneInfo(<PropertyName>, [<TimeFormat>])
PropertyName - The name of the time-zone property. The default is G.
PropertyName | Description |
---|---|
S[tandardName] | The standard name of the local time-zone. |
UtcOffsetH[ours] | The UTC offset in hours for the local time-zone. |
UtcOffsetM[inutes] | The UTC offset in minutes for the local time-zone. |
T[oUniversalTime] | The local time converted to UTC. |
I[sDaylightSavingTime] | Whether DaylightSavingTime is in effect at the current time. |
DaylightN[ame] | The standard name for DaylightSavingTime in the local time-zone. |
DaylightS[tart] | The start date/time of DaylightSavingTime in the local time-zone. |
DaylightE[nd] | The end date/time of DaylightSavingTime in the local time-zone. |
DaylightD[elta] | The adjustment in hours for DaylightSavingTime in the local time-zone. |
TimeFormat - Optional. A custom format string or a standard format code.
Examples of custom format strings, which are for en-US:
Refer to: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings
TimeFormat Example | Result |
---|---|
h:mm:ss.ff | 2019-06-30T15:29:58.26 → 3:29:58.26 P |
d MMM yyyy | 2019-06-30T15:29:58.26 → 30 May 2019 |
H:mm:ss.f | 2019-06-30T15:29:58.26 → 15:29:58.2 |
dd MMM HH:mm:ss | 2019-06-30T15:29:58.26 → 30 May 15:29:58 |
H:mm:ss.ffff | 2019-06-30T15:29:58.26 → 15:29:58.2650 |
Standard Format Codes:
Refer to: https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-string
Code | Description | Example |
---|---|---|
d | Short date pattern. | 2019-06-15T13:45:30 → 6/15/2019 |
D | Long date pattern. | 2019-06-15T13:45:30 → Monday, June 15, 2019 |
f | Full date/time pattern (short time). | 2019-06-15T13:45:30 → Monday, June 15, 2019 1:45 PM |
F | Full date/time pattern (long time). | 2019-06-15T13:45:30 → Monday, June 15, 2019 1:45:30 PM |
g | General date/time pattern (short time). | 2019-06-15T13:45:30 → 6/15/2019 1:45 PM |
G | General date/time pattern (long time). | 2019-06-15T13:45:30 → 6/15/2019 1:45:30 PM |
M.m | Month/day pattern. | 2019-06-15T13:45:30 → June 15 |
O,o | Round-trip date/time pattern. | 2019-06-15T13:45:30 → 2019-06-15T13:45:30.0000000-07:00 (Local) |
2019-06-15T13:45:30 → 2019-06-15T13:45:30.0000000Z (Utc) | ||
R,r | RFC1123 pattern. | 2019-06-15T13:45:30 → Mon, 15 Jun 2019 20:45:30 GMT |
s | Sortable date/time pattern. | 2019-06-15T13:45:30 → 2019-06-15T13:45:30 (Local) |
2019-06-15T13:45:30 → 2019-06-15T13:45:30 (Utc) | ||
t | Short time pattern. | 2019-06-15T13:45:30 → 1:45 PM |
T | Long time pattern. | 2019-06-15T13:45:30 → 1:45:30 PM |
u | Universal sortable date/time pattern. | 2019-06-15T13:45:30 → 2019-06-15 13:45:30Z |
U | Universal full date/time pattern. | 2019-06-15T13:45:30 → Monday, June 15, 2019 8:45:30 PM |
Y,y | Year month pattern. | 2019-06-15T13:45:30 → June, 2019 |
Fixed Issue: Any function that has multiple string arguments fails with a Missing argument(s) error when the function is called with multiple strings arguments enclosed in double quotes. #2555
For example, the following use of the substitute
function should return
Cola','Diet Cola','Caffeine Free Cola
@substitute("Cola;Diet Cola;Caffeine Free Cola", ";", "','")
CellFillColor
, CellIsHidden
, CellIsLocked
, Checksum
, ColumnWidth
,
CommentPropertyForCell
, ExcelComment
, HasExcelComment
, IsDataCell
, IsMemberCell
,
and any of the DataPoint
and MemberCell
functions. #2696CommentPropertyForCell
function
is called and the CellAddress property is an invalid cell address:
Object reference not set to an instance of an object. #2696ReplaceTokens
function 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. #2804In 8.0, the CallWebService
, OpenApplication
, and Process
methods are supported
as Workbook Script Extensions instead of as built-in methods. Because there is a
potential security risk associated with the use of these methods, the availability
of the methods requires the explicit inclusion of the extensions either by importing
as a module into each tenant, or by including as a component in the deployment.
The module can be imported from the wbs_extensions.zip, which is installed in the
DodecaFramework metadata
folder. The component can be included when creating the
deployment in the ClickOnce Prep Utility, which is the recommended approach. #2648
This represents a BREAKING CHANGE for any workbook script that uses the
CallWebService
, OpenApplication
, or Process
method unless the Workbook Script
Extensions are imported or deployed.
The AddDataCache.SqlScript
overload, BuildRangeFromScript.SqlScript
overload,
and ExecuteSqlTransaction
are no longer supported. #2566
While this represents a potential BREAKING CHANGE, we are not aware of any customers using these overloads and method, which were previously replaced with similar, yet more enhanced functionality through the SQLPassthroughDataSet technology.
Fixed Issue with AddDataCache
Method: When the EssbaseMembersPerGeneration
overload is used to add member information to a data cache, member information
from the level that matches the given number or name is returned instead. #2680
This represents a potential BREAKING CHANGE; data caches produced by workbook script procedures that utilize the AddDataCache.EssbaseMembersPerGeneration overload will change. Please review any procedures that utilize this overload.
SetBorders
, SetConditionalFormat
, SetFill
, and SetWorksheetOptions
methods, a color RGB argument has been added for all existing arguments that accept an
Excel color index, but not a specific color. For example, SetBorders
has a TopColor
argument, which specifies an Excel color index. A TopColorRGB argument has
been added to allow for specifying the color as an RGB value instead of an Excel
color index. #2460A new argument has been added to the EssbaseSend
method: The UseMemberNamesAndAliasesForRows
argument controls whether members names and aliases are used for row dimensions.
The argument allows for overriding the view’s UseMemberNameAndAliasesForRowDimensions
setting, which is used when the argument value is not specified. The argument is
primarily used when multiple send ranges exist, and require a different value
for the setting. #2637
In 8.0, a SQLDataCache
overload is supported for the ForEach
method, which
is used in conjunction with a data cache created using the AddDataCache
method’s
SQLPassthrougDataSet
overload. The SQLDataCache
overload allows access to
each column value of the current row from within the procedure called by the ForEach
method. The value of a given column for the current row is available in a workbook
script property, which has the same name as the column. #2449
In the following example, the AddDataCache.SQLPassthroughDataSet
overload is used
to add a data cache containing the results of a relational query, which represents
customer contact information:
The procedure that is executed by the ForEach
method calls ShowMessageBox
to
display the current row’s values. The property values are used to create the caption
and the message displayed in the message box.
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.
The SQLDataCache
overload also supports an optional argument, PropertyNamePrefix
.
If specified, the value is prepended to each property name.
In the example workbook script, if the PropertyNamePrefix
is set to C_, the
ShowMessageBox.Message
argument uses the prefixed property names:
The ForEach
method DataCache
overload has also been enhanced to allow access to
all of the column values of the current row from within the procedure called by
the ForEach
method for a data cache that is created by any of the AddDataCache
overloads.
Prior to 8.0, only a single column value could be accessed. The PropertyName argument specifies the name of the property that is added and which contains the value of the column specified by the ColumnNumber argument. In 8.0, an additional property is added for each column in the row and the property name is appended with a period and the column number, starting with ".1".
To illustrate the difference between the DataCache
and SQLDataCache
overloads
with respect to the creation of the properties, the following example uses the
same data cache, created by the AddDataCache.SQLPassthroughDataSet
overload,
which was used in the previous example.
The ForEach.DataCache
overload’s PropertyName argument value is ColumnName
and the ColumnNumber
value is 1. Since the data cache contains 8 columns, 9
properties are created: ColumnName, ColumnName.1, ColumnName.2… ColumnName.8.
As in the previous example, the procedure called by the ForEach
method shows
a message box. The caption includes the customer’s first and last name, and the
message shows the value of each column in the current row.
New Method: The CommentOperations
method, Load
overload, can be used to
reload comments or to load the comments for a comment range, which has the
LoadPolicy
set to None. #1415
Depending on the requirements, the scope of the load operation can be controlled by the SpecifySheetBy, SheetSpec, and Address method arguments, which allows for 1) loading all comment ranges on all sheets, 2) loading a specified comment range on all sheets, such as for a cascaded view, 3) loading all comment ranges on a specific sheet, 4) loading a specified comment range on a specific sheet, and 5) loading a specified comment range with no sheet specified.
New Method: The SetDataTableRangesModificationTracking
method allows for a
workbook script to control whether changes to cell values in existing rows in a
data table sheet range are subsequently detected as unsaved changes when the view
is rebuilt or closed.
By default, when the SaveDataSetRanges
tool is included in the toolbar of a
SQL view or an Excel Essbase view, any changes to cell values in existing rows in a
data table sheet range that are not saved will be detected as unsaved changes when
the user attempts to rebuild or close the view. The user is then asked whether
the changes should be saved.
When a workbook script modifies the cell values in a data table sheet range, the
SetDataTableRangesModificationTracking
method can be used to temporarily disable
modification tracking. This prevents the changes by the workbook script from being
detected as unsaved changes. #2231
A view may require the use of formulas in one or more columns within a data table
sheet range. While this can be accomplished by using the CopyRange
method along
with the SetDataTableRangesModificationTracking
in the AfterDataSetRangesBuild
event, the preferred approach is to include the formulas in the data table sheet
range in the view template. When the first row of a data table sheet range contains
a formula in a given column, the formula is automatically copied to all cells in
the column within the range during the build processing, and the resulting changes
to the cell values are not subsequently detected as unsaved changes.
New Method: The SetEssbaseSendRangeModificationTracking
is used to enable or
disable modification tracking for Essbase send ranges by setting an Essbase view’s
SendRangeModificationTrackingEnabled
run-time property. #1591
The property controls whether the Send tool detects changes to values within send ranges, which determines whether the tool prompts the user when unsaved changes are detected. When a workbook script needs to modify values within a send range, which should not be detected by the tool, this method can be used to temporarily disable modification tracking, and then enable after the edits are completed.
SendRangeModificationTrackingEnabled
run-time property to. If the value is blank, TRUE is assigned.New Method: The Wait
method pauses the execution of the current procedure for
a specified number of seconds using the General
overload or until a specified
condition is met using the 'Until' overload. #2279
New Method Overload: The SetBorder
method has a new LineStyleSG
overload.
The overload addresses a limitation with the support of Excel line styles by the
SpreadsheetGear control, which can only render the Excel line styles None, Continuous,
and Double. The Excel line styles Dash, DashDot, DashDotDot, Dot, and
SlantDashDot are rendered as Continuous. All Excel line styles are maintained
and displayed correctly when the workbook is viewed in Excel.
The LineStyleSG
overload provides the line styles None, Dotted, Thin,
Medium, Thick, and Double, which render correctly in the SpreadsheetGear
control and will look the same when the workbook is viewed in Excel.
For the SetBorder
method All
overload, a LineStyleSG
argument has been added.
#2697
This screenshot depicts the rendering of the Excel line styles and SpreadsheetGear line styles in the SpreadsheetGear control and when viewed in Excel.
New Method Overloads: The CopyRange
method has two new overloads. The ToClipboardAsImage
and ToFileAsImage
overloads copy a range to the clipboard or to the specified
file as an image. #2228
+
New Method Argument: A ShowViewAsActiveWindow argument has been added to the
OpenView
method to support the ability to control whether the opened view’s window
becomes the active window. #2613
When the argument is set to False, the opened view’s window does activate for
less than second or so (for an asynchronous view) or until the view builds (for a
synchronous view) and then the current view’s window reactivates. This behavior
is due to implementation constraints related to the use of the OpenView
method
for batch report generation.
New Method Arguments: Several arguments have been added to the SendEmail.SMTP
method overload to support the ability to include a snapshot of a range or ranges
from the view in the email. #2223
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.)
CopyRange
method overloads to allow the method to perform a cut operation and
automatically clear the copied cells. #2527EssbaseRunMaxl
method. The existing MessagesSheetName argument is the name
of a worksheet, which is added to the current view and will contain the messages generated
by the execution of MaxL script that are not related to any specific statement.
The StatementMessagesSheetName argument is the name of a worksheet, which will
contain the messages related to the execution of a single statement. If the script
contains multiple statements, then a sequentially numbered sheet will be added for
the messages generated by each statement. In previous versions, the messages
related to a given statement were written to the sheet containing the statement
results, which is specified by the ResultsSheetName argument. #2451SetWorkbookOptions
method. The argument value controls the ratio of the width of the worksheet tabs
to the width of the horizontal scrollbar as a number between zero and one. For
example, if set to .75, the width of the area for worksheet tabs is 75% and the
width of the scroll bar area is 25% of the total width of the view window. By default,
the value is .6. #2206New Method Arguments: For the SetUDA.RemoveFromMember
method overload, new arguments
have been added to support the ability to control whether the removal of the specified
UDA’s continues after an error is encountered on the server when a given UDA
(in the delimited string of UDA’s) is not valid for the member. #2211
CopyFromBinaryArtifact.Range
, CopyRange.FromFile
, BuildRangeFromScript.ImportRange
,
and BinaryArtifactOperations.ImportRange
. #2348AddDataCache
method overloads. The argument controls whether
the value of the first column in each row in the data cache must be unique.
If not specified, the argument value defaults to False. #2343OpenView
method. The argument controls whether the current view’s selector tokens are added as
tokens to the opened view. When added, the tokens are used to set default selections,
when applicable, and can be referenced in workbook scripts, SQLPassthroughDataSets, etc.
as used by the opened view. #1752Deprecated Method Argument (and New Method Argument): The EssbaseReport
method
IncludeAttributes has been deprecated, and a new argument, AttributePolicy,
has been added. #2308
The IncludeAttributes argument controls whether the server performs a retrieve on the results returned by the script in order to obtain attributes for the member and data cells, which are then returned with the results to the client. An issue exists with the implementation in that argument value is reversed in the request sent to the server, such that True is treated as False and vice versa.
To avoid introducing a potential breaking change to existing workbook scripts, which may use and set the argument value based on the actual behavior, the IncludeAttributes argument has been deprecated. The new AttributePolicy argument can be used instead to control whether member and data cell attributes are returned with the results. The valid argument values are IncludeAttributes and NoAttributes. By default, when no value is specified (and the IncludeAttributes is not specified), attributes are returned.
Attributes should not be included if the report script suppresses page and column headings using {SUPPAGEHEADING} and {SUPCOLHEADING}.
Refer to ScriptEssbase View for an example of excluding attributes when page and column headings are suppressed in the report script.
Revised Method Argument Description: The SQLPassthroughExecute
method description
was revised to avoid a potential issue when a statement contains a semi-colon and
the StatementDelimiter is not specified as a non-semicolon character. The method
description:
Executes a delimited list of SQL statements within a single server request. Important: If the Statements argument contains a semicolon ";", then multiple statements should be delimited using a non-semicolon character, such as the pipe character "|", which should be specified as the StatementDelimiter argument. By default, the StatementDelimiter is a semicolon. #2428
Fixed Issue with SetHidden
Method - With the Worksheet
overload, the address
of the selected cell is used instead of the specified Address argument as the basis
for evaluating the Hidden argument. For instance, if A1 is the active or selected
cell on the sheet and is empty, and the Address argument is C3 and contains TRUE,
the sheet does not become hidden when the Hidden argument is =@ACell()=TRUE.
To avoid potentially breaking existing workbook scripts by correcting the behavior,
the Worksheet
overload has been deprecated and a new overload OfWorksheet
has
been introduced, which correctly applies the specified Address argument. #2226
BinaryArtifactOperations
Method - With the CreateFromFile
overload, a .xlsx extension is added to the specified filename, which results in
an error similar to the following: Could not find file c:\Users\Administrator\Documents\MyTextFile.txt.xlsx
#2537SetSelector
Method: When a view’s Auto Build tool is
turned On and the view’s workbook script uses the SelectorSelectionChange
event
to call SetSelector
to set the selection for another selector, the following
error occurs: Object reference not set to an instance of an object. #2442SetWorksheetOptions
Method: When the method is used to rename
a worksheet in an Essbase Excel view, any Essbase-specific functionality that
is dependent on the internal representation of the Essbase data on the sheet does
not function as expected. #2778AddDataCache
Method: With the EssbaseMdxQuery
overload,
when members in the column axis contain invalid XML characters, an error similar
to the following occurs:
Unable to send the request. Name cannot begin with the '0' character, hexidecimal value 0x30, Line 1, position 790.
#2240