Understanding Events in the Life of Essbase Excel View

An Excel Essbase view has a very eventful life. Whenever something significant is either about to happen or has happened, the view notifies anyone who is interested. The notifications are each referred to as an event.

Workbook scripting provides a mechanism for listening and responding to a view’s events. A workbook script event link is used to respond to an event by way of a procedure, which is automatically executed when the event occurs. The procedure consists of one or more methods, each of which performs some action within the context of the event.

Some events have event arguments that provide information about the event context. For example, the BeforeRangeRetrieve event has two arguments: SheetName and RangeName. The workbook script @EPVal(<PropertyName>) function can be used to get the value of an event argument, such as @EPVal(RangeName) to get the name of the range being retrieved.

In the following sections, the events that occur when a view is opened, built, and closed, as well as when operations are performed, are listed and described, including any event arguments associated with the event.

  • Events that occur before something significant is about to happen allow the workbook script to cancel whatever is about to happen. The CancelEvent method can be used to prevent the default behavior or processing from continuing. This is referred to as _cancelling the event.

Events on Open

When an Excel Essbase view is opened, the following events occur:

Event 1:  AfterConstruct

Description

This event occurs after the view metadata creates the Excel Essbase view instance and before the view user interface is created and initialized.

Remarks

This event provides the opportunity to set view properties, such as adding or editing view tokens, before the view’s user interface is initialized, including selectors.

Most of the view’s properties can be edited from a workbook script using the SetViewPropertyValue method.

To add or edit a view token during the AfterConstruct event, use the View overload of the AddToken method. This is equivalent to editing the view’s ViewTokens property in the View metadata editor.

It is actually quite rare for a workbook script to use the AfterConstuct event link, but when needed, it provides a way to edit view properties before any of the settings are used.

Event 2:  AfterInitializeUI

Description

This event occurs after the view user interface is created, and before the view is shown.

Remarks

Similar to the AfterConstruct event, this event is rarely used by workbook scripts. It is useful, for example, when a view token needs to be added or modified after the user-interface is initialized, which is when the selectors are created and the default selections are set.

Events on Build

When an Excel Essbase view is built, the following events occur:

Event 1:  BeforeBuild

Description

This event occurs at the beginning of the build processing.

Remarks

This event can be cancelled, which cancels the build processing. If the workbook script cancels the event, it should also update the status bar. The SetViewStatus method updates the status icon and text. The SetProgressText method updates the progress text.

Event 2:  BeforeWorkbookClose

Description

This event occurs before the current workbook is closed.

The view can be thought of as a workbook that contains one or more sheets. Even before the first time the view is built, the workbook contains a single empty sheet. Since the view can be built many times, the workbook is closed during the build processing to restore the view to the initial state. Closing the workbook actually removes all the sheets except for the first sheet, which is cleared of values, formulas, and formats.

Remarks

This event can be cancelled, which cancels the build processing. As with cancelling the BeforeBuild event, the status bar should be updated by the workbook script.

Event 3:  AfterWorkbookClose

Description

This event occurs after the current workbook is closed.

Event 4:  BeforeTemplatePullToClient

Description

Even though the Excel template is actually only pulled from the server to the client the first time the view is built or the next time the view is built after the template has changed, this event always occurs before the check is performed that determines whether the template already exists in the client cache and, if so, whether the template needs to be updated.

Remarks

This event can be cancelled, which cancels the build processing.  As with cancelling the BeforeBuild event, the status bar should be updated by the workbook script.

This event can be used to dynamically control which Microsoft Excel template is used for the view. To do so, use the SetViewPropertyValue method to set the ExcelTemplateBinaryArtifact.ID and ExcelTemplateBinaryArtifact.ID values.

Event 5:  AfterTemplatePullToClient

Description

This event occurs after the Microsoft Excel template is either determined to already exist in the client cache or is pulled from the server to the client cache.

Event 6:  BeforeWorkbookOpen

Description

This event occurs before the Excel template, which is specified by ExcelTemplateBinaryArtifact property, is loaded.

Remarks

This event can be cancelled, which cancels the build processing. As with cancelling the BeforeBuild event, the status bar should be updated by the workbook script.

Event 7:  AfterWorkbookOpen

Description

This event occurs after the Microsoft Excel template is loaded.

Remarks

This event is by far the most frequently used of all the events. It provides the opportunity to dynamically affect the content of the view.

When the view is configured as a cascading view, the following events occur:

Event 8:  BeforeCascadeSheetsBuild

Description

This event occurs before the cascade template sheet is copied for each unique combination of cascade source selections.

Remarks

Although this event can be cancelled, doing so does not cancel the build processing, but rather cancels the creation of the cascade sheets.

Event 9:  BeforeCascadeSummarySheetBuild

