From the User Guide:

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.

These two small sentences belie the great value macros offer to report developers. In my own words, macros allow the author to dynamically alter the SQL at runtime.

Practically this allows the author to specify specific fields, tables, or even databases at run time. The author can create filters that check users’ credentials, or redirect users to different tables based on the month or the users’ language settings.

All macros are delimited with two #s. The following is an acceptable macro: #sq(‘Hello World!’)# The sq() function will wrap a string in single quotes. If you attempted to run the same macro without the sq(), Cognos would try to interpret Hello World! as a function call, yielding you a syntax error. Understanding this behavior is key to building complex macros.

Let’s say that you’re working on a database that has materialized views for each month. The tables are named v_YYYYMM: 201001, 201002, 201003, etc… Your report needs to run against the current month. You can use the macro function timestampMask with the parameter $current_timestamp.

[ns].[table].#sb(‘v_’ + timestampMask($current_timestamp,’yyyymm’))#

$current_timestamp checks the time on the Cognos server (a side note, I am pretty sure that it checks the dispatcher. If you have multiple dispatchers in different time zones this can cause some issues unless they’re all synced). Lets say that returns 2010-09-18 22:20:31.000+02:00. timestampMask() will take the timestamp and return it with the specified format: 201009. sb() will then wrap ‘v_’ and 201009 in square brackets: [v_201009]. Cognos will then attempt to run [ns].[table].[v_201009].

Ultimately this will allow you to run cleaner and faster SQL.