Using query macros
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 Analytics - Reporting includes the same capabilities as macros used in Framework Manager. However, Reporting 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.