Add a query macro

Add a query macro to allow run-time changes to be made to SQL queries.

About this task

When you reference a parameter, you must do the following:

  • Use a number sign (#) at the beginning and end of each set of one or more parameters.

    Everything between the number signs is treated as a macro expression, which is processed at runtime. Framework Manager removes anything that is outside the number signs.

  • Precede each parameter map entry with a dollar sign ($)
  • Use a name that starts with an alpha character (a..z, A..Z)

Do not insert macros between existing quotation marks or square brackets because IBM® Cognos® Analytics - Reporting does not execute anything within these elements.

Procedure

  1. To add a macro to an expression, in the Expression Editor, click the macros tab macros tab.
  2. Click the Queries icon Queries icon, and click Queries.
  3. If the query to which you are adding a macro was built by using your own SQL, do the following:
    • Click the SQL object that is linked to the query.
    • In the Properties pane, double-click the SQL property.
    • In the SQL dialog box, type the macro.
  4. If the query to which you are adding a macro was not built by using your own SQL, do the following:
    • Click the query.
    • In the Properties pane, double-click the Generated SQL/MDX property.
    • In the Generated SQL/MDX dialog box, click Convert.
    • In the SQL dialog box, type the macro.