This chapter explains how to develop custom-defined macros and how to use them in calculation scripts and formulas. Custom-defined macros are written with 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 Technical Reference in the docs directory.
This chapter includes the following sections:
Custom-defined macros use an internal 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.
Essbase requires that you have a security level of database designer or higher to create and manage 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. You can find out information about existing custom-defined macros using either MaxL or Application Manager.
To view information about custom-defined macros using Application Manager, use this procedure:
To view information about custom-defined macros using MaxL, use this procedure:
MAXL> display macro on application 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.
For more information about similar statements in MaxL, see the Technical Reference in the docs directory.
When you create a custom-defined macro, records the macro definition and stores it for future use. Create the macro once, and then you can use it in formulas and calculation scripts until the macro is updated or removed from the catalog of macros.
Use these sections to understand more about creating custom-defined macros and to find instructions for creating them using MaxL or Application Manager:
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 server where it was created.
When you create a global custom-defined macro, all of your 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 (using a macro name without the AppName. prefix) 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 Changing Custom-Defined Macros.
For more detailed information on the MaxL create macro grammar, see the Technical Reference in the docs directory. For rules about naming custom-defined macros, see Naming Custom-Defined Macros.
Remember these requirements when you create macro names:
To create a custom-defined macro, use this procedure:
MAXL>create macro Sample.'@COUNTRANGE'(Any) AS 2>'@COUNT(SKIPMISSING, @RANGE(@@S))' 3>spec '@COUNTRANGE(MemberRange)' 4>comment 'counts all non-missing values';
MAXL>create macro'@COUNTRANGE'(Any) AS 2>'@COUNT(SKIPMISSING, @RANGE(@@S))' 3>spec '@COUNTRANGE(MemberRange)' 4>comment 'counts all non-missing values';Be sure to add the application name plus a period (.) as a prefix before the name of the local macro. In this example, Sample is the prefix for the local macro name. This prefix assigns the macro to an application, so the macro will only be available within that application.
MAXL> refresh custom definition on application sample;You can refresh the catalog for all applications on a server by restarting the server.
To create a custom-defined macro, global or local, use this procedure:
After creating custom-defined macros, you can use them like native 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 server where they were created.
For more information about creating custom-defined macros, see Creating Custom-Defined Macros.
To use a custom-defined macro:
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.
For more information about creating and running calculation scripts, see Developing Calculation Scripts. For more information about creating and running formulas, see Developing Formulas.
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, and also depending on which client you use:
For information on determining whether a custom-defined macro is local or global, see Viewing 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 change a local custom-defined macro using MaxL, use this procedure:
MAXL> create or replace macro Sample.'@COUNTRANGE'(Any) 2> as '@COUNT(SKIPMISSING, @RANGE(@@S))';
MAXL> create or replace macro '@COUNTRANGE'(Any) as 2> '@COUNT(SKIPMISSING, @RANGE(@@S))';
MAXL> refresh custom definition on application sample;You can refresh the catalog for all applications on a server by restarting the server.
To change local or global custom-defined macros using Application Manager, use this procedure:
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 that for 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 server, so you must verify that no calculation scripts or formulas on the server are using a global custom-defined macro before removing it.
You can use MaxL or Application Manager to remove custom-defined macros:
For information on determining whether a custom-defined macro is local or global, see Viewing Custom-Defined Macros.
To remove a custom-defined macro, use this procedure:
MAXL> drop macro Sample.'@COUNTRANGE';
MAXL> drop macro '@COUNTRANGE';
MAXL> refresh custom definition on application sample;You can refresh the catalog for all applications on a server by restarting the server.
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 databases and no calculation routines are being performed.
To change local or global custom-defined macros using Application Manager, use this procedure:
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |