Developing Custom-Defined Calculation Macros

This chapter explains how to develop custom-defined macros and how to use them in Hyperion Essbase calculation scripts and formulas. Custom-defined macros are written with Hyperion Essbase calculator functions and special macro functions. Macros enable you to combine multiple calculation functions into a single function.

For more details about the macro language syntax, rules, and examples of its use, see the online Technical Reference in the DOCS directory.

This chapter includes the following sections:

About Developing Custom-Defined Macros

Custom-defined macros use an internal Hyperion Essbase macro language that enables you to combine calculation functions and operate on multiple input parameters.

When developing and testing custom-defined macros, make sure to create and test new macros locally within a test application. You should register custom-defined macros globally only after you have tested them and are ready to use them as part of a production environment.

Hyperion Essbase requires that you have a security level of database designer or higher to create and manage custom-defined macros.

For more information about creating custom-defined macros locally or globally, see Developing Custom-Defined Calculation Macros.

Naming Custom-Defined Macros

The names of custom-defined macro must start with an "@" symbol; for example, @MYMACRO. The rest of a macro name can contain letters, numbers, and the following symbols: ";@", "#", "$", and "_". Macro names can not contain spaces. The names of custom-defined macros which are only called by other macros should start with "@_" to distinguish them from general use macros and functions.

Macros must have unique names. Macro names must be different from each other, from the names of custom-defined functions, and from the names of existing Hyperion Essbase calculation functions. If a Hyperion Essbase application contains a local macro that has the same name as a global macro, the local macro takes precedence and is used for calculation.

Creating Custom-Defined Macros

When you create a custom-defined macro, Hyperion Essbase records the macro definition and stores it for future use. You create the macro once, and then you can use it in formulas and calculation scripts until the macro is updated or removed from the Hyperion Essbase registry of macros.

You can create custom-defined macros in two ways: locally or globally. When you create a local custom-defined macro, the macro is only available in the application in which it was created. When you create a global custom-defined macro, the macro is available to all applications on the Hyperion Essbase server where it was created.

When you create a global custom-defined macro, all of your Hyperion Essbase applications can use it. Be sure you test custom-defined macros in a single application (and create them only in that application) before making them global macros.

CAUTION: Do not create macros as global (without the AppName. prefix in the macro name) when testing them, because this makes it more difficult to update them if you find problems. For more information about updating custom-defined macros, see Updating Custom-Defined Macros.

For more detailed information on the Hyperion Essbase MaxL create macro grammar, see the online Technical Reference in the DOCS directory. For more information about naming custom-defined macros, see Naming Custom-Defined Macros.

To create a local custom-defined macro for a specific application:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create macro statement to create the custom-defined macro; for example, type the following command:
```
MAXL> create or replace macro Sample.'@COUNTRANGE'(Any) AS 
   2> '@COUNT(SKIPMISSING, @RANGE(@@S))'
   3> spec '@COUNTRANGE(MemberRange)'
   4> comment 'counts all non-missing values';
```
Notice the AppName. prefix before the name of the macro. This prefix assigns the macro to an application, so the macro will only be available within that application.

To create a global custom-defined macro on a Hyperion Essbase server:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create macro statement to create the custom-defined macro; for example, type the following statement:
```
MAXL> create or replace macro '@COUNTRANGE'(Any) as 
   2> '@COUNT(SKIPMISSING, @RANGE(@@S)'
   3> spec '@COUNTRANGE(MemberRange)'
   4> comment 'counts all non-missing values';
```
Note: Notice that the AppName. prefix is not included in the name of the macro. The lack of a prefix makes a macro global.

To register the global custom-defined macro, an application must be running. If no applications are running when you register the global macro, Hyperion Essbase starts the application with the earliest creation date that contains a database.

Checking the Registration of Custom-Defined Macros

After creating custom-defined macros, you may want to determine whether a macro has been successfully created or whether a custom-defined macro is local or global.

To check the registration of a custom-defined macro on a Hyperion Essbase server:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the display macro statement to check the registration of the custom-defined macro; for example, type the following statement:
```
MAXL> display macro Sample; 
```
This statement displays a list of all macros registered for the named application (Sample) and any registered global macros. The display macro statement lists global macros without an application name to indicate that they are global. If the application contains a macro with the same name as a global macro, only the local macro is listed.

