In This Section:
Understanding Custom-Defined Macros
Creating Custom-Defined Macros
Updating 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.
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.
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.
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
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';
You can use custom-defined macros like native calculation commands in calculation scripts or formulas.
To use a custom-defined macro:
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.
View a custom-defined macro to determine whether it has been successfully created, or whether it is local or global.
Tool | Topic | Location |
|---|---|---|
Administration Services | Editing Custom-Defined Macros | Oracle Essbase Administration Services Online Help |
MaxL | 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))';
You can copy custom-defined macros to any Essbase Server and application to which you have appropriate access.
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:
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';