March 30, 2012
This version uses SpreadsheetGear 2010 (6.0.3.190), NetAdvantage 2011, Volume 1 (11.1.20111.2042), and Aspose.Cells 7.0.3.0.
The release notes for this version contain the following sections:
Application Metadata
Application Metadata Editor
Commentary
Date Selector and Date Selector List
Essbase Connection Metadata
Essbase Operations
Metadata Editors
Relational
Saved Views
Session Cache
View Selector Explorer Bar and View Selector Tree
View to PDF Conversion
Workbook Script Metadata Editor
Workbook Script Functions, Methods, and Event Links
Workbook Script Tool Controller
Application Metadata
A new application setting, WorkbookScriptDebuggingEnabled, controls whether workbook scripting 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. This setting is used to prevent the possibility of the Workbook Script Debugger opening in a user application. (#1076)
Application Metadata Editor
- Fixed Issue: When an application with an authentication service is copied and an authentication property is modified for the new application, the same property is also changed for the original application. (#1051)
Commentary
Fixed Issue: When multiple sheets contain the same defined comment range name, an error similar to the following is generated:
"Error setting up comments. CommentRangeHandlerWorkbookView.SetupComments failed. WorkbookViewControlImplementation.SetCellValues failed. 'C1!'CommentArea is not a valid range name or reference for sheet C2." (#1061)
- Fixed Issue: When the comment contains an invalid XML character, an error similar to the following is encountered: "Unable to build the view. Error setting up comments. Unable to process CommentsQuery request. '', hexadecimal value 0x1A, is an invalid character. Line 1, position 265." (#1064)
Date Selector and Selector List
- Fixed Issue: The date assigned to the selector’s token is computed based on Coordinated Universal Time (UTC) (i.e. Greenwich Mean Time), which (depending on the locale of the Windows system) may result in a token value that represents the previous day to the selected date.
Resolution: The date assigned to the selector’s token is based on local time, which ensures that the token value represents the same day as the selected date. (#1043)
Fixed Issue: When the Admin application is run on Windows system that is configured with non-English regional date format settings, the date string entered for a date selector list’s DefaultSelectionString and NullLastUsedItemString generates an error if the format is not recognized as a valid date for the English (United Stated) locale. (#1042)
Resolution:
When an administrator defines a date selector list, the format of the specified date string for the MaxDate, MinDate, DefaultSelectionString, and NullLastUsedItemString settings should conform to the date formats of the locale used by the Windows operating system (on which the Admin application is running.)
The framework persists the date values to the metadata database using a neutral format that is not associated with any particular locale. This allows the actual date values to be retained and then converted to the appropriate format when the locale of the adminstrator’s Windows system changes as well as when the date selector list is used by a date selector within a view.
When the date selector list is used by a date selector within a view, the format of the date presented by the selector is determined by the view’s culture settings (i.e. CulturePolicy and CultureName.) In the screenshot below, the CulturePolicy for the Transaction by Supplier view is UseWindowsCulture. At runtime, the format of the dates presented by the selector is based on the regional format settings on the user’s system. Alternatively, when the CulturePolicy is set to UseSpecifiedCulture, the specified CultureName value determines the date formats.
Essbase Connection Metadata
- Two new settings have been added to the Essbase connection metadata. The settings, AdminPassword and AdminUsername, represent the credentials for a user who has administrator privileges. (#1038)
The credentials are required and used for the following:
- Beginning with Essbase 11.1.2.1.102, the Java API, which is used by the dodeca-essbase service, requires administrator privileges when obtaining information that requires opening the outline. To accommodate this change to the API, the client uses the AdminUsername and AdminPassword when requesting cube-related information.
- When an application uses the EssbaseUserRolesFromGroupNames authentication service and the view selector is configured to show saved views that are owned and shared by users that are members of a specific Essbase group, the client uses the AdminUsername and AdminPassword to request the names of all the users that are assigned to the group. The Java API is used by the dodeca-essbase service to obtain the users in a given group, and the API requires administrator privileges for this operation. This applies to Essbase versions 9.3.0 and later.
Essbase Operations
- Fixed Issue: If a user right-mouse clicks and drags a member outside the area where members are present in the grid, an error was raised indicating the row or column was outside the bounds of the grid. It was determined that the server code that limits the pivot 'drop' selection did not properly limit the upper bound of the row or column. The algorithm that sets the drop point of the pivot was modified to properly limit the drop cell to be within the bounds of the grid. (#1059)
Fixed Issue: In some instances, extraordinarily long error messages or stack traces were being presented to users in response to normal Essbase exceptions. (#1060)
Metadata Editors
- Addressed inconsistencies with hotkey usage. For instance, some hotkeys (Alt+A and Alt+C) performed multiple operations, such as Copy and Collapse all, and All (filter) and Admin (menu). The property filters (All, Common, Required) were added to several Metadata Editors, which previously did not support filtering. (#968)
- Commit confirmation supports a "Don’t show this message again." option. If this option is checked, the confirmation dialog is not shown again for any metadata editor during the current session or any future session. (#1073)
Added a Refresh tool to each metadata editor, which allows reloading all the metadata without having to close and reopen the editor. (#1074)
Relational
- This is potentially a BREAKING CHANGE.
For a SQLPassthroughDataSet, a SQL statement cannot contain a column name that is the same as the value specified as the DataTableName. This will result in the following error:
- Fixed Issue: When Excel outlining is enabled for an outer level of grouping and the grouping is configured to automatically show the detail rows, the "+" outline symbol is displayed next to each summary row. The screenshot below illustrates the issue. (#1044)
This screenshot illustrates the corrected outlining:
- Fixed Issue: Extra spaces or tabs in a SQL Insert, Update, and Delete statements may cause errors in the JDBC driver. To address this issue, the standardization performed by the Dodeca server in order to make the parsing of the parameters easier, such as converting new line characgters to a single space, was expanded to replace tab characters with a single space and to strip extraneous spaces. (#1055)
- Fixed Issue: In some cases, row errors from relational update, insert, or delete operations encountered on the server were not be returned to the client. (#1057)
- How to use a query’s IncrementalMaxRows property to optimize client and server memory usage and improve performance for large retrievals
By default, all the rows selected by a relational query are returned to the client at one time. If the query retrieves hundreds of thousands of rows, for instance, then a large amount of memory is required by the dodeca service, which runs inside the Java application server, to construct the XML, which contains the results returned to the client.
A query’s IncrementalMaxRows property can be used to limit the number of rows fetched by a single call to the server. When the total number of rows selected by a query exceeds the specified maximum number of rows, additional requests are made by the client until all the rows are fetched. For example, if the IncrementalMaxRows value is 100,000 and the query selects 345,000 rows, then the client incrementally sends four requests to the server. As one response is received, another is sent, until all the rows are received by the client. The database query is executed during the first request, and the dodeca service retains a database cursor to the results, which is used by each subsequent request and then released after all the rows have been sent to the client.
Typically, use of the IncrementalMaxRows property is recommended only when the total number of rows is likely to exceed 100,000 to 200,000. As a rule of thumb, set the property value to 100,000. However, each environment is different, and the best approach is to experiment in your environment to determine the optimal setting for a given query. For instance, if the query returns a large number of columns, you may need to set the value of the IncrementalMaxRows to a smaller number, since the number of columns also affects the size of the data.
*Note: The IncrementalMaxRows property only applies when the SQLPassthroughDataSet contains a single query. A SQLPassthroughDataSet that contains more than one query retrieves all the rows for all the queries in a single request to the server. As a rule, queries that potentially select thousands of rows should not be combined with other queries in the same SQLPassthroughDataSet.*
Another way to reduce memory usage and improve performance, especially when retrieving thousands of rows, is to use column aliases in the SELECT statement. For instance, this is a relational query used by the Dodeca Sample Application.
SELECT SUPPLIER.SUPPLIER_ALIAS,
CONVERT(char(10), SALES.TRANSDATE, 101) ,
MARKET.STATE, PRODUCT.SKU_ALIAS,
MEASURES.CHILD,
SCENARIO.SCENARIO,
SALES.AMOUNT
FROM SCENARIO INNER JOIN
MEASURES INNER JOIN
PRODUCT INNER JOIN
MARKET INNER JOIN
SALES INNER JOIN
SUPPLIER ON SALES.SUPPLIERID = SUPPLIER.SUPPLIERID ON MARKET.STATEID = SALES.STATEID ON
PRODUCT.PRODUCTID = SALES.PRODUCTID ON MEASURES.MEASURESID = SALES.MEASURESID ON
SCENARIO.SCENARIOID = SALES.SCENARIOID
Using this SELECT statement and setting the query’s DataTableName to "Transaction", the following illustrates the response XML for two of the rows generated by the query.
By changing the select statement to use single character column aliases, as shown below, and by also changing the query’s DataTableName to "T", the size of the XML returned to the client is significantly reduced, which reduces the amount of memory required to package the XML on the server and to read the XML on the client.
SELECT SUPPLIER.SUPPLIER_ALIAS AS S,
CONVERT(char(10), SALES.TRANSDATE, 101) AS D,
MARKET.STATE AS T,
PRODUCT.SKU_ALIAS AS P,
MEASURES.CHILD AS M,
SCENARIO.SCENARIO AS E,
SALES.AMOUNT AS A
FROM SCENARIO INNER JOIN
MEASURES INNER JOIN
PRODUCT INNER JOIN
MARKET INNER JOIN
SALES INNER JOIN
SUPPLIER ON SALES.SUPPLIERID = SUPPLIER.SUPPLIERID ON MARKET.STATEID = SALES.STATEID ON
PRODUCT.PRODUCTID = SALES.PRODUCTID ON MEASURES.MEASURESID = SALES.MEASURESID ON
SCENARIO.SCENARIOID = SALES.SCENARIOID
Saved Views
- Display and user management of saved views
The ability for a user to save a view is a capability already supported by Dodeca. This capability is enabled for a given view by setting the view’s AllowSave property to True, and by also using a toolbars configuration that contains the Save As and Save tools.
The Open View tool can be used to view the current user’s saved views, and the Manage Saved Views dialog can be used to open, rename, and delete the current user’s saved views. Both of these tools are accessed from the File menu on the application’s main menu bar.
As an enhancement implemented for this release, the current user’s saved views can be listed in the view selector, from which the user is able to open, rename, and delete a saved view.
To show the current user’s saved views in the view selector, add a SavedViewList item to a hierarchy and set the OwnerFilter property to CurrentUser, which is the default setting. The item serves as a placeholder. At startup, as well as when the user refreshes the view selector, the SavedViewList item is replaced with items that represent the user’s saved views.
The Manage Saved Views dialog is still supported, although saved views can only be shared from the Save As dialog and from the View Selector. Opening, renaming, and deleting saved views can be performed from both the Manage Saved Views dialog and the View Selector. Deleting multiple saved views at a time is supported by the Manage Saved Views dialog.
Sharing saved views with other users
Prior to this release, a user was able to save a view, but was not able to share the saved view with other users. The sharing of saved views is now supported, and can be enabled for a view by setting the view’s AllowSharing property to True. When sharing is enabled, a user is allowed to share a saved instance of the view with everybody (i.e. all other users) or with specific users (or users in specific roles/groups). The view can be shared at the time the user saves the view, and the view can also be shared (or the sharing modified) from the view selector.
To share the view at the time the view is saved, the user can click the "Share with" button on the Save As dialog, which opens the View Sharing dialog, as shown in the screenshot below.
To share the view (or modify the sharing) from the view selector, the user can click the saved view with the right mouse button and then select the "Share with--" item from the context menu.
View Sharing and Authentication Services
The ability to share a saved view with specific users, or with users who are assigned specific roles, is supported in conjunction with an authentication service. When no authentication service is associated with an application, saved views can be shared, but only with Noone or with Everyone. In this case, the "Specific people--" option is not visible on the context menu.
New properties have been added to the authentication services specifically for use with view sharing. The following section describes these settings.
View Sharing and the WindowsUserRolesFromSqlPassthroughDataSet or the EssbaseUserRolesFromSqlPassthroughDataSet Authenication Service
Both of these authentication services can be configured to query a database for the list of available users and roles, as well as the roles to which a given user belongs and the users assigned to a given role.
The following settings, which apply to both of the SQL-based authentication services, specify the ID of a SQLPassthroughDataSet:
- AllRolesSqlPassthroughDataSetID — Defines the query used to obtain all the roles as defined in the database, which are used to 1) populate the Role dropdown list in the View Selector’s HierarchyToRoleMapping dialog, and 2) populate the User/Role dropdown list in the View Sharing dialog. This setting is required if you want to allow users to specify which role(s) to share a saved view with, which in effect shares the view with all users assigned the specified role.
- AllUsersSqlPassthroughDataSetID — Defines the query used to obtain all the users as defined in the database, which are used to populate the User/Role dropdown list in the View Sharing dialog. This setting is required if you want to allow users to specify which user(s) to share a saved view with.
RolesForUserSqlPassthroughDataSetID — Defines the query used to obtain the authenticated user’s role(s).
Since the user’s name is not known until runtime, the query’s SELECT statement should include a WHERE clause that contains the RolesForUserSqlPassthroughDataSet_UserNameVariable value within a condition. For example, if the RolesForUserSqlPassthroughDataSet_UserNameVariable is %USERNAME% and the name of the column that contains the user name is UserName, the WHERE clause would be WHERE UserName = '%USERNAME%'.
This setting is required if you want to leverage the view selector’s HierarchyToRoleMapping capability, which allows you to control which view hierarchy or hierarchies are presented in the view selector based on the authenticated user’s role membership.
This setting was previously called WindowsUserRoleSqlPassthroughDataSetID and the variable setting was called WindowsUserNameVariable. Although the names presented in the metadata editor have changed, the metadata defined in previous releases is compatible with the 6.2 release.
UsersInRoleSqlPassthroughDataSetID — Defines the query used to obtain the users who are assigned to a given role.
Since the role name is not known until runtime, the query’s SELECT statement should include a WHERE clause that contains the UserInRoleSqlPassthroughDataSet_RoleNameVariable value within a condition.
For example, if the UsersInRoleSqlPassthroughDataSet_RoleNameVariable is %ROLENAME% and the name of the column that contains the role name is RoleName, the WHERE clause would be WHERE RoleName = '%ROLENAME%'.
The SQLPassthroughDataSet is only used when a SavedViewList item is included in the view hierarchy and the item’s OwnerFilter is set to SpecifiedUsers and a role(s) is specified. At runtime, the SavedViewList item is replaced with all the shared views that are owned by users assigned to the specified role(s), which requires that all the users assigned a specified role be obtained.
For the WindowsUserRolesFromSqlPassthroughDataSet authentication service, the IncludeDomainWithUserName property has been added. This setting controls whether the domain is prepended to the name of the Windows user. By default, the current user’s Windows logon name is in the form DOMAIN\USERNAME. To exclude the domain, set IncludeDomainWithUserName to False.
_Note: If you set the IncludeDomainWithUserName to False, the USER_ID values in the BINARY_ARTIFACTS table for the existing saved views will need to be modified to strip off the domain name. _
Similarly, for the EssbaseUserRolesFromSqlPassthroughDataSet authentication service, the USER_ID values in the BINARY_ARTIFACTS table for the existing saved views may need to be modified to strip off the domain name. This is necessary when you want users to be able to share saved views with specific users or all users in specific roles, and the user names returned by the AllUsersSqlPassthroughDataSetID do not include the domain name.
View Sharing and the EssbaseUserRolesFromGroupNames Authentication Service
This authentication service has two new settings, which are used in conjunction with view sharing. Internally, the authentication service is used to obtain a list of all the Essbase groups and all the Essbase users. The lists are used to populate the User/Role dropdown list in the View Sharing dialog. The new settings, AllUsersExternalOnly and AllUsersExternalProtocol, are used when the list of all users is obtained.
The AllUsersExternalOnly controls whether the list of users contains only external users or all defined users. The AllUsersExternalProtocol controls the external authentication protocol used when obtaining external users.
View Sharing Considerations
Since saved views are stored in the metadata database’s BINARY_ARTIFACTS table, saved views are exported when the Export Metadata to a Local Zip File is used. The view sharing information is stored in the VIEW_ACCESS table, which is NOT exported with the metadata.
If you need assistance with an application that already uses an authentication service and you want to utilize any of the capabilities described in this section, contact us at support@appliedolap.com.
Session Cache
- Fixed Issue: When multiple instances of the same client application are open, the session cache is shared by all the applications. (#1049)
View Selector Explorer Bar and View Selector Tree
- Automatically restores the view selector size, location, and expansion
The size and location of the view selector control is now automatically captured when the application is exited, and restored when the application is started. For the View Selector Explorer Bar, the group (i.e. the category) expansion is also captured and restored. Similarly, for the View Selector Tree, the node expansion is captured and restored. When the user refreshes the content of the view selector by selecting the Refresh button on the context menu, the size and group (or node) expansion is retained. (#1040)
- *Export Excel Template Context Menu Option *
A view’s Excel template can now be exported directly from the view selector. When the ViewSelectorProperties AllowImportExcelTemplate setting is True, an "Export Excel Template" item is displayed on the context menu if the view is assigned an Excel template binary artifact. This menu item is only displayed for an Excel, a SQL Excel, or an Excel Essbase view. (#1066)
This capability is supported for both the ViewSelectorExplorerBar and the ViewSelectorTree. It is intended for use with an Admin application as a way to quickly export a view’s Excel template binary artifact directly from the view selector. By default, the option is not shown in order to avoid unintentionally exposing this capability to end users.
To enable this capability for an application, you must set the view selector’s AllowExportExcelTemplate setting to True.
View to PDF Conversion
Fixed issue with invalid numeric formatting in the output PDF:
- A cell is formatted as a number, with negative numbers displayed as red and enclosed in parentheses
The formatted cell contains a formula that references a cell that contains a negative number AND is hidden.
Under these conditions, the cell value displays in the PDF with a negative sign instead of being enclosed in parentheses. For example, as -2000000 instead of (2000000). (#1071)
This issue is resolved with Aspose.Cells 7.0.3.0.
Fixed issue with custom formatting, #,##0.0,,_);(#,##0.0,,);-_), that aligns positive numbers with negative numbers in parentheses by padding positive numbers with a blank to the right: (#1072)
This issue is resolved with Aspose.Cells 7.0.3.0.
Workbook Script Metadata Editor
Automatically restores the workbook script layout
The layout of a workbook script is now automatically captured when the script is edited, and restored when the workbook script is re-selected. A layout is cached locally and is preserved across sessions. The layout consists of the following: The expansion state (i.e. whether expanded or collapsed) of the Definition, Properties, Event Links, and Procedures areas. The widths of the columns in each of the grids. The top row in the Properties, Event Links, and Procedures grids. The expansion state of each procedure and method in the Procedures grid.
- Debug mode option that supports debugging specific procedures
To debug a workbook script, you can now either enable debugging for all procedures or for specific procedures by setting the Debug Mode.
Debugging is enabled for a specific procedure by checking the box located to the left of the procedure name in the Procedures grid.
In this example, the debugger is automatically opened when the AfterBuildSetup procedure is executed. Debugging can be disabled for the procedure from within the debugger using the Enable Procedure Debugging state button.
If you are not familiar with the workbook script debugger, refer to the Workbook Script Debugger section of the Version 5.3.0.2646 Release Notes for more details.
Formula Editor dialog enhancements
The Formula Editor dialog can be used to edit argument values within the Workbook Script Editor and the Workbook Script Debugger. The dialog can be accessed when an editable value is selected by pressing the Shift+F2 keys or by clicking the Edit Text tool on the toolbar or context menu.
The enhancements to the dialog include the following:
- The size and location, as well as the font name and size, are retained and restored. (#1069)
When mismatched parentheses are detected, an error message is displayed, which indicates the number of opening and closing parentheses. Parentheses that are inside of double quotes are not included. (#1070)
Workbook Script Functions, Methods, and Event Links
- Fixed issue with BuildRangeFromScript method — When the Insert argument value is True, an extra blank row is inserted below the data rows. (#1048)
- Fixed issue with MemberName, @MbrName, and other Member<> functions -- When the member string represents an alias and the alias is used by multiple shared members, the function generated the error "An item with the same key has already been added." (#1047)
- Fixed issue with SendEmail method — The General overload throws an exception when multiple email addresses are specified. (#1063)
- Fixed issue with the SetActiveWorkbook method — The method fails to find the specified workbook when the character casing (i.e. upper and lower characters) of the loaded workbook’s name is different than that of the specified workbook. (#1046)
- Fixed issue with the SetChart method — The PointProperties overload incorrectly interprets the PointIndex argument. (#1068)
- Fixed issue with SetFormulaBar method — When the method is used to set the visibility of the formula bar for an Excel Essbase view from the AfterSheetChanged event, the setting does not stick. (#1062)
- Fixed issue with SetImage, SetLine, and SetShape methods, which failed to find the specified image, line, or shape, and were unable to perform most of the override options other than General. (#1054)
- Fixed issue with the WorksheetEvalulate function, @WSEval — The function generated an error when the specified Excel formula contained an Excel function. (#1052)
The @WSEval function can now be used to evaluate an Excel formula. Since @WSEval is the only WBS function that allows an Excel formula as the argument, the function can be used as an argument to other WBS functions, which effectively allows you to specify an Excel formula as the argument for any WBS function. Also note that the formula argument can also contain other workbook script functions.
- SetSelector Method — Added BringSelectionIntoView and ResetControl arguments. For the selector treeview and listbox, the BringSelectionIntoView argument controls whether the selected item (or first selected item) is within the viewable area of the control. For the selector treeview, these settings determine whether the expansion of the nodes is restored to the initial state. (#1045)
- New Function: The EssbaseDatabaseNote, @EssDatabaseNote([<connectionID>]), function returns the database note currently attached to an Essbase cube. If the connection ID is not specified and the view is an Essbase view, the note attached to the Essbase database associated with the view is returned. (#1058)
- New Method: The CloseApplication closes the client application. (#1054)
- New Method: The SetActiveSheet method activates the sheet specified by the SpecifySheetBy and SheetSpec arguments. (#1050)
Workbook Script Tool Controller
- Added CheckedInitializeProperty tool controller argument, which specifies the name of the boolean workbook script property used to set whether a state button tool is checked or unchecked. If the property’s value is true, the tool is checked. The state button tool’s checked state is initialized using the property’s value and the checked state is also automatically updated when the property’s value changes.
The difference between the CheckedInitializeProperty and the CheckedProperty arguments is that the CheckedInitializeProperty does not trigger the tool click event. In most cases, you simply want to set the checked state from the workbook script without executing the procedure specified by the ProcedureName argument. (#1067)