Using Custom-Defined Macros

After creating custom-defined macros, you can use them like native Hyperion Essbase calculation commands. Macros you created locally--using the AppName. prefix on the macro name--are only available for use in calculation scripts or formulas within the application in which they were created. If you created the custom-defined macros globally--without the AppName. prefix-- the macros are available to all calculation scripts and formulas on the Hyperion Essbase server where they were created.

For more information about creating custom-defined macros, see Creating Custom-Defined Macros.

To use a custom-defined macro:

Create a new calculation script or formula, or open an existing calculation script or formula.
- If the custom-defined macro was registered locally--within a specific application--you must use a calculation script or formula within that application.
- If the custom-defined macro was registered globally, you can use any calculation script or formula on the Hyperion Essbase server.
Add the custom-defined macro to a new or existing calculation script or formula. To use the custom-defined macro shown earlier in this chapter, type 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.

For more information about creating and running calculation scripts, see Developing Calc Scripts. For more information about creating and running formulas, see Developing Formulas.

Updating Custom-Defined Macros

When you update a custom-defined macro, you must determine whether the macro is registered locally or globally. There are different procedures for updating macros, depending on whether the macro is local or global.

For information on determining whether a custom-defined macro is local or global, see Checking the Registration of Custom-Defined Macros.

Updating Local Custom-Defined Macros

Local custom-defined macros are created using an AppName. prefix in the macro name and can be used only within the application where they were created. To update a local custom-defined macro, you must stop and restart the Hyperion Essbase application where the macro resides.

To update a local custom-defined macro:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create or replace macro statement to replace the custom-defined macro; for example, type the following command:
```
MAXL> create or replace macro Sample.'@COUNTRANGE'(Any) as 
   2> '@COUNT(SKIPMISSING, @RANGE(@@S))';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts the application where the macro resides.

Updating Global Custom-Defined Macros

Global custom-defined macros are created without an AppName. prefix in the macro name and are available in any application running on the Hyperion Essbase server where they were created.

Note:

When you update a custom-defined macro, Hyperion Essbase shuts down and restarts any running applications.

To update a global custom-defined macro:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create or replace macro statement to replace the custom-defined macro; for example, type the following command:
```
MAXL> create or replace macro '@COUNTRANGE'(Any) as 
   2> '@COUNT(SKIPMISSING, @RANGE(@@S))';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts any running applications.

Removing Custom-Defined Macros

When removing a custom-defined macro, you must first determine whether the macro is registered locally or globally. The procedure for removing global custom-defined macros is more complex than removing local custom-defined macros and should only be performed by a database administrator.

Before removing custom-defined macros, you should verify that no calculation scripts or formulas are using them. Global custom-defined macros can be used in calculation scripts and formulas across a Hyperion Essbase server, so you must verify that no calculation scripts or formulas on the server are using a global custom-defined macro before removing it.

For information on determining whether a custom-defined macro is local or global, see Checking the Registration of Custom-Defined Macros.

Removing Local Custom-Defined Macros

Local custom-defined macros are created using an AppName. prefix in the macro name and can be used only within the application where they were created. To remove a local custom-defined macro, you must stop and restart the Hyperion Essbase server where the macro was created.

To remove a local custom-defined macro:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the drop macro statement to remove the custom-defined macro; for example, type the following statement:
```
MAXL> drop macro Sample.'@COUNTRANGE';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts the application where the macro resides.

Removing Global Custom-Defined Macros

Global custom-defined macros are created without an AppName. prefix in the macro name and are available in any application running on the Hyperion Essbase server where they were created.

Global custom-defined macros can be used in calculation scripts and formulas across a Hyperion Essbase server, so you should verify that no calculation scripts or formulas are using a global custom-defined macro before removing it.

CAUTION: Only a database administrator should remove global custom-defined macros. Removal of global custom-defined macros should only be performed when no users are accessing Hyperion Essbase databases and no calculation routines are being performed.

Note: When you remove a custom-defined macro, Hyperion Essbase shuts down and restarts any running applications.

To remove a global custom-defined macro:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the drop macro statement to remove the custom-defined macro; for example, type the following statement:
```
MAXL> drop macro '@COUNTRANGE';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts the application where the macro resides.