Creating custom macros

Macros that you create are available when configuring processes in a flowchart and defining derived fields.

About this task

Unica Campaign supports the following types of custom macros, which in turn support an unlimited number of variables:

  • Custom macros that use an expression
  • Custom macros that use raw SQL
  • Custom macros that use raw SQL and include a specified value

Support for raw SQL improves performance, allowing complex transactions to be carried out in the database rather than having the raw data filtered and manipulated in the application server.

Important: Non-technical users are able to use custom macros; therefore, when you create a custom macro you should describe how it works very carefully, place similar kinds of macros in special folders, and so on. This approach can help reduce the possibility that someone might use a custom macro incorrectly and retrieve data that they were not expecting.

Procedure

  1. Open a flowchart in Edit mode.
  2. Open the Options menu and select Custom macros.
  3. In the Custom macros dialog, click New item.
  4. In the Save under list, select the folder where you want to save the macro. If no folders exist, use the default, None.
  5. In the Name field, enter a name and declaration for the macro so that it can be referenced.

    Use the following syntax: MacroName(var1,var2,...)

    For example: GenGroupBy(id,val1,table,val2)

    The MacroName must be unique and alphanumeric. It can include underscores (_) but not spaces.

    Note: If a custom macro has the same name as a built-in macro, the custom macro takes precedence. To avoid confusion, do not name custom macros with operator names or names that are the same as the built-in macros. The exception would be if you specifically want the new custom macro to always be used instead of the built-in macro.
    Important: The variable names must match the variable names in the custom macro definition in the Expression window, and they must be expressed as a comma-separated list in parentheses.
  6. From the Security policy list, select a security policy for the new custom macro.
  7. Use the Note field to explain what the custom macro is designed to do and what each variable represents. Be sure to describe the macro carefully, so users understand how it works when they encounter it in lists.
  8. From the Expression type list, select the type of custom macro that you are creating:
    • HCL macro expression: For more information, see the HCL Macros User's Guide.
    • Raw SQL selecting ID list: If you select this option, you must select a Database.
    • Raw SQL selecting ID + value: If you select this option, you must select a Database and a Value type. Be sure to select the correct value type. Otherwise, when you later attempt to profile this query, a "Mismatched Type" error results. If you select Text as the value type, fill in the Width (# bytes) field. You can obtain this information from the database. If you do not have access to the database or are unable to obtain the information, enter 256, the maximum width.
  9. Click in the Expression field to open the Specify selection criteria dialog.
  10. Create your query expression. You can use as many variables as you like. Variable syntax is alphanumeric, and the variable must be enclosed in angle brackets (< >). Operands (values and strings) and operators might be variables.
    Important: Do not use flowchart user variables in custom macro definitions because custom macros are global but flowchart user variables are not global.

    The following example shows a custom macro definition.


    Custom macro example
  11. Click Save.

    The custom macro is saved in the Items list.

  12. Close the Custom macros dialog.

Results

The macro can now be accessed by name for use in flowchart processes and in definitions for derived fields. If you need to edit the macro, select Custom macros from the Options menu.