Description

If the view’s SummarySheetAdd setting is True, this event occurs before the cascade summary sheet is added.

Remarks

Cancelling this event prevents the summary sheet from being added.

Event 10:  AfterCascadeSheetsBuild

Description

This event occurs after the cascade sheets are added and the template cascade sheet is deleted.

This concludes the events that occur when the view is configured as a cascading view.

Event 11:  BeforeWorkbookRetrieve

Description

This event occurs before the retrieval of Essbase data begins for the entire workbook. The view’s RetrievePolicy property determines whether all sheets, only sheets that contain one or more named retrieve ranges, or only the active sheet is retrieved.

Remarks

Cancelling this event bypasses the default retrieve behavior.

This event provides the opportunity to modify the view content before the Essbase retrieve(s) occurs or to intercept the processing with logic implemented by the workbook script.

For each sheet that is retrieved, as determined by the view’s RetrievePolicy property, the following events occur:

Event 12:  BeforeSheetReplaceTokens

Description

This event occurs before tokens are replaced on the sheet.

Event Arguments

SheetName is the name of the sheet.

Remarks

Cancelling this event bypasses the default token replacement behavior for the sheet.

Since the sheet cannot be assumed to be the active sheet, any workbook script method called within the context of this event that applies to the sheet should set SpecifySheetBy to SheetName and set SheetSpec to @EPVal(SheetName).

Event 13:  AfterSheetReplaceTokens

Description

This event occurs after the tokens are replaced on the sheet.

Remarks

Since the sheet cannot be assumed to be the active sheet, any workbook script method called within the context of this event should set SpecifySheetBy to SheetName and set SheetSpec to @EPVal(SheetName).

Event 14:  BeforeSheetRetrieve

Description

This event occurs before the data is retrieved for all of the Essbase retrieve ranges on the sheet, or if no retrieve ranges exist, for the used range.

Event Arguments

SheetName is the name of the sheet.

Remarks

Cancelling this event bypasses the default retrieve behavior for the sheet.

This event provides the opportunity to modify the sheet content before the Essbase retrieve(s) occurs or to intercept the processing with logic implemented by the workbook script.

Since the sheet cannot be assumed to be the active sheet, any workbook script method called within the context of this event that applies to the sheet should set SpecifySheetBy to SheetName and set SheetSpec to @EPVal(SheetName).

For each retrieve range on the sheet, or if no retrieve ranges exist, the following events occur for the used range:

Event 15:  BeforeRangeRetrieve

Description

This event occurs before the Essbase data is retrieved for the range.

Event Arguments

SheetName is the name of the sheet that contains the range.

RangeName is the name of the retrieve range. If the used range is being retrieved, the range name is empty.

Remarks

Cancelling this event bypasses the default retrieve behavior for the range.

This event provides the opportunity to intercept the processing with logic implemented by the workbook script.

Since the sheet cannot be assumed to be the active sheet, any workbook script method called within the context of this event that applies to the sheet should set SpecifySheetBy to SheetName and set SheetSpec to @EPVal(SheetName).

Event 16:  AfterRangeRetrieve

Description

This event occurs after the Essbase data is retrieved for the range.

Event Arguments

SheetName is the name of the sheet that contains the range.

RangeName is the name of the retrieve range. If the used range is being retrieved, the range name is empty.

Remarks

Since the sheet cannot be assumed to be the active sheet, any workbook script method called within the context of this event that applies to the sheet should set SpecifySheetBy to SheetName and set SheetSpec to @EPVal(SheetName).

This concludes the events that occur for each range on the sheet.

Event 17:  AfterSheetRetrieve

Description

This event occurs after all the ranges on the sheet have been retrieved.

Event Arguments

SheetName is the name of the sheet.

Remarks

Since the sheet cannot be assumed to be the active sheet, any workbook script method called within the context of this event that applies to the sheet should set SpecifySheetBy to SheetName and set SheetSpec to @EPVal(SheetName).

This concludes the events that occur for each sheet.

Event 18:  AfterWorkbookRetrieve

Description

This event occurs after all the sheets, as determined by the view’s RetrievePolicy property, are retrieved.

If the view has CommentRanges, the following events occur:

Event 19:  BeforeCommentsSetup

Description

This event occurs before the comments are set up.

Event 20:  AfterCommentsSetup

Description

This event occurs after the comments are set up.

This concludes the events that occur when the view has CommentRanges.

Event 21:  AfterBuild

Description

This event occurs at the end of the build processing.

Remarks

This event can be cancelled, which is not typical for an event that occurs after something has happened. This allows a workbook script to cancel the build after the processing based on the content of the view.  For example, if the view does not contain any data, the workbook script can cancel the event, so the view remains covered.