Dodeca Version 6.3.0

November 13, 2012 - Build 3699

This version uses SpreadsheetGear 2010 (, NetAdvantage 2011, Volume 1 (11.1.20111.2042), and Aspose.Cells

The release notes for this version contain the following sections:

ClickOnce Prep Utility

Dodeca-Essbase Server — Gzip Compression

Dodeca-Essbase Server — Miscellaneous

Dodeca-Essbase Server — Relational Outline Caching

Dodeca Server — Metadata Audit Log

Essbase Connection Metadata

Essbase Views

Excel View Wizards

Excel View Template Designers

Metadata Editors

Relational Views (SQL Excel and Essbase Excel)


SQL Server JDBC Drivers

View Sharing (and Authentication Services)


Workbook Script — Essbase MDX

Workbook Script Functions, Methods, Properties, and Event Links

ClickOnce Prep Utility

A new temporary digital signing certificate (dodeca_temp.pfx) is installed with this release, and expires on 12/31/2039. The previous certificate is set to expire on 11/20/2012. All installations that use the new certificate will be treated as new installations. This means that the client-side cache is not retained from the previous version. For instance, the size of the application window and the last used selector items are not restored from the previous version. (#1131)

Dodeca-Essbase Server — Gzip Compression

The dodeca-gzip-filter servlet filter compresses the xml stream returned from both the dodeca and dodeca-essbase services to reduce bandwidth, which can reduce the time it takes for transferring the results from the server to the client. The amount of compression varies depending on the nature and size of the response from the server. (#1118)

The dodeca-gzip-filter servlet filter code is contained within the dodeca and dodeca-essbase servers and is configured inside the WEB-INF\web.xml file for each respective server. The web.xml entries for the filter are:



If multiple filters are specified and the dodeca-gzip-filter servlet filter is enabled, the dodeca-gzip-filter must be mapped first. If this filter is not mapped first and thus becomes the outermost servlet filter, other filters enabled on the server may receive gzipped data.

Though the gzipped content may reduce bandwidth requirements, use of the dodeca-gzip-filter may not result in optimal performance. The gzip algorithm adds overhead to the communication due to the time and resources required to compress and uncompress the data. To assist in computing the optimal performance, this version of Dodeca has a discrete gzip logger. The gzip logger is configured in the classes\ file.

log4j.additivity.dodeca.service.gzip If the logger is set to DEBUG level, detailed gzip log information is written to the dodeca-essbase-gzip and dodeca-gzip log files. These logs contain the uncompressed size, compressed size, compression percentage, and time to compress. This information can help you determine if the time spent compressing the content, plus the time saved in data transfer, will result in better performance given your network speed.

Below is a sample of the log contents. The last row shows an Essbase ZoomIn transaction where the 16 Mb response is compressed to about 1.8 Mb for a compression ratio of 89.1%. The compression time, however, is about 3.9 seconds. If you take into account a similar amount of time for decompression, then it would make sense to enable gzip when the amount of time to transfer 16 Gb at your network speed is more than the amount of time to transfer 1.8 Gb at those speeds plus the estimated 7.8 seconds of processing time added for gzip compression and decompression.


Dodeca-Essbase Server — Miscellaneous

  • Essbase Cube Information — Relational cache logging

    The Dodeca-Essbase service requires particular cube information to operate properly. The cube information is normally generated from the Essbase outline when the first user logs into the system and connects to a given cube. The information is then cached in server memory. Starting in version 6.0, the administrator has the option to generate this information in the background and to store it in a relational data store.

    To make it easier to determine if the cube information is being generated dynamically or if it is being pulled from the relational cache, the logging for this functionality was split out into its own log file, the dodeca-essbase-cubeinfo.log file.

    This is an example of a log entry for a cube info call that generated the cached cube information:

    INFO|2012.09.24 13:26:59.243|dodeca.essbase.service.cubeinfo|"Generated item

    Mustang.Sample.Basic and stored it in the MetadataCache (291 ms)"

    This is an example of a log entry for a cube info call that retrieved cached information:

    INFO|2012.09.24 13:43:42.142|dodeca.essbase.service.cubeinfo|"Found cubeinfo

    item localhost.Sample.Basic in the relational cache and stored it in the MetadataCache;

    CACHED_OUTLINE_ITEMS.HASHCODE=a3bb7ec1033aa451ad304f15eb8167dd1637d88c. (9 ms)

    Note: This log can also be used to determine the performance implications of relational caching.

    The file has a new section for configuring this log. The log messages will be output for the INFO level and below. (#1123)

  • Fixed issue with the Essbase data audit log not recording the log values for Date and Text measure values. (#1114)

  • Fixes issue with a text measure that has a missing label defined. A retrieve operation does not return the missing label defined for a text list, but instead returns #MISSING. (#1115)

  • Fixed issue with retrieving members in a dimension with a dimension number greater than 32. (#1116)

Dodeca-Essbase Server — Relational Essbase Outline Caching

The relational Essbase outline caching functionality was introduced in Dodeca version The functionality provides the ability to store Essbase outlines represented by Dodeca Essbase connection objects in a relational database to enhance both performance and flexibility to Essbase member requests.

In the initial release of this functionality, in order to enable outline caching for a given Essbase database, an administrators was required to either add the outline caching information to the file or, alternatively, to insert a record into a table in the Dodeca metadata store. This was sometimes difficult for administrators to do, so new functionality was implemented to make the configuration easier and to provide additional capabilities. This functionality includes the following:

  • The relational outline caching can be enabled or disabled for an Essbase database from the client using the Essbase Connections Metadata Editor. (#1097)

  • The relational outline caching for an Essbase database can be configured to cache summary-level information only. The cached summary-level information is used when Dodeca first connects to an Essbase data source, which may require the Dodeca-Essbase server to open the outline. Opening some Essbase outlines can be a time-consuming process, which may result in some users experiencing slow start up times of the Dodeca client. (#1098)

Note: The new ExtendedCubeInfoEnabled property can also be used to decrease the startup time. Refer to the Essbase Connection Meatdata section for more information.

In the Essbase Connection Metadata Editor, each connection contains Relational Outline Caching properties:

  • *RelationalOutlineCacheID* — The ID of the relational outline cache, which is used to uniquely identify the outline cache for the database represented by the Essbase connection. If not specified, the default ID is formatted as <Server>.<Application>.<Database>, such as MyEssbaseServer.Sample.Basic.

  • *RelationalOutlineCachingEnabled* — Controls whether relational outline caching is enabled for the Essbase database.

  • *RelationalOutlineCachingSummaryOnly* — Controls whether the relational outline cache includes the outline details, which is the default, or only the outline summary-level information.

    To change these properties, the *AdminPassword* and *AdminUsername* must also be specified.

The Dodeca-Essbase server must still be properly configured for relational

outline caching before these properties can be applied. Refer to Section Dodeca Essbase Server — Relational Outline Caching, Version, for additional details. Steps 4 and 5 are no longer required. (#1108)

Dodeca Server — Metadata Audit Log

The dodeca metadata audit logging feature is now disabled by default. The metadata audit logging can, in certain cases, cause issues when loading metadata into an Oracle database using the Application Setup Utility. (#1094)

The metadata audit logging configuration property is located in the file, which is installed with the Dodeca Framework in the staging\WEB-INF folder. To enable logging, change the value to true, and then use the ClickOnce Setup Utility to create the WAR file. Depending on the Java application server, the setting can also be modified after deployment.


Essbase Connection Metadata

  • The Essbase connection metadata contains a new property, ExtendedCubeInfoEnabled, which controls whether the extended cube information is returned when cube information is obtained for the database.

    Cube information is obtained when an Essbase connection is used by the Dodeca client. Most of the cube information can be obtained without opening the outline, except for the following extended information: SmartLists (i.e. the text list strings and associated values for SmartLists) and the date format string.

    Since opening an outline can be time consuming, setting the ExtendedCubeInformationEnabled property to False when the extended information is not needed avoids the overhead incurred when the outline is opened.

    Although the SmartList and date format string are not currently used internally on the client, the date format string is used internally by the dodeca-essbase service in the data audit log functionality. If ExtendedCubeInformationEnabled is set to False and if date measures are updated, the data audit log tables may not accurately reflect the updated data. (#1125)

    Essbase Views

  • Fixed issue: When retrieving data from an outline marked to allow duplicate member names, members that are not unique in the outline do not appear in the grid.

    Resolution: Unique member names are represented in the grid using the member ID. For example, if a duplicate member exists that represents Rochester, New York, then the fully-qualified member ID, [New York].[Rochester], appears in the grid. (#1126)

Additional unique member name functionality is intended for a future release.

Excel View Wizards

The Excel, Essbase Excel, and SQL Excel View Wizards provide a step-by-step user-interace for creating, editing, and deploying Excel-based views. All the required view properties along with the most frequently used properties can be set. Upon completion, the wizard automatically creates the metadata objects that represent the view, the Excel binary artifact, and the workbook script, as needed. The wizard then presents several options, including adding the view to a view hierarchy, opening the view template designer, reviewing and editing all the view properties, and opening the view.

Opening the View Wizard

To create a new view using the wizard, open the wizard for the specific view type from the Admin menu.


To edit an existing view using the wizard, open the wizard from the View Selector ExplorerBar or View Selector Tree by clicking the Excel view with the right mouse button and then selecting the Open View Wizard option from the context menu.


To enable the ability to open the view wizard from the View Selector, use the Admin / Applications editor to set the View Selector’s AllowOpenExcelViewWizard property to True.


The Wizard User-Interface

The wizard dialog is presented as a sequence of pages that can be navigated using the navigation buttons (i.e Back, Next, Finish, etc.) or the navigation guide on the left, which is an ordered list of page links. The navigation guide also provides a visual means of indicating which page is the active page by displaying the active page link as bolded text.


The view wizards are modeless dialogs, which allow you to interact with the application while the dialog remains open.

The view wizard presented in the screenshots below is the Essbase Excel view wizard. The Excel and SQL Excel view wizards are identical to the Essbase Excel view wizard, with the exception of the three Essbase-specific pages: Essbase Connection Settings, Essbase Retrieve Settings, and Essbase Send Settings. In a future release, the SQL Excel view wizard will be enhanced to support the configuration of SQL Passthrough DataSet ranges.

The pages are generally self-explanatory, but the View Template Options page is described here to bring your attention to the choices available for identifying the Excel file to be used by the view.


The view template options include:

  • Import an Excel file — Displays a page that allows you to browse the file system and select the Excel file.

  • Create an Excel file — Displays a page that includes the two Excel file format options, .xls and .xlsx. This option creates a blank Excel workbook.

  • Select an existing view template — Displays a page which allows you to select from a list of all the existing Excel binary artifacts, which are the Excel files that have already been loaded into the metadata repository.

When one of the options is selected and the page associated with the option is displayed, another page link appears in the navigation guide below the View Template Options page link. In this example, the "Create an Excel file" option was selected. You can navigate to the page, but you can also change the option by navigating to the View Template Options and then select another option.


Once you have completed specifying the settings and are ready to save the view, click the Finish page link at the bottom of the navigation guide or click the Finish button on the Save and Sharing page.


When using the wizard to create a new view, the wizard does the following when Finish is clicked:

  • Creates a new view metadata instance that contains the specified settings.

  • If the View Template "Create an Excel file" or "Import an Excel file" option is selected, creates the Excel binary artifact and associates the template with the view.

  • If the Workbook Script page "Add a new workbook script" option is selected, creates a new workbook script instance and associates the workbook script with the view.

  • Commits the metadata instance(s) to the metadata repository.

  • Displays the post-finish Options page.


    The Edit View navigation button restarts the wizard in edit-mode.


    The "Add the view to a hierarchy" option displays this page:


    To add the view to a hierarchy, select the hierarchy from the dropdown list. Click and drag the view to the hierarchy and drop it at the location where you want to place the view. You can also use drag-and-drop to move and copy items within the hierarchy. Save the changes to the hierarchy by clicking the Commit button.

    You can add the view to another hierarchy by repeating the steps.

    Click the Options button to return to the Options page.


    The "Edit view properties" option displays this page, which allows you to review and edit all of the view’s properties.


    To save any changes, click the Commit button. Click the Options button to return to the Options page.


    The View Template Designer, which is described in the following section, can be opened directly from the wizard.


    The view can be previewed by opening the view directly from the wizard.

Excel View Tempate Designers

The Excel, SQL Excel, and Essbase Excel view types use an Excel file as the view template. The Excel file is stored in the metadata repository as a binary artifact, and associated with one or more views through the view metadata. Previously, all editing of the view template was performed in Excel. With this release, editing the view template is now supported within Dodeca.

The View Template Designers support many commonly used Excel editing tools, along with tools designed specifically for enabling the template to be used within Dodeca, such as inserting selector tokens and adding retrieve and send ranges for Essbase Excel views. The Essbase Excel View Template Designer also supports Essbase operations similar to the classic Essbase Excel add-in.

Essbase Excel View Template Designer


Excel and SQL Excel View Template Designer


The view template designer tab caption contains the ID and version of the Excel binary artifact that stores the view template, and the name of the Excel file.


Key view template designer features include the following:

  • View template versioning — The template can either be saved as the current version (that is displayed in the designer) or as a new version of the Excel binary artifact. To load a previous version of the template into the designer, select the version from the Template Versions dropdown list. Selecting the current version from the list allows you to discard any unsaved changes.

  • Opening an Excel file from the file system — A Excel file can be opened from the file system, edited, and saved as the view template (or as a new version of the view template.)


_It is possible to have multiple Excel files open concurrently. The Workbooks dropdown contains the list of all of the open workbooks, and can be used to change the active workbook. The active workbook has a checkmark to the left of the file name and designer tab caption also contains the file name. _

When the template is saved, the active workbook content is saved as the view template.


Helpful Hint

There may be times when you working in the designer and need to use Excel functionality to edit the template that is not supported by the designer. You can use the following steps to integrate Excel into the view template designer workflow:

  1. From the designer, use the Open in Excel tool to open the view template in Excel.

  2. Edit and save the Excel file.

  3. Use the Open Workbook tool to open the Excel file in the designer.

  4. Make any additional changes to the template in the designer, if necessary.

  5. Use the Save Template or Save Template as New Version tool to commit the view template to the metadata repository.

    • Excel (and related) operations

The designers support the following Excel tools:

  • Clipboard — Cut, copy, and paste

  • Font — Font face and size, increase and decrease font size, font attributes (bold, italic, and underline), border, fill color, and font color

  • Alignment — Align text left, center text, align text right, merge and center

  • Number Format — Accounting number format, percent style, comma style, increase and decrease decimal

  • Cells — Insert (cells, rows, columns, sheet), and delete (cells, rows, columns, sheet)

  • Format cells — Supports setting the number format, alignment, font, borders, interior, and protection for the selected range

  • Filter (Excel and SQL Excel view template designers only) — Enable filtering, clear and reapply filter

  • Show — Show headings, gridlines, formulas

    • Unhide Rows and Columns and Restore Hidden Rows and Columns

      The standard Excel Hide and Unhide Rows (and Columns) tools are available on the context menu. The Unhide Rows and Columns tool provides a convenient way to unhide all hidden rows and columns in the active worksheet. The tool is enabled when there are any rows or columns located within the range defined by $A$1 and the last used cell. When the tool is clicked, the rows and columns become unhidden. The designer retains the addresses of the unhidden rows and columns, which are used by the Restore Hidden Rows and Columns tool to "undo" the unhiding of the rows and columns.

  • Zoom — Zoom factor, increase and decrease zoom factor

  • Protect worksheet and contents of locked cells

    Note that cell protection is set using the Format Cells tools.

The context menu contains the available Excel operations for the selected range.

  • Inserting selector tokens — One of the most common tasks performed when enabling a spreadsheet for use in Dodeca is to add selector tokens to cells as placeholders for point-of-view selections.

    The Selector Tokens tool allows you select a cell (or multiple cells) that you want to set to the token for a particular selector.

  • Name Manager — Used to create, edit, delete, and find all names defined in the workbook.


    The Name Manager is a modeless dialog, which allows you to interact with the application while the dialog remains open. To set the Refers to value, you can either key in the string or, for a range, select the range on the worksheet.

  • Essbase operations — The Essbase Excel View Template Designer supports the following Essbase operations and utilities:

    • Retrieve, keep only, remove only, zoom in, zoom out, and pivot

    • Essbase options (indentation, aliases, zoom level, etc.)

    • Member Select dialog

    • Undo/Redo adhoc operations

The context menu contains the Essbase operations along with the available Excel operations for the selected range.

  • Essbase Range Editor — Used to create, edit, and delete Essbase retrieve and send ranges.


    The Essbase Range Manager is a modeless dialog, which allows you to interact with the application while the dialog remains open. To set the Refers to value, you can either key in the string or, for a range, select the range on the worksheet.

    The Name Manager can also be used to manage the Essbase ranges, but the Essbase Range Editor is designed to facilitate retrieve and send range creation, including the ability to associate a specific Essbase connection with the range in the template.


Metadata Editors

  • Fixed Issue: When the Refresh button is clicked and there are unsaved metadata changes, the user is warned as asked whether to overwrite the changes. If the user chooses to continue, the metadata is refreshed. After the refresh is completed, the Commit button remains enabled. Clicking the Commit button generates an error.

Resolution: The Commit button is disabled after a refresh is completed. (#1093)

Relational Views (SQL Excel and Essbase Excel)

  • Fixed issue encountered when a database error, which is returned by the JDBC driver, includes embedded double quotes. This results in an XML parsing error message being returned to the client. (#1119)


  • Fixed issue with the Essbase Selector Treeview error "Unable to find the ancestors for "<dimension>". Unknown Member [].", encountered when the BaseMemberNodePolicy is set to AddBaseMemberAsDescendantNode and a selector list item is a dimension name. (#1111)

SQL Server JDBC Drivers

The SQL Server JDBC drivers shipped with the dodeca and dodeca-essbase servers have been upgraded to the latest version. The upgrade includes both the normal JDBC driver, sqljdbc.jar, which is for use with Java 1.5 servers and the Type 4 JDBC driver, sqljdbc4.jar, which is for use with Java 1.6 servers.

In addition, the sqljdbc4.jar has been made the default JDBC driver for both servers by renaming the sqljdbc.jar to sqljdbc.jar_use_for_java15. If your server uses Java 1.5, you must rename sqljdbc.jar_use_for_java15 to sqljdbc.jar and remove or rename the sqljdbc4.jar to have a different file extension. (#1121)

If you need more information or assistance related to the SQL Server JDBC drivers, contact

View Sharing (and Authentication Services)

  • In Version*, the ability for a user to share a saved view was introduced. When sharing is enabled for a given view, a saved instance of the view can be shared by the owner with Noone or with Everyone. An application that is configured with an authentication service can control whether saved views can also be shared with users in specific roles and/or with specific users. In Version, for the SQL-based authentication services, the roles/users included all the roles/users returned by a defined SQLPassthroughDataSet query. The functionality did not support the ability to restrict the roles and/or users that an individual authenticated user is allowed to share with.

    *Refer to the Saved Views section of the Version release notes for more information about saved view sharing.

    In version 6.3.0, the WindowsUserRolesFromSqlPassthroughDataSet and the EssbaseUserRolesFromSqlPassthroughDataSet support the ability to optionally filter the roles/users based on the authenticated user name. To support this capability, the following authentication properties were added: ** RolesToShareWithSQLPassthroughDataSetID — (Optional) The ID of the SQLPassthroughDataSet that defines the query used to obtain the roles with whom the authenticated user is allowed to share saved views. The roles are displayed in the User/Role dropdown list in the View Sharing dialog.

    + This setting is only required if you want to allow the user to select the role(s) with whom to share a saved view, which in effect shares the view with all users assigned the selected role.

    + To restrict the roles with whom the authenticated user can share views, the query’s SELECT statement can include a WHERE clause that contains the SqlPassthroughDataSet_UserNameVariable value within a condition.

    • UsersToShareWithSQLPassthroughDataSetID — (Optional) The ID of the SQLPassthroughDataSet that defines the query used to obtain the users with whom the authenticated user is allowed to share saved views. The users are displayed in the User/Role dropdown list in the View Sharing dialog.

      This setting is only required if you want to allow the user to select the users(s) with whom to share a saved view.

      To restrict the users with whom the authenticated user can share views, the query’s SELECT statement can include a WHERE clause that contains the SqlPassthroughDataSet_UserNameVariable value within a condition.

    • AllowSharingWithEveryone — Controls whether the authenticated user is allowed to share saved views with everyone.

      By default, the user is allowed to share saved views with everyone. The property is typically set to False when the RolesToShareViewsWithSqlPassthroughDataSetID and/or UsersToShareViewsWithSqlPassthroughDataSetID restrict the roles and/or users with whom the user is allowed to share saved views.

    • SqlPassthroughDataSet_UserNameVariable — The string within the RolesForUserSqlPassthroughDataSetID query SELECT statement to be replaced with the name of the authenticated user. This allows the user name to be used in the SELECT statement WHERE clause to query for only the roles associated with the authenticated user.

      The user name variable can also be used in the RolesToShareViewsWithSqlPassthroughDataSetID and UsersToShareViewsWithSqlPassthroughDataSetID query SELECT statements to restrict the roles and users with whom the authenticated user is allowed to share views.

This property was previously called RolesForUserSqlPassthroughDataSet_UserNameVariable. Although the name presented in the metadata editor has changed, any existing application metadata that specifies a value for this property is compatible with the 6.3.0 release. The property value will now be shown in the editor as the SqlPassthroughDataSet_UserNameVariable.

Here is a simple example that illustrates how to restrict the users and roles that the authenticated user is allowed to share with. For this example, let’s say that we want to allow the user to share a saved view with the roles to which the authenticated user is assigned and with the users who are also assigned to the same roles as the authenticated user.


The application is assigned the EssbaseUserRolesSqlPassthroughDataSet authentication service. Since we want to restrict the roles and users that the user is allowed to share with, the AllowSharingViewsWithEveryone property is set to False.

The *SqlPassthroughDataSet_UserNameVariable* setting is %USERNAME%.

This example uses a very simplified database model, where a single table contains a row for each mapping of a role to a user. A given user may be assigned to multiple roles.


Note: The Dodeca framework does not have any requirements related to the schema of the database used to store users and roles. The relational query can be defined as appropriate for the database within your environment.

To allow the user to share a saved view with only the roles to which the authenticated user is also assigned, the *RolesToShareWithSQLPassthroughDataSetID* is set to the same SQLPassthroughDataSetID as is the *RolesForUserSQLPassthroughDataSetID*. The Dodeca_User.RolesForUser SQLPassthroughDataSet query is defined as:


So, when the user "amy" logs at startup, the '%USERNAME%' is replaced with 'amy', and the results include the following roles: BusinessAnalyst, Executive, FinancialAnalyst, and Manager. (Amy has a lot of responsibilities!)

When she shares a saved view, the list of available roles presented in the view sharing dialog includes the results of the same query based on the *RolesToShareWithSQLPassthroughDataSetID* setting.

To allow the user to share a saved view with the users who are also assigned to the same roles as the authenticated user, the *UsersToShareWithSQLPassthroughDataSetID* is assigned the Dodeca_User.UsersToShareViewsWith, which defines the following query:





In this example, the results would include user2, user3, and user4.

The view sharing dialog allows the user to share with the roles returned by the RolesToShareWithSQLPassthroughDataSetID and the users returned by UsersToShareWithSQLPassthroughDataSetID.



  • Fixed issue with the Save As tool — When an application uses either the EssbaseUserRolesFromSqlPassthroughDataSet or WindowsUserRolesFromSqlPassthroughDataSetauthentication service and the AllRolesSqlPassthroughDataSetID or AllUsersSqlPassthroughDataSetID metadata property is not specified, the issue is encountered when a user attempts to use the Save As tool for a view that has sharing enabled. An "Object reference is not set to an instance of an object" exception is thrown. (#1120)

Workbook Script — Essbase MDX

The use of multi-dimensional expressions (MDX) as a means of querying Essbase cubes has become more common and widespread due to its flexibility and efficiency. To allow Dodeca administrators/developers to leverage MDX for generating view content, a new workbook script method, EssbaseMdxQuery, and a new BuildRangeFromScript overload, EssbaseMdxQuery, are available with this release in order to support the ability to run a custom Essbase MDX query and write the results to a worksheet.

The following example uses the BuildRangeFromScript method’s EssbaseMdxQuery overload to retrieve the data at the top of the worksheet, and the EssbaseMdxQuery method to retrieve the data at the bottom of the worksheet.


For the data at the top, the view template contains the page and column fields, and the

BuildRangeFromScript is used to build the row field. The template contains the retrieve range Ess.Retrieve.Range.1. With the method’s Insert argument is set to TRUE, the row members returned by the MDX query are inserted into the retrieve range. The view’s build processing retrieves the data.


The MDX-specific BuildRangeFromScript arguments include the following:

  • ScriptText — Specifies the MDX query, which can be tokenized. Note that token replacement occurs automatically, so it is not necessary to use the ReplaceTokens, @ReplTokens(), or TokenValue, @TVal() function.

  • HideRestrictedData — Controls whether data to which the user has no access is suppressed. By default, HideRestrictedData is TRUE; Restricted data is not returned.

    • IdentifierType — Controls whether member names, aliases, or unique names are returned by the query. The UniqueNames option is applicable when the outline contains duplicate member names. By default, IdentifierType is Name; Member names are returned.

    • RepeatRowMemberLabels — Controls whether member names are repeated for each row returned by the query. By default, RepeatMemberLabels is True.

    • BuildRowsOrColumns — Indicates whether row or columns are built from the query results.

      The MDX query is tokenized to filter the results based on the point-of-view selection for measures and scenario.

SELECT \{Year.Children, Year} ON COLUMNS,

NON EMPTY CrossJoin(\{East.Children, East}, \{Colas.Children, Colas}) ON ROWS FROM SAMPLE.BASIC

WHERE (, )

The data at the bottom is generated using the EssbaseMdxQuery method. As specified in the workbook script, only the rows and columns are returned to the client. This is appropriate since the Dataless argument is set, by default, to FALSE, so the method is responsible for pushing the data to the sheet. With no retrieve range, the build processing does not attempt to retrieve the data.


The HideRestrictedData, IdentifierType, and RepeatRowMemberLabels arguments are the same as described above. Other MDX-specific arguments include the following:

  • MDX — Specifies the MDX query, which can be tokenized. Note that token replacement occurs automatically, so it is not necessary to use the ReplaceTokens, @ReplTokens(), or TokenValue, @TVal() function.

  • Axis — Controls which of the axes of data generated by the query is returned to the client. By default, Axis is RowsColumnsAndPages; The data for the Rows, Columns, and Pages axes is returned to the client.

  • Dataless — Controls whether the MDX query returns data values along with the members, or members only. By default, Dataless is FALSE; Data values are returned.

Workbook Script Functions, Methods, Properties, and Event Links

Event Links

  • New Event Links: The Activated and Deactivated event links allow a workbook script to execute logic when a view’s window is activated or deactivate, respectively. (#1099)

  • AfterDataSetRangeBuild Event Link — Fixed issue with the event not being raised in an Essbase Excel view. (#1101)

  • Fixed issue with the EditCellChanged, ListDataValidationCellChanged, and RangeChanged event links: The event properties (Row, Column, CellAddress, SheetIndex, and SheetName) are set based on the active worksheet and active cell instead of the worksheet that contains the cell or range that changed. (#1124)


  • New Function: HasExcelComment determines whether a cell has an Excel comment. If a cell contains an Excel comment. (#1103)

    Function usage: @HasExcelComment([Address])

  • New Function: ExcelComment returns the comment text. (#1103)

Function usage: @ExcelComment([Address])

  • New Function: TokenIsNullOrEmpty returns True/False indicating whether the specified has a null value or the value is an empty string. If the token does not exist, returns True. (#1105)

    Function usage: @TokenIsNullOrEmpty(<TokenName>, [<UseTargetView>])

  • EssConnectionPropertyValue Function — Added the following Essbase cube properties that can be obtained using the EssConnectionPropertyValue function:






    For example, @EssConnPVal(Sample.Basic, EssCube.DateFormatString) returns the cube’s DateFormatString. (#1113)


  • New method: EssbaseMdxQuery — Refer to the section, Workbook Script — Essbase MDX. (#1110)

  • Fixed issue with CopyWorksheet and MoveWorksheet — When the SpecifyToPosition is ToPositionOfSheetNamed or ToPositionNumber, the sheet is always copied (CopyWorksheet) or moved (MoveWorksheet) to the first position regardless of the ToPosition value. (#1096)

  • Fixed issue with SetSelector — When the method hides a selector, the selector reappears when the active sheet changes. (#1112)

  • AddDataCache method — Fixed issue encountered when the AddDataCache method’s Shared argument is True, and then the OpenView method is used to open a view, the DataCache property in the target view is empty. (#1106)

  • BuildRangeFromScript method — Added an EnterDatesAsText argument, which controls whether the method attempts to convert each script result value to a date. By default, the argument value is True, which avoids the potential overhead incurred by the attempted conversions.

If the script returns data that contains dates and the dates need to be formatted on the sheet, then 1) the EnterDatesAsText argument value should be set to False, and 2) the appropriate cell number formats on the sheet should be set to Date. Please note that if the EnterDatesAsText argument value is set to False, but the appropriate cell number formats are not set to Date, the cells will contain the internal serial number representation of the date. (#1100)

  • Essbase BuildRangeFromScript method — Added EssMdxQuery overload. Refer to the section, Workbook Script — Essbase MDX. (#1109)

  • EssbaseRunCalc method — Added ProgressTextStarted, ProgressTextCompleted, ProgressTextFailed, and ErrorPropertyName arguments to allow custom progress text. (#1128, #1130)

  • ForEach method — Fixed an issue encountered when the executed procedure contains a method that starts an asynchronous operation (i.e. executes an operation on the background thread), such as the EssbaseRunCalc method when the BackgroundCalc argument is enabled. The procedure is only executed for every other iteration of the loop. For example, with the NumberToNumber overload, if the StartNumber is 1 and the EndNumber is 7, the procedure is only executed when the number is 1, 3, 5, and 7. (#1095)

  • SetOutlineLevel method — Added two overloads: ShowLevels and ShowDetail. (#1104)

    ShowLevels — Shows rows and/or columns with an outline level that is less than or equal to the specified level. The overload-specific arguments include RowLevels and ColumnLevels. The leave the levels unchanged, specify nothing or 0. To collapse all levels, specify 1 as the ColumnLevels or RowLevels argument value.

    ShowDetail — Shows/expands or hides/collapses the detail corresponding to the outline level of the row or column specified as the address.

  • SetConditionalFormat method — Fixed an issue encountered when the Type argument is set to Expression: the Minimum and Maximum argument values are not resolved correctly. (#1133)