Developing Custom-Defined Calculation Macros

28 Developing Custom-Defined Calculation Macros

Essbase custom-defined macros (CDM) enable you to combine multiple calculation functions into a single function. You can use them in calculation scripts or formulas.

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 be Database Manager or higher.

Custom defined macros are supported only for block storage cubes.

Naming Custom-Defined Macros

Essbase custom-defined macro names must be unique and start with the @ symbol. Local macros are prepended with the application name.

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 registers it in a catalog. Your macro can be global or local to an application. You can then use it 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 the create macro MaxL statement.

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

Use your custom-defined macros the same way you use Essbase native calculation commands: by calling them in calculation scripts or associating them with outline formulas.

To use a custom-defined macro:

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.
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.
Save the calculation script or formula, and then run it as usual.

Viewing Custom-Defined Macros

View a custom defined macro in Essbase to determine whether it has been successfully created, and whether it is local or global in scope.

To view a custom-defined macro, use the display macro MaxL statement.

Examples

The following MaxL statement displays only macros defined in the Sample application:

display macro on application Sample;

The following MaxL statement displays all global and local macros, if you have permission to view them:

display macro;

Updating Custom-Defined Macros

To change the syntax or behavior of a custom defined macro in Essbase, first determine whether it is local or global in scope, and then use the MaxL statement create or replace macro to update its definition.

To update a custom-defined macro:

Determine whether the macro is registered locally or globally.

See Viewing Custom-Defined Macros.
To update the macro definition; use the create macro MaxL statement.

Examples

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. You must be Database Manager or higher.

To copy a custom-defined macro, use the create or replace macro MaxL statement.

Example

The following MaxL statement creates a copy of @COVARIANCE macro:

create macro Sample.'@COVARIANCE2'(single, single) as '@COVARIANCE'(single, single);

Removing Custom-Defined Macros

To remove a custom defined macro from Essbase, first determine whether it is local or global in scope, and then use the MaxL drop macro statement.

To remove a custom-defined macro:

Determine whether the macro is registered locally or globally.

See Viewing Custom-Defined Macros.
Verify that no calculation scripts or formulas are using the custom-defined macro.
To remove the macro from the catalog of macros, use the drop macro MaxL statement.
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 Essbase catalog of custom-defined macros after you add, update, or remove macros. Use the MaxL statement refresh custom definitions if the changes are local, or restart the Essbase Server if the changes are global.

To refresh the catalog of custom-defined macros for all applications on a server, restart the server. See Start, Stop, and Check Servers for independent deployments, or Use Commands to Start, Stop, and View Status of Processes for stack deployment on OCI.

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

For example, the following MaxL statement refreshes the catalog of custom-defined macros for the Sample application:

refresh custom definition on application sample;