Build 1749

September 16, 2009

Comments

KeyItemsConditionString is a string that evaluates to True or False. It controls whether the KeyItemsString is evaluated for any given cell within the comment range. When used, the KeyItemsConditionString is typically a formula.

Let’s say, for example, that the comment range contains blank and locked cells that are not intended to have associated comments, and therefore should not have any associated key items. To exclude these cells, the KeyItemsConditionString can be set to the following:

=Not(Or(IsBlank(@ACell()), @CellIsLocked()))

When a given cell is blank or locked, the condition evaluates to False and the KeyItemsString is not applied to the cell.

Alternatively, the same condition can be included in the KeyItemsString formula using the Excel IF function. For example, using the following, when a cell is blank or locked, the key items string evaluates to an empty string:

=If(Or(IsBlank(@ACell()), @CellIsLocked()), "", "<key items>")

In cases where many of the cells within the comment range are excluded from having comments, performance can sometimes be improved by using the KeyItemsConditionString instead of including the same condition in the KeyItemsString.

dodeca Servlet and dodeca-essbase Servlet

xercesImpl.jar (DOM Parser)

xml-apis.jar (XML Parser supporting classes)

xalan.jar (XSLT support classes)

jta.jar (javax.transaction classes)

These jar files are included in the WEB-INF\lib directory and may be removed if your application server already includes them in its distribution.

