Using Query Macros

A macro is a fragment of code that you can insert in the Select statement of a query or in an expression. For example, add a macro to insert a new data item containing the user's name.

You can include references to session parameters, parameter maps, and parameter map entries in macros. Parameter values are set when you run the query. For example, you can use the language session parameter to show only the data that matches the language setting for the current user.

Macros can be used in these different ways:

They can be inserted in the SQL.
An example is Select * from Country where Country.Name = #$myMap{$runLocale}#
They can supply an argument to a stored procedure query subject.
If a value is not hard-coded for the argument, the stored procedure query subject can be used to return different data.
They can be inserted in expressions, such as calculations and filters.
An example is a filter [gosales].[Sales staff].[Staff name] = #$UserLookUpMap{$UserId}#
They can be used as a parameter wizard.
Parameters can reference other parameters. An example is Map1, Key = en-us, Value = #$myMap{$UserId}#

You can also add query macros to the IBM® Cognos® Framework Manager model. For more information, see the Framework Manager User Guide.

Support for query macros in IBM Cognos Report Studio includes the same capabilities as macros used in Framework Manager. However, Report Studio query macros do not extend to the layout. Therefore, when making changes to the query using macros, you must bear in mind the side-effects on the layout. For example, if a macro removes a column from the query that the layout refers to, a run-time error will occur.