Developing Custom-Defined Calculation Macros

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:

Understanding Custom-Defined Macros

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.

Viewing 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:

  1. Start OLAP Server.
  2. Start Application Manager and connect to the server.
  3. Select Server > Custom-Defined Macros. Essbase displays the Custom Defined Macros Manager, where all the macros you have created are listed.

To view information about custom-defined macros using MaxL, use this procedure:

  1. Start OLAP Server.
  2. Start MaxL Shell and use the display macro statement. For example, to view information about all the custom-defined macros on the Sample application, you would type this statement:
  3. 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.

Creating Custom-Defined Macros

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:

Understanding Scope

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.

Naming Custom-Defined Macros

Remember these requirements when you create macro names:

Creating Macros Using MaxL

To create a custom-defined macro, use this procedure:

  1. Start OLAP Server.
  2. Start MaxL Shell at the operating system command prompt and log on to the server.
  3. Use the create macro statement to create the custom-defined macro. The name of the macro is different, depending on whether it is a global macro, or if it is local.
  4. Refresh the catalog of custom-defined macros for a single application by issuing this statement:
  5. MAXL> refresh custom definition on application sample; 
    

    You can refresh the catalog for all applications on a server by restarting the server.

Creating Macros Using Application Manager

To create a custom-defined macro, global or local, use this procedure:

  1. Start the OLAP Server.
  2. Start the Application Manager and connect to the server.
  3. Click Server > Custom-Defined Macros. Essbase displays the Custom Defined Macro Manager:
  4. Select a server to which you are connected, where the custom-defined macro will be used. If the custom-defined macro is global, disregard the Application selection box. If the custom-defined macro is not global, select the application where the custom-defined macro will be used.
  5. Click New to create a new custom-defined macro. Essbase displays the Custom Defined Macro Editor:
  6. Select the appropriate scope for the custom-defined macro: <Global> if the custom-defined macro will be available for all applications on the server, or select an application name, such as Sample in this example, if the custom-defined macro will be available for only one application.
  7. In the Name text box, leave the "@" symbol as the first character of the custom-defined macro name, and then add the rest of the name, for example COUNTRANGE. Notice that the corresponding MaxL statements which Application Manager uses to create the custom-defined macro are displayed in the MaxL Statement box. Review the contents of this box as you add entries in the editor to ensure accuracy.
  8. Add the correct information to the Signature, Expansion, Spec, and optional Comments box:
  9. Review the information and when it is correct, click OK to create the macro. Essbase displays the macro in the Custom Defined Macro Manager.

Using Custom-Defined Macros

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:

  1. Create a new calculation script or formula, or open an existing calculation script or formula.
  2. 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:
  3. 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.

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

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

Changing 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, 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.

Changing Custom-Defined Macros Using MaxL

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:

  1. Start the server.
  2. Start MaxL Shell at the operating system command prompt and log on to the server.
  3. Use the create or replace macro statement to replace the custom-defined macro. The syntax is slightly different, depending on whether the macro is local or global.
  4. Refresh the catalog of custom-defined macros for a single application by issuing this statement:
  5. MAXL> refresh custom definition on application sample; 
    

    You can refresh the catalog for all applications on a server by restarting the server.

Changing a Custom-Defined Macro Using Application Manager

To change local or global custom-defined macros using Application Manager, use this procedure:

  1. Start the OLAP Server.
  2. Start the Application Manager and connect to the server.
  3. Click Server > Custom-Defined Macros. Essbase displays the Custom Defined Macro Manager:
  4. Select a server to which you are connected, where the custom-defined macro will be used. If the custom-defined macro is global, disregard the Application selection box. If the custom-defined macro is not global, select the application where the custom-defined macro will be used.
  5. Select the macro you want to change.
  6. Click Edit to change the custom-defined macro. Essbase displays the Custom Defined Macro Editor:
  7. Change the macro as needed.
  8. Review the information and when it is correct, click OK to save your changes the macro. Essbase displays the macro in the Custom Defined Macro Manager.

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 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.

Removing a Custom-Defined Macros Using MaxL

To remove a custom-defined macro, use this procedure:

  1. Start the server.
  2. Start MaxL Shell at the operating system command prompt and log on to the server.
  3. Use the drop macro statement to remove the custom-defined macro. The syntax is slightly different, depending on whether the macro you are removing is local or global.
  4. Refresh the catalog of custom-defined macros for a single application by issuing this statement:
  5. 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.

Removing A Custom-Defined Macro Using Application Manager

To change local or global custom-defined macros using Application Manager, use this procedure:

  1. Start the OLAP Server.
  2. Start the Application Manager and connect to the server.
  3. Click Server > Custom-Defined Macros. Essbase displays the Custom Defined Macro Manager:
  4. Select a server to which you are connected, where the custom-defined macro will be used. If the custom-defined macro is global, disregard the Application selection box. If the custom-defined macro is not global, select the application where the custom-defined macro will be used.
  5. Select the macro you want to change.
  6. Essbase displays the Custom Defined Macro Manager. The macro you deleted should not be listed.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com