Note: xercesImpl.jar replaced the older xerces-2.6.2.jar and xml-apis.jar replaced a slightly older version. The files added are from the Oracle distribution of Tomcat 5.5.17. (#745, #746)

Essbase 11.1.1.3

Please contact support@appliedolap.com to request an 11.1.1.3 compatible version of the dodeca-essbase servlet. (#744)

Essbase Excel View

Excel 2007

The Excel 2007 file format is now fully supported for the Excel and the Excel Essbase view types. (#760)

Excel View and Essbase Excel View

In Excel, function arguments in formulas are separated by the character that is defined as the List Separator in the Regional Options settings for the local Windows environment. The character is typically a comma or a semicolon. Excel automatically converts the argument separator in an Excel file to the list separator in effect when the file is opened.

In Dodeca, the CultureName setting determines the list separator that is used for functions in Excel formulas for a given view, regardless of the regional settings of the user’s Windows environment. The list separator that is defined for the view’s culture, such as English (United States) or French (France), is handled by the view in the same way that Excel handles the list separator defined by the regional settings. Any formulas defined in the view’s Excel template are automatically converted to use the view culture’s list separator as the function argument separator. Any formulas entered by the user into the view’s spreadsheet must also use the view culture’s list separator. Any Excel formulas in the workbook scripts that are used by the view must also use the view culture’s list separator as the function argument separator.

By default, the CultureName is set to "English (United States)". The formula function argument separator is a comma.

Depending on the particular use case, the view’s CultureName property can be set at design time or set at runtime using a workbook script. For instance, if a view is used by users in different countries and users are able to enter formulas into the view’s spreadsheet, it may be preferable to set the view’s CultureName to be consistent with the regional settings of the Windows environment. This allows users to use the argument separator they are accustomed to using in Excel.

To set a view’s CultureName property at runtime, use the AfterConstruct event link to set the view’s CultureName using the @WindowsCultureName() function:

In this scenario, it is also important to create workbook scripts that are not written for a specific culture. To create a culture-neutral workbook script, use the @SEP() function in place of function argument separators in Excel formulas. For instance, instead of using a comma as the argument separator in the Address formula:

=Address(@LRow(), @LCol())

Use the @Sep() function, which is replaced at runtime with the list separator for the view’s culture. So, for example, if the view’s CultureName is "French (France)", the function returns a semicolon.

=Address(@LRow()@Sep()@LCol())

Caveats:

There are a couple of considerations when deciding whether to allow copying and cutting from the view to the clipboard. When the copied range is pasted into an Excel spreadsheet, any hidden rows and/or columns within the copied range are automatically unhidden in Excel. However, setting AllowCopyCutToClipboard to False prevents a user from copying and pasting within a view and also from copying from one view and pasting into another view.

Both the AllowFindAndReplace and BackgroundThreadWorkbookCalculation properties appear in the Behavior category for an Excel View and an Excel Essbase View. The following is a screenshot of the Excel View’s Behavior properties:

Workbook Scripting

The Close method allows a workbook script to close the view. (#748)

By default, the @DPDimValsMbrs, @DPDimValsAliases, and @DPDimValsDisp return a delimited list of all the contributing dimension/member pairs for a specified datapoint.

For example, the DataPointDimValuesMembers function can be used to return all the dimension/member pairs for the active cell:

@DPDimValsMbrs()

Using an example datapoint in the Sample Basic database, returns:

Year=Jan;Measures=Sales;Product=100-10;Market=New York;Scenario=Budget

The optional Dimensions parameter allows the script to specify the dimensions to be included in the result. For instance, the Dimensions parameter in the following example specifies that only the Market, Product, and Scenario dimension/member pairs should be included in the result. (Note that to use the default cell address, alias table, and delimiters, the parameters are not specified, but each must be denoted with a comma.) So, the function:

@DPDimValsMbrs(, , , , Market;Product;Scenario)

Using the same datapoint as the example above, returns:

Market=New York;Product=100-10;Scenario=Budget

Note: These functions can be used to generate part or all of the key items string for a comment range. However, it is always more efficient to get the key item values directly from the sheet when possible.

DataPointDimValuesMembers

DataPointDimValuesAliases

Other datapoint related functions worked properly if the CellAddress argument was not specified, but the CellAddress argument was not documented as optional. The documentation was corrected for the following functions:

DataPointDimLevel

DataPointDimGeneration

DataPointDimOrientation

DataPointDimValueDisplayed

DataPointDimValueMember

DataPointDimValueAlias

DataPointDimValuesDisplayed

DataPointIsBlank

DataPointIsDouble

DataPointIsError

DataPointIsMissing

DataPointIsNoAccess

DataPointIsZero

DataPointIsReadOnly

DataPointIsReadWrite

Note: The AliasTable argument of several of these functions have also changed to indicate that the argument is optional. (#754)

The PrintToFile overload allows a view to be saved to a Microsoft XPS file. (Currently, saving to a PDF is not supported.)

Arguments include:

PrintWhat — Options include Selection, Sheet, and Workbook

PrinterName — Currently, only "Microsoft XPS Document Writer" is supported.

Filename — The base name and extension of the output file. The value can include workbook script functions and/or Excel formulas.

FolderOption — Options include MyDocuments or Desktop. If no option is specifed, the Folder argument is used.

Folder — When the FolderOption is not specified, indicates the path of the target folder where the output file is saved. The value can include workbook script functions and/or Excel formulas.

IncrementFilename — When True, the base file name is a appended with a "(<number>)" if a file with the same name already exists in the target folder. For example, if the output file name is "Income Statement.xps", and a file with that name already exists, the output file is named "Income Statement (2).xps".

The following example illustrates using the PrintToFile overload to save a view’s workbook to a file that uses the view’s ID as the base file name with an .xps extension. The file is saved to the MyDocuments folder.

MemberCellDimensionNumber

MemberCellIsDimension

MemberCellIsExplicitlyShared

MemberCellIsImplicitlyShared

MemberCellIsLabelOnly

MemberCellIsNeverShare

MemberCellIsParent

MemberCellIsStoredData

Dodeca Framework Build 1712

August 18, 2009

The version of SpreadsheetGear has been upgraded from 4.0.11.106 to 5.1.1.112. (The SpreadsheetGear WorkbookView user control is the spreadsheet user-interface component used for Excel-based views.) Among the new features are the following:

Printing Tools

Print Preview Sheet, Print Preview Workbook, and Print Preview Selection (#739)

The previous Print and Print All Worksheets tools (although still supported) have been replaced with tools that print the active sheet, the entire workbook, or a selection. An Essbase Excel view or an Excel view can now be configured with a ViewToolbarsConfigurationID that references a toolbar configuration that contains the Print popup menu tool as well as the Print Preview popup menu tool. These popup menu tools are configured as segmented popup menu tools, which behave like a button when the tool’s icon is clicked and like a menu when the dropdown arrow is clicked. Clicking the icon is the same as clicking the Print Sheet tool (for the Print popup menu tool) and Print Preview Sheet tool (for the Print Preview popup menu tool) from the dropdown list.

An Essbase Excel view or an Excel view can also be configured with a MergeableToolbarsConfigurationID that references a toolbars configuration that contains the File / Print and Print Preview menus, which contain the related print and print preview tools.

In keeping with the Excel paradigm, when a print tool is selected from the view’s toolbar, the print dialog is not displayed; however, when a print tool is selected from the File/Print menu, the print dialog is displayed.

These tools have been added to the appropriate toolbars configurations in the metadata starter kit. If the starter kit was used to seed a tenant’s metadata and if changes have not been made to the toolbars configurations, then the toolbars configurations can be imported from the metadata_starter_kit.zip installed in the installation path, such as C:\Program Files\Applied OLAP\Dodeca Framework 4.1.0.0\metadata, which will overwrite the existing toolbars configurations.

If changes have been made to the toolbars configurations, such as by adding custom context menus, then the new print tools can be added to an existing toolbars configuration without overwriting the existing toolbars configurations.

The following describes the steps necessary to add the Print and Print Preview menus to a mergeable toolbars configuration, such as Essbase View Main All:

The Print method provides two overloads: Print and Preview. Both overloads support a PrintWhat argument, which can be set to Sheet, Workbook, or Selection. The Print overload also supports a ShowDialog argument, which controls whether the Print dialog is displayed. (#740)

Dodeca Framework Build 1673

July 30, 2009

Application

The following Essbase authentication services are now supported out of the box: EssbaseUserRolesFromSqlPassthroughDataSets and EssbaseUserRolesFromGroupNames

When an application is configured with either of these services, the user is prompted at startup for an Essbase user name and password. The service authenticates the user by attempting to connect to the Essbase database that is configured for the service.

With the EssbaseUserRolesFromSqlPassthroughDataSet* service, the service can optionally be configured to query a relational database for the role or roles to which the user is assigned.

With the EssbaseUserRolesFromGroupNames service, the names of the Essbase groups to which the user is assigned are obtained. (#715)

*The EssbaseUserRolesFromSqlPassthroughDataSets service provides the same functionality as the EssbaseAuthenticationService, which is supported by the EssbaseAuthentication module. The module was previously included with the metadata_starter_kit.zip. If you are currently using the EssbaseAuthenticationService, we recommend that you start using the EssbaseUserRolesFromSqlPassthroughDataSets instead, and remove the EssbaseAuthentication module from your application(s).

Please note that the EssbaseUserRolesFromGroupNames service requires an updated dodeca-essbase servlet and is only supported for Essbase versions 9.3.0 and higher.

Resolution: A more informative error message is now presented, such as "Unable to initialize the view selector. Unable to get the "<hierarchy ID>" hierarchy metadata. The ViewSelectorTree is configured with the hierarchy "<hierarchy ID>", which is not an existing hierarchy ID." (#717)

Comments

A comment range contains discontiguous areas. A comment range can only contain a single area. CommentRange.Caption = "<CommentRange.Caption>", CommentRange.Address = "<CommentRange.Address>". (#707)

Resolution: A case-insensitive comparison is now used to determine whether the actual range name matches the configured range name. (#703)

Resolution: When BaseKeyItems are defined and no KeyItemsString is specified, the key items for each cell (or each group of merged cells) within the range are the same. (#706)

dodeca Metadata Servlet

dodeca-essbase Servlet

Essbase Connections Metadata Editor

Essbase Selector Metadata Editor

Essbase Send Tool

Excel Essbase View

Resolution: Hidden columns within the retrieve range are not auto-fit and remain hidden. (#704)

Excel Formatting Tools

Resolution: Modified the approach to be consistent with the equivalent Excel tools, which adjust the decimal places based on the active cell’s number format and then set the number format of all the cells in the selected range to the modified format assigned to the active cell. This approach also resolved the issue encountered when the selection includes a large number of cells. (#702)

Metadata Editors

View Metadata Editor

Workbook Scripting

The following events are raised during the import processing:

BeforeExcelImport, AfterExcelImport, BeforeImportWorksheet, and AfterImportWorksheet

A "WorkbookName" event property has been added for each of these events, which allows a workbook script to use Excel external references to access the Excel file, such as with the following formula:

='[@EPVal(WorkbookName)]Input'!@ACell()

The @EPVal(WorkbookName) evaluates to the name of the external workbook, which is resident in memory during the import, so the workbook is treated like an opened external workbook in Excel. So, for example, this allows a workbook script to copy values, formulas, and/or formatting, etc. from the external workbook into the view’s workbook. The script can also prevent the default processing* from being performed by using the CancelEvent method from within the BeforeImportWorksheet event link.

*By default, the data within each send range is copied from the external workbook to the view’s workbook.

By way of example, the following workbook script illustrates this functionality:

Let’s say that when a specific sheet is imported that you want to set the value of each unlocked cell within a specific range in the view’s workbook to the value from the corresponding cell in the external sheet. The DoBeforeImportWorksheet in the screenshot below is configured accordingly:

The MethodCondition uses the BeforeImportWorksheet event property "SheetName" to restrict the processing of this method to only the Input sheet. This is important when the workbook contains multiple sheets.

=IF ("@EPVal(SheetName)"="Input", TRUE, FALSE)

The CellCondition excludes locked cells based on whether the cell in the view’s workbook is locked.

The Entry uses the BeforeImportWorksheet event property "WorkbookName" to reference the external workbook that is being imported. The value is obtained from the same cell location in the external worksheet as the current cell within the CellByCell loop.

='[@EPVal(WorkbookName)]Input'!@ACell()

Using the CopyRange method with the Specify overload could be used to copy an entire range from the external workbook by using an external reference for the CopyRange argument; a local range within the view’s workbook as the PasteRange; and the PasteType (i.e. All, Formats, Formulas, Values, etc.) to control what gets copied.

Note: The Before/AfterImportWorksheet events also expose a SheetName event property, which can be accessed using @EPVal(SheetName).

Dodeca Framework Build 1603

June 8, 2009

Application

Excel Essbase View

+ ^ $ . | \{ } ( ) * ?

(#684)

TitleTop is the text displayed as the title at the top of the chart within the chart control area. The value can be tokenized.

SheetName is the name of the sheet that contains the data to be represented by the chart. The SheetName setting is used instead of the RetrieveRangeName when the "used range" of a worksheet represents the Essbase retrieve range. To configure a chart to represent the used range of a worksheet, use the SheetName to specify the name of the worksheet, and leave the RetrieveRangeName blank.

The RetrieveRangeName is not a new property, but the description has been updated per the addition of the SheetName property.

RetrieveRangeName is the defined range name (i.e. Ess.Retrieve.Range.<identifier>) that represents the Essbase retrieve range that contains the data to be represented by the chart. When the chart represents a sheet-level range, the range name should either include the worksheet name as part of the range name by using the Excel defined name convention, such as 'Market by Quarter'!Ess.Retrieve.Range.1 OR use the SheetName property to specify the name of the worksheet. (#691)

Excel Essbase View, Ad hoc Essbase View, and Script Essbase View

Export Metadata to Local Zip File Utility

Toolbars Configuration Metadata Editor

View User-Interface

Workbook Script

Workspace Layout

For an application for which either of the following is true, any existing workspace layout will have to be recreated.

\1. The ViewSelectorCaption value is set to something other than the default value, which is an empty string.

\2. The ViewSelectorCaptionTab is set to something other than the default value, which is "View Selector"

If the application’s WorkspaceLayoutBinaryArtifact setting is assigned a workspace layout, the view selector may not be docked properly when the application starts up. If not, use the Application Setup Utility to clear the WorkspaceLayoutBinaryArtifact setting. You should then be able to startup the application, recreate the workspace layout binary artifact, and then assign the new layout to the application. (#688)

Dodeca Framework Build 1569

May 7, 2009

Comments

Excel Essbase View

View Selectors

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 access a view’s Excel template binary artifact directly from the view selector. By default, the setting is False to avoid unintentionally giving end users access to the metadata.

Workbook Scripting

Dodeca Framework Build 1552

April 21, 2009

Comment Range

Comments Explorer

Essbase Login Service

Metadata Editors

Selector Listbox (and Essbase Selector Listbox)

Workbook Script Metadata Editor

Dodeca Framework Build 1527

March 31, 2009

Comments

Toolbars Configuration MetadataEditor

View Hierarchies Metadata Editor

Workbook Scripting

Dodeca Framework Build 1498

March 9, 2009

Application Metadata Editor

Toolbars Configuration Metadata Editor

View Hierarchy Metadata Editor

Workbook Scripting

Workbook Script Metadata Editor

Dodeca Framework Build 1483

February 25, 2009image::image504.jpeg[image,width=16,height=16]

View Selector Tree

Dodeca Framework Build 1481

February 24, 2009

The version of SpreadsheetGear has been upgraded from 4.0.8.104 to 4.0.11.106.

Application

At runtime, the image is scaled only when the width of the view selector is less than the width of the image.

This capability is intended for use with an Admin application as a way to quickly access a view’s metadata or workbook script settings directly from the view selector. By default, the setting is False to avoid unintentionally giving end users access to the metadata.

Comments

Comments are supported for the Excel View and Essbase Excel View types.

Essbase Excel View

An example of the previous error message:

Unable to build the view. Unable to open Excel file "C:\Documents and Settings\user\Application Data\Applied OLAP, Inc.\Dodeca\4.1.0.0\SAMPLE.GENERAL.Test.1". Unrecognized file type.

Has been replaced with:

Unable to open the Excel template "C:\Documents and Settings\user\Application Data\Applied OLAP, Inc.\Dodeca\4.1.0.0\SAMPLE.GENERAL.Test.1". The file size is 0 bytes. Verify that the binary artifact "Test, Version 1" contains the Excel file.

Essbase Views

Hierarchy Metadata Editor

Selector ComboBox

SelectorListBox, EssbaseSelectorListBox, and EssbaseSelectorTreeView

The following screenshots show a few examples of different layouts using the SelectorDockedControlGroupStyle and SelectorDockedControlLocation settings. This first screenshot shows the selectors stacked and docked on the right. The second screenshot shows the selectors side-by-side and docked on the bottom. And, the last screenshot shows the selectors displayed with sliding headers and docked on the right. (#645)

Essbase Selector TreeView

When base members are added as descendant nodes, the ancestor nodes are automatically added to the tree. The BaseMemberAsDescendantNode_RootNodeMemberPolicy setting controls which member (or members) is used as the root node. By default, the root node is the dimension. Alternatively, the root node can be based on generation, level, or member name. The options include UseDimension, UseSpecifiedGenNumber, UseSpecifiedGenName, UseSpecifiedLevelNumber, UseSpecifiedLevelName, and UseSpecifiedMemberName. When the policy is UseSpecifiedMemberName, the BaseMemberAsDescendandNode_RootNodeMemberValue setting specifies the member name(s).

The RootNodeExpansionPolicy includes a new option, ExpandAllRootNodesAndPrefilledDescendantNode. All root nodes along with all prefilled descendant nodes are expanded. This option is intended for use when the BaseMemberNodePolicy setting is AddBaseMemberAsDescendantNode and you want the tree to automatically expand to show the base members. This option can also be used when the Prefill setting is True and you want the tree to automatically expand to show all the nodes. Since prefilling the tree and automatically expanding all nodes increase the amount of time the tree takes to display, the total number of members represented by the tree should be taken into account before enabling these options.

For instance, you can limit the members displayed in the tree based on a UDA. For example, with the SelectorListObjectTypeID set to EssbaseReportScript, and the report script defined to return all the Market members that are assigned the Small Market UDA:

<ROW (Market)

<LINK (<UDA (market, "Small Market"))

!

And, with the following SelectorControlProperties settings:

BaseMemberNodePolicy = AddBaseMemberAsDescendantNode

BaseMemberAsDescendantNode_RootNodeMemberPolicy = UseSpecifiedDescendantNode BaseMemberAsDescendandNode_RootNodeMemberValue = 2

RootNodeExpansionPolicy = ExpandAllRootNodesAndPrefilledDescendantNode

The base members are all Small Market members and the root nodes represent the 2^nd generation members that are ancestors of Small Markets, which happens to be all the 2^nd generation members in the Market dimension. If West, for example, did not contain a Small Market, there would be no root node for West.

This is only one example. The base members can be generated based on an EssbaseDelimitedString, an EssbaseReportScript, an EssbaseMemberQuery, or an EssbaseSQLPallthroughDataSet. (#610)

Workbook Script Metadata Editor

Column headers positioned at the top of the Procedures grid:

Column headers embedded for each procedure, method, and argument list:

Column headers hidden:

Workbook Scripting

When the following arguments are used in new scripts, the previous default values will have to be entered as required for the desired behavior:

Method.Argument Default Value Was

AddDataCache.Delimiter ;

AddDataCache.ListDelimiter |

AddProperty.DataType string

AddProperty.Initialize TRUE

AddTokensForSubstVars.TokenName [T.<VarName>]

BuildRangeFromScript.BuildRowsOrColumns Rows

BuildRangeFromScript.Delimiter ;

BuildRangeFromScript.EnterNumbersAsText TRUE

BuildRangeFromScript.ListDelimiter |

BuildRangeFromScript.StartCell A1

CancelEvent.Cancel FALSE

ClearRange.Clear All

EssbaseRunCalc.BackgroundCalc FALSE

ExecuteProcedure.MethodCondition TRUE

ExitProcedure.Exit FALSE

SaveWorkbook.IncrementFilename TRUE

SetActiveWorkbook.SpecifyWorkbookBy Index

SetComment.MatchAny FALSE

SetEntry.Error None

SetPageBreak.BreakSetting Manual

SetPageBreak.Orientation Horizontal

SortRange.SortBy All

UnloadWorkbook.SpecifyWorkbookBy Index

One of the method’s arguments is PropertyName. If specified, each iteration of the loop adds a Property with the specified name and the value of the loop’s current value. As a result, the @PVal() Function can be used to get the value.

Using the DataCache overload, the ForEach method can loop through values from any of the list sources supported by the AddDataCache method, such as DelimitedString, SQLPassThroughDataSet, and EssbseReportScript. (#640)

Dodeca Framework Build 1321

December 12, 2008

Selectors and Essbase Selectors Metadata Editors

Starter Kit Metadata

Workbook Scripting

Dodeca Framework Build 1308

December 8, 2008

The versions of SpreadsheetGear and Infragistics NetAdvantage have been upgraded for this build. SpreadsheetGear was upgraded from SpreadsheetGear 2007 (3.1.0.104) to SpreadsheetGear 2008 (4.0.8.104). NetAdvantage was upgraded from NetAdvantage 2007, Vol. 3 to NetAdvantage 2008, Vol.3 (8.3.20083.1009).

NetAdvantage 2008, Volume 3

IMPORTANT: We recommend that you upgrade your toolbar configurations to NetAdvantage 2008, Vol. 3 by exporting and importing your toolbars configurations. To do this, use the Export and Import tools from the Toolbars Configuration Metadata Editor. These tools export and import the native NetAdvantage UltraToolbarsManager layout files. If you have any questions, please contact us.

SpreadsheetGear 2008

SpreadsheetGear is the vendor of the spreadsheet control used by the Essbase view types and the Excel view type.

SpreadsheetGear 2008 includes enhancements to the WorkbookView spreadsheet control, including (but not limited to) the following:

*This version of SpreadsheetGear includes support for 2D charts only and does not support axis titles. You may encounter other limitations as this is the first release of SpreadsheetGear that includes charting.*

Refer to the following for additional information:

http://www.spreadsheetgear.com/support/samples/imaging.aspx

An Excel template used by an Essbase Excel view or a generic Excel view can now contain charts, which are retained within Dodeca, and persisted when the view is opened in or saved to Excel.

Application User Interface

Binary Artifact Metadata Editor

Please contact us for additional details if you use Symantec and are experiencing any issues with ClickOnce deployment.

When this error is encountered, take the following steps on the client machine:

If the error persists, please contact us.

ClickOnce Prep Utility

Dodeca Metadata Service

Several customers have requested a way to get the DDL for the Dodeca metadata table structure. This capability is now available by going to a web browser and typing in the location of the metadata service* and then selecting the option to generate DDL.

*The format of the metadata service address is:

http://<server>:<port>/dodeca/metadata-service

The DDL generated is based on the database type configured in your hibernate.properties file and includes the DDL to drop an existing schema and create a new schema. It also includes the DDL to update a schema, so you can see changes to the schema definition.

Note: The update schema functionality does not show changes in column widths and thus should be used for informational purposes only.

Essbase Adhoc View

Splash Screen

Tool Controllers

This issue was reported by a customer who needed to assign a member name that contained a forward slash as the value of a worbook script tool controller argument.

Toolbars Configuration Metadata Editor

The Toolbars Configuration Metadata Editor now supports new utilities: Toolbars Designer and Configure Tools.

The Toolbars Designer supports adding/editing/deleting toolbars and tools, as well as adding and removing tools contained by toolbars and popup menus.

Note: Since views use popup menu tools as context menus, you are able to create and modify context menus.

Accessible from the designer, the Import Standard Tools and Toolbars and the Import Tools and Toolbars from a Toolbars Configuration wizards support importing existing tools and toolbars.

The Configure Tools utility supports the ability to customize configurable standard tools by specifying tool argument values and to configure custom tools by mapping a tool to a tool controller and specifying tool argument values.

Workbook Script Tool Controller

Workbook Scripting

Dodeca Framework Build 1244

October 28, 2008

Comments Functionality

Workbook Scripting

Tool Arguments Builder

ClickOnce Installation

This issue is encountered on machines with an old version of the Proventia software. A dialog box with a "Details" button is displayed indicating the installation failed. The button is either disabled or, when the button is clicked, an empty document is presented.

The issue can be resolved by upgrading the Proventia software. Please contact us for specific instructions. (#580)

The current versions of Kensington Mouseworks have a problem in one of their driver DLL’s that crashes all ClickOnce applications. The issue occurs in a DLL that is loaded with every Windows process, and crashes that process if the executable path is longer than 128 characters. Since the ClickOnce store uses directory obfuscation when storing an application’s files, this issue affects all ClickOnce applications.

The only current workaround is to use Add/Remove Programs to uninstall Kensington Mouseworks. This requires a reboot to restore the minimal drivers Windows needs for mouse input. After the reboot, any advanced functionality provided by the Kensington hardware will not work. (#581)

Dodeca Framework Build 1234

October 17, 2008

Comments Functionality

In addition to the existing ShowFormulaBar setting, the Excel Essbase view now supports a ShowFormulaBarOnDrillthroughSheet, which controls whether the Formula Bar is visible when the active sheet is a drillthrough sheet. The ShowFormulaBar setting now only applies to non-drillthrough sheets.

Excel and Excel Essbase ViewsU

Workbook Scripting

The view’s toolbars and selectors are disabled while the calculation is running, but the user is able to interact with other views. The view’s status bar displays "Calc running" while the calculation is underway and "Calc completed" when the calculation is done. When the calculation is completed, the workbook script procedure resumes with the next method following the EssbaseRunCalc method.

Essbase Selector TreeView

Auto Build Tool Controller

Workbook Script Tool Controller

Tool Controller Debug Tool Argument

OpenViewForDataCellToolController

OpenViewForMemberCellController

All the Essbase operations tool controllers, such as KeepOnlyToolController, ZoomInToolController, etc.

The argument is a Boolean that defaults to false. To turn on debugging, specify /x=true in the tool arguments string.

For these tools, the context menu tool visibility is set based on whether the selected cell(s) passes the criteria defined by the tool arguments. If debugging is on, a message box is displayed after the right button is clicked and before the context menu is displayed. The message box contains the tool argument values, information about the selected cell(s), and diagnostics.

For example, the message box below was displayed by the "Open View for Member Cell" tool controller that was configured to display the tool only when Market members are selected. As indicated, only one of the 3 selected cells qualified, since one of the cells was a Year, and another a Scenario.

Dodeca Framework Build 1216

October 1, 2008

Manage Saved View

The SetCover method controls whether the view content is shown or hidden. (#557)

This method can be used to show or hide a specified view toolbar. The tool arguments include a ToolbarKey and Visible value. This method allows you, for example, to control whether any given toolbar is visible based on the active sheet by calling the method from the ActiveSheetChanged event link. For instance, with an ExcelEssbaseView, you may want a particular toolbar to be displayed only when the active sheet is a drillthrough sheet. (#566)

This function returns an Essbase connection’s property value given the connection ID and the property name. (#556)

Arguments: No EssbaseConnectionID specified; No Username/Password specified.

Behavior: The view’s connection is used. The current username and password associated with the connection are used. This could come from the "phantom" username and password stored in the connection’s metadata or the username and password that the user may have already entered. If no username and password is associated with the connection, the user is prompted.

Arguments: No EssbaseConnectionID specified; Username/Password specified.

Behavior: The view’s connection is used. The specified username and password arguments are used for the connection.

Arguments: EssbaseConnectionID specified; No Username/Password specified.

Behavior: The specified connection is used. The current username and password associated with the connection are used. This could come from the "phantom" username and password stored in the connection’s metadata or the username and password that the user may have already entered. If no username and password is associated with the connection, the user is prompted.

Arguments: EssbaseConnectionID specified; Username/Password specified

The specified connection, username, and password are used. (#564)

Dodeca Framework Build 1201

September 17, 2008

Session Caching (#547)

The session cache is a client cache that is used to store data, such as resolved selector lists and SQL pass-through datasets, for the duration of a session. The cache uses the same backing store used for application caching. Session caching improves performance by reducing the number of client to server requests. It is enabled or disabled for an application by the application’s SessionCachingEnabled metadata setting. It is enabled by default.

When session caching is enabled for an application, the administrator controls whether or not an individual SQL pass-through dataset or selector list uses the session cache through the metadata instance’s SessionCachingEnabled setting.

By default, a SQL pass-through dataset’s SessionCachingEnabled setting is False. When the query results are data-driven (i.e. when the query results are likely to change over the course of a session), then session caching should be disabled.

Please note that a SQL pass-through dataset that contains a tokenized query does not necessarily imply that the results are data-driven. When the query is tokenized, the cache can contain multiple occurrences of the dataset, each representing a unique post-'token replacement' query statement.

Session caching is also supported for Essbase selector lists:

The EssbaseDelimitedString, EssbaseMemberQuery, and EssbaseReportScript selector list items are optionally cached based on the selector list’s SessionCachingEnabled setting. By default, session caching is enabled for an EssbaseDelimitedString and EssbaseMemberQuery, but is disabled for an EssbaseReportScript. Session caching should be disabled for an EssbaseReportScript selector list when the members generated by the report are data-dependent, such as when the script suppresses missing.

The EssbaseSQLPassthroughDataSet selector list items are optionally cached based on the associated SQL pass-through dataset’s SessionCachingEnabled setting.

When session caching is enabled for an Essbase selector list and the selector list’s SelectorControlObjectTypeID is set to EssbaseSelectorTreeView, then the session cache is also used to store outline information that is used to populate the treeview.

Generally speaking, session caching should be disabled for any selector list that represents a dimension whose member hierarchy changes frequently. However, when a dimension is fairly static, enabling session caching is an excellent way to improve overall performance.

Session caching is also used to store Essbase member information that’s retrieved for the following:

An administrator can view the contents of the session cache using the Cache Viewer tool, which is located in the default admin toolbars configuration’s Admin menu.

Workbook Scripting

Save View Tool

View Metadata Editor

Dodeca Framework Build 1187

September 8, 2008

ClickOnce Prep Utility

The application launch mode LaunchFromURLOnly is especially useful when there are multiple applications installed on a client machine that are defined within the same tenant. Since the tenant, application ID, and path are specified by the URL’s query string parameters, using the link ensures that the correct application is started.

Excel Essbase View

The event sequence when building an Excel Essbase View is as follows:

BeforeBuild

BeforeWorkbookClose

AfterWorkbookClose

BeforeTemplatePullToClient

AfterTemplatePullToClient

BeforeWorkbookOpen

AfterWorkbookOpen

For a cascade view

BeforeCascadeSheetsBuild

If the SummarySheetAdd setting is true

BeforeCascadeSummarySheetBuild

End If

AfterCascadeSheetsBuild

End For

BeforeWorkbookRetrieve

For each sheet

BeforeSheetReplaceTokens

AfterSheetReplaceTokens

BeforeSheetRetrieve

For each retrieve range on the sheet

BeforeRangeRetrieve

AfterSheetRetrieve

End For each

AfterSheetRetrieve

End For each

AfterWorkbookRetrieve

If the view has CommentRanges

BeforeCommentsSetup

AfterCommentsSetup

End If

AfterBuild

Dodeca Framework Build 1183

September 3, 2008

ClickOncePrepUtilityForm

Comments

Workbook Scripting

EssbaseLoginDialog

Dodeca Framework Build 1173

August 26, 2008

Database structural changes

Three changes were made to the database structure in this release which will require an update to the database schema. Two of the changes were made to accommodate longer hash key values necessary to comply with the government 'FIPS' ("Federal Information Processing Standards") standards required for Federal government computing systems. Dodeca formerly used MD5 hashing algorithms and has been updated to the FIPS-compliant SHA-1 standard. The changes are:

The changes to the hashing algorithms will require that customers with existing metadata run a one-time update to their metadata using our new HashcodeUpdateUtility. This command-line utility will convert the values in the BINARY_ARTIFACTS.CHECKSUM and COMMENTS.COMMENT_KEY_HASH columns from MD5 to SHA-1 hash codes. We expect to help our customers make this change via webcast. IF YOU ARE AN EXISTING CUSTOMER WITH EXISTING METADATA, DO NOT INSTALL THIS BUILD UNTIL YOU DISCUSS THIS CHANGE WITH US AND YOU HAVE BACKED UP YOUR EXISTING DATABASE INCLUDING METADATA AND COMMENTS.

User Audit Report

The Dodeca metadata service now has a User Audit report. This report can help you manage your license compliance and can also help you determine who is using your system. To run the report, open a browser instance and type in the location of your metadata service (ex. 'http://<server>:<port>/dodeca/metadata-service'). You can then select to run the report for a specific tenant code or for all tenant codes.

Hibernate Properties

The C3P0 Connection Pool settings have been changed in the hibernate.properties file. The default values were increased for hibernate.c3p0.max_size, hibernate.c3p0.min_size, and hibernate.c3p0.acquire_increment to 100, 10, and 5, respectively. (#530)

New Caching Configuration Options (#518)

Two new configuration options were added to the app.config file to support differing needs for caching user settings at runtime.

DataDirectoryBackingStore

The DataDirectoryBackingStore was added to support local storage that uses FIPS compliant algorithms for hashing and encryption. It is also a good choice for customers who do not want to use .NET isolated storage. Further, this backing store is a good choice for customers who would like to encrypt their user settings using FIPS compliant TripleDES encryption. This backing store stores settings by creating files in the ClickOnce Data Directory. To configure the DataDirectoryBackingStore, follow these steps before building your deployment:

DataBackingStore

The DataBackingStore was added to support storage of user settings in a SQL Server database. It is a good choice for customers who would like to have their settings be persisted across multiple computers. It is also the backing store that is supported when using Citrix server in a distributed server farm environment. This backing store stores settings in a database table in a SQL Server database. To configure the DataDirectoryBackingStore, follow these steps before building your deployment:

After these steps are complete for the respective backing store, save and close the .config file. Run the ClickOncePrepUtilityForm and generate a new deployment. When a user installs Dodeca on their machine, the new setting storage mechanism will start.

Note: We do not support conversion of previously stored local settings to use a different backing store. Further, as the metadata service location, tenant and application keywords were previously stored solely in the backing store, if the user was previously deployed from a URL, it is mandatory for that user to reinstall from a URL in order to have those values available for processing. If they initially attempt to launch/update from a menu shortcut, they may see an error indicating the tenant or other key pieces of information is missing.

Note: Beginning with this build, a backup copy of the the metadata service location, tenant and application keywords, which are essential to Dodeca startup, are also stored in the client deployment metadata subdirectory. As these values are passed on the querystring upon the original installation and startup, the backup copy of these values are stored unencrypted.

ClickOncePrepUtilityForm

The ClickOncePrepUtilityForm now uses the data directory backing store (instead of isolated storage) to store saved settings. If you previously saved settings, you will need to re-enter and re-save the settings. (#531)

Application Setup Utility

The Application Setup Utility also now uses the data directory backing store (instead of isolated storage) to store saved settings. If you previously saved settings, you will need to re-enter the settings.

Session State

The application’s size, location, and whether the application is maximized is now retained between sessions and restored on startup. (#497)

View Selector

Previously, when a view selector’s HierarchyToRoleMapping setting was used to map a hierarchy to a role, the first hierarchy that the authenticated user qualified for (based on role membership) was presented in the view selector. Since a user may actually have several roles (i.e. belong to several groups), the functionality has been changed in this build to display all the hierarchies that the user qualifies for.

The ViewSelectorExplorerBar merges multiple hierarchies based on hierarchy item ID’s.

With the ViewSelectorTree, the HierarchyRootNodeDisplayPolicy setting controls whether the root nodes represent the hierarchies OR represent the top level items of each hierarchy. The policy options include the following:

The ability to filter hierarchy items before a hierarchy is displayed is now supported through extensibility by way of a custom filter service. This allows an application to restrict which hierarchy items are presented based on the user’s access. The ViewSelectorExplorerBar and ViewSelectorTree settings now include a HierarchyItemsFilterObjectTypeID setting, which identifies the service.

Please contact us for more information about how to implement a hierarchy item filter service.

A new application setting, ViewSelectorLocked, allows an administrator to prevent a user from moving the view selector from the default docked location, which is specified by the ViewSelectorDockedLocation setting.

Selectors

When the control was undocked and then the Find section was expanded, the Search results covered the "Select" button.

The view setting SelectorControlDisplayMode now supports a new option, LockedDockedControl, which can be used to prevent a user from moving the treeview or listbox selector from the default docked location, which is specified by the SelectorControlDockedLocation setting.

Workbook Scripting

Previously, multiple WorkbookScriptToolController instances could only share the same workbook script if all the instances used the view’s workbook script. This required that the logic for the workbook script-based tools be embedded in the view’s workbook script (or be included as an external script in the view’s workbook script.)

With this build, workbook script-based tools that provide related functionality, such as tools that support spreading, can use the same workbook script instance without having to use the view’s workbook script. This is handled automatically when the /s tool argument is used and the specified ID is a different ID than the view’s WorkbookScriptID setting.

A WorkbookScriptToolController can still use the view’s workbook script by not including the /s tool argument or by using the /s tool argument and specifying the same value as the view’s workbook script ID.

The AfterInitializeUI event link is raised after the view UI is initialized.

This event link was added to address a customer’s specific requirement, which was to be able to set a dependent selector’s selection to null when the user changes the "depended on" selector’s selection, but only after the dependent selector’s default selection was set based on the default selection policy. The AfterInitializeUI event link can be used to add a view token that sets the dependent selector’s token to "null". When the "depended on" selector’s selection changes, the dependent selector will have a null selection.

This event link will be useful whenever you need to insert logic after the view’s user-interface is initialized, but before the view is actually displayed.

When the ConnectionID argument was specified, the script was not using the username and password settings that were configured by the connection’s metadata. Instead, the script was using the view’s Essbase connection’s username and password.

With this build, the script’s behavior is as follows:

If the Username and Password script arguments are specified, the specified values are used. Otherwise, if the Essbase connection’s metadata Username and Password settings are specified, the specified values are used. Otherwise, the username and password of the view’s connection are used.

Saved Views

The Save View As command now checks to determine whether the name specified by the user is the name of another view previously saved by the user. If so, the user is prompted as follows: "The saved view name "<view name>" already exists. Do you want to replace the existing saved view?" If the user clicks "Yes", the existing saved view is replaced with the current view.

In addition, the saved view name is now always used as the view caption even when the view’s CaptionAfterBuild setting is specified.

SQLPassthroughDataSet Enhancements (#503)

The SQLPassThroughDataset has a new Timeout property that is used to limit the amount of time a query can run on the server. The property value is configurable for each distinct SQLPassthoughDataSet object and is expressed in seconds. A value of 0 or less indicates no timeout will be set.

The ConnectString property has been changed to make it easier to specify. Previously, the ConnectString was specified using a space delimited format and was sensitive to the insertion of extra spaces. The format has been changed to support newline delimiters between different properties with a space delimiter between the property name and the property value. Here is an example of the new format:

hibernate.dialect org.hibernate.dialect.SQLServerDialect

hibernate.connection.username sa

hibernate.connection.password password

hibernate.connection.driver_class com.microsoft.sqlserver.jdbc.SQLServerDriver

hibernate.connection.url jdbc:sqlserver://localhost;DatabaseName=AdventureWorks;SelectMethod=cursor

This format is the format used in the hibernate.properties file and makes it possible to use properties that do not have a name/value pair such as 'hibernate.jdbc.wrap_result_sets'.

The SQLPassthroughDataSet functionality continues to support the old format

ConnectString but we recommend customers update their ConnectString definitions to use the new format.

The connect string can be tokenized. Token replacement is performed before the query is executed.

The column names returned by a query, along with their data type, are now automatically mapped from the resultset. Column definitions are no longer necessary nor are they used if created in the metadata editor.

The ConnectString property is now encrypted using the TripleDES encryption algorithm when it is transported across the network. This change was made to prevent passwords from being transmitted across the network in clear text format. Note: The encryption was also implemented in the older SQLPassthroughDataSetService.

The server initializes connections using a Hibernate SessionFactory object. In the previous implementation of the SQLPassthroughDataSet service, a SessionFactory was created for each call to the service which was inefficient. The service has been updated to cache the SessionFactory on the server. The SessionFactory cache is keyed on an SHA-1 hash of the encrypted ConnectString. The new connection caching functionality enables support for Hibernate connection pooling for SQLPassthroughDataSet functionality which should increase performance.

Backwards Compatibility Note:

The original SQLPassthroughDataSet service implementation has been retained on the server but is scheduled to be removed after existing customers confirm they have updated their SQLPassthroughDataSet definitions. To enable the older SQLPassthroughDataSet service on the server, find the following line in the dodeca.properties file:

dodeca.metadata.service.SQLPassthroughDataSet=com.appliedolap.dodeca.server.metadata.selectors.SQLPassthroughDataSetService2

and replace it with:

dodeca.metadata.service.SQLPassthroughDataSet=com.appliedolap.dodeca.server.metadata.selectors.SQLPassthroughDataSetService

Open In Excel Tool (#510)

Previously, when a view was opened in Excel, the view’s name was used as the Excel filename. In this build, the view’s caption is used as the Excel filename.

Page Setup Tool (#524)

The non-PageSetup nodes are no longer displayed and the caption is set to "Page Setup'. Also, the Page Setup node for the active worksheet is automatically selected.

New Tools

The About tool has been added to the default Administrator toolbars configuration. It displays the version and build number.

The tools can be added to a toolbar and context menu.

The Paste options include All, All (except cell borders), Formats, Formulas, Formulas and Number Formats, Values, Values and Number Formats

The tool saves the view’s workbook as an Excel file and adds the Excel file as an attachment to an email that is then displayed using the default email client UI. The user can add recipients and edit the email before sending. The tool has been added to the appropriate Sample tenant’s view toolbars configurations.

The following formatting tools are now supported: AlignLeft, AlignRight, Bold, Borders (Popup menu), BordersList (12 different borders buttons), Center, CommaStyle, CurrencyStyle, DecreaseDecimal, DecreaseFontSize, DecreaseIndent, Explorer, FillColor,

Font, FontColor, FontSize, IncreaseDecimal, IncreaseFontSize, IncreaseIndent, Italic,

MergeAndCenter, PercentStyle, Underline

The Print All tool prints all the worksheets in the workbook.

ExcelEssbaseView

Dodeca Framework Build 1110

July 18, 2008

Essbase Selector Metadata Editor

A default selector list can now be automatically created for each new selector when committed. The default selector list ID is <SelectorID>_Default, such as Market_Default. The default control is an EssbaseSelectorTreeView and the default list is an EssbaseDelimitedString. The SelectorListProperties are set as follows:

Metadata Editors

For the metadata editors that display properties in a property grid, any property that represents another metadata instance can be right clicked to display a context menu that allows the metadata editor (associated with the instance’s type) to be opened.

For example, in the View Metadata Editor, if an Excel Essbase View is selected, clicking the WorkbookScriptID property name with the left mouse button (to activate the item) and then clicking the right mouse button displays a context menu. If the WorkbookScriptID is set to the ID of a workbook script, such as MyWorkbookScript, the context menu contains an "Edit MyWorkbookScript" item. Clicking the item opens the Workbook Scripts Metadata Editor (or activates the editor if already open) and the MyWorkbookScript instance is automatically selected. If the ID is not assigned a value, the context menu displays "Open Workbook Scripts", which also opens or activates the Workbook Script Metadata Editor, but without automatically selecting an instance.

Import Essbase Connections

A new utility is available that supports importing multiple Essbase Connections. The Import Essbase Connections utility is available from within the Essbase Connections Metadata Editor and can be launched from Admin / Quick Start Utilities and from the Quick Start task pane, which can be displayed from the Help menu.

Import Dimensions

The Import Dimensions utility, which is available from within the Essbase Selectors Metadata Editor, can now be launched from Admin / Quick Start Utilities and from the Quick Start task pane, which can be displayed from the Help menu.

Selector and Selector List Changes and Bug Fixes

Workbook Script Tool Controller

The workbook script tool controller now supports a Tool Caption argument.

Selector Control Dialog

When a view’s SelectorControlDisplayMode is set to ModalDialog, the dialog’s location centered within the bounds of the application instead of relative to the selector tool. The size of the dialog has also been made larger.

Import View from Excel

The issue with the import of a view from Excel failing when an optional selector has a null selection when the view was exported has been fixed.

Dodeca Framework Build 1088

June 30, 2008

Essbase Connections

Send Tool

Workbook Scripting

Dodeca Framework Build 1070

June 23, 2008

Essbase Send Tool

Workbook Scripting

A workbook script now has a DoTracing property, which defaults to False. The DoTracing property can be turned On and Off via the SetTracing workbook script method. This method supports the logging of a specific message. When tracing is On, each executed procedure is logged in the WorkbookScriptTrace.log file. This file is delimited, so it can easily be viewed in Excel.

The function parser now supports quoted strings. If a string within a function’s arguments is quoted with double quotes, then everything within the quotes is treated as a single argument and the parser will not parse characters within the quotes.

Selectors

SpreadsheetGear WorkbookView