Developing Custom-Defined Calculation Macros

In This Section:

Understanding Custom-Defined Macros

Naming Custom-Defined Macros

Creating Custom-Defined Macros

Using Custom-Defined Macros

Viewing Custom-Defined Macros

Updating Custom-Defined Macros

Copying Custom-Defined Macros

Removing Custom-Defined Macros

Refreshing the Catalog of Custom-Defined Macros

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

Understanding Custom-Defined Macros

With custom-defined macros, you can combine multiple calculation functions into a single function.

When developing and testing custom-defined macros, create and test new macros locally within a test application. Register custom-defined macros globally only after you have tested them in a test application and are ready to use them in a production environment.

To create and manage custom-defined macros, you must have a security level of Database Manager or higher.

Naming Custom-Defined Macros

Follow these guidelines when naming custom-defined macros:

  • Start the macro name with the “@” symbol; for example, @MYMACRO. The rest of a name can contain letters, numbers, and the following symbols: @, #, $, and _. Macro names must not contain spaces.

  • For macros that are called only by other macros, start the macro name with “@_”, to distinguish it from general-use macros and functions.

  • Give macros unique names. Additionally, a macro name must be different from the names of custom-defined functions and from the names of existing calculation functions.

    Note:

    If an application contains a local macro that has the same name as a global macro, the local macro takes precedence and is used for calculation.

  • For local macros, you must prepend the application name to the macro name, separating the application name from the macro name with a period:

    AppName.@MacroName

    For example:

    Sample.@MYMACRO
  • Because global macros are available to any application running on the Essbase Server where the macro was created, you do not assign an application name to it.

Creating Custom-Defined Macros

When you create a custom-defined macro, Essbase records the macro definition and stores it in a catalog of macros. You can then use the macro in formulas and calculation scripts until the macro is removed from the catalog.

You can register a custom-defined macro in the following ways:

  • As local, in which the macro is available only in the Essbase application in which it was created

  • As global, in which the macro is available to all Essbase applications running on the Essbase Server where the macro was created

  To create a custom-defined macro, use a tool:

Tool

Topic

Location

Administration Services

Creating Custom-Defined Macros

Oracle Essbase Administration Services Online Help

MaxL

create macro

Oracle Essbase Technical Reference

The following MaxL statement creates a local macro named @COUNTRANGE for use in the Sample application:

create macro Sample.'@COUNTRANGE'(Any) AS
'@COUNT(SKIPMISSING, @RANGE(@@S))'
spec '@COUNTRANGE(MemberRange)'
comment 'counts all non-missing values';

The following MaxL statement creates a global macro named @COUNTRANGE:

create macro'@COUNTRANGE'(Any) AS
'@COUNT(SKIPMISSING, @RANGE(@@S))'
spec '@COUNTRANGE(MemberRange)'
comment 'counts all non-missing values';

Using Custom-Defined Macros

You can use custom-defined macros like native calculation commands in calculation scripts or formulas.

  To use a custom-defined macro:

  1. Create or open an existing calculation script or formula.

    • If it was registered locally, you must use a calculation script or formula within the application in which the macro was created.

    • If it was registered globally, you can use any calculation script or formula within any application on the Essbase Server.

  2. Add the custom-defined macro to the calculation script or formula.

    For example, to use the @COUNTRANGE custom-defined macro shown earlier in this chapter, create the following calculation script:

    CountMbr = @COUNTRANGE(Sales, Jan:Dec);

    Use this calculation script with the Sample.Basic database, or replace “Sales, Jan:Dec” with a range of members in a test database.

  3. Save the calculation script or formula, and then run it as usual.

Viewing Custom-Defined Macros

View a custom-defined macro to determine whether it has been successfully created, or whether it is local or global.

  To view a custom-defined macro, use a tool:

Tool

Topic

Location

Administration Services

Viewing Custom-Defined Macros

Oracle Essbase Administration Services Online Help

MaxL

display macro

Oracle Essbase Technical Reference

Updating Custom-Defined Macros

  To update a custom-defined macro:

  1. Determine whether the macro is registered locally or globally.

    See Viewing Custom-Defined Macros.

  2. To update the macro definition; use a tool:

Tool

Topic

Location

Administration Services

Editing Custom-Defined Macros

Oracle Essbase Administration Services Online Help

MaxL

create macro or replace macro

Oracle Essbase Technical Reference

The following MaxL statement changes the local macro @COUNTRANGE, which is used only in the Sample application:

create or replace macro Sample.'@COUNTRANGE'(Any)
as '@COUNT(SKIPMISSING, @RANGE(@@S))';

The following MaxL statement changes the global macro @COUNTRANGE:

create or replace macro '@COUNTRANGE'(Any)
as '@COUNT(SKIPMISSING, @RANGE(@@S))';

Copying Custom-Defined Macros

You can copy custom-defined macros to any Essbase Server and application to which you have appropriate access.

  To copy a custom-defined macro, use a tool:

Tool

Topic

Location

Administration Services

Copying Custom-Defined Macros

Oracle Essbase Administration Services Online Help

MaxL

create macro

Oracle Essbase Technical Reference

Removing Custom-Defined Macros

The procedure for removing global custom-defined macros, which is more complex than that for removing local custom-defined macros, should be performed only by DBAs.

  To remove a custom-defined macro:

  1. Determine whether the macro is registered locally or globally.

    See Viewing Custom-Defined Macros.

  2. Verify that no calculation scripts or formulas are using the custom-defined macro.

  3. To remove the macro from the catalog of macros; use a tool:

    Tool

    Topic

    Location

    Administration Services

    Deleting Custom-Defined Macros

    Oracle Essbase Administration Services Online Help

    MaxL

    drop macro

    Oracle Essbase Technical Reference

  4. Restart all applications associated with the macro.

    See Refreshing the Catalog of Custom-Defined Macros.

The following MaxL statement removes the local macro @COUNTRANGE, which is used only in the Sample application:

drop macro Sample.'@COUNTRANGE';

The following MaxL statement removes the global macro @COUNTRANGE:

drop macro '@COUNTRANGE';

Refreshing the Catalog of Custom-Defined Macros

Refresh the catalog of custom-defined macros when you add, update, or remove macros.

  To refresh the catalog of custom-defined macros for all applications on a server, restart the server.

  To refresh the catalog of custom-defined macros for one application, use the refresh custom definitions MaxL statement.

The following MaxL statement refreshes the catalog of custom-defined macros for the Sample application:

refresh custom definition on application sample;