Report Studio Cookbook: Using Macros in Cognos

Difficulty: Easy

Required Ingredients: Prompt macros and static values within a prompt parameter.

Scenario:

You have some existing reports that prompt on Year and Month.

Users would like a better experience by having default selections provided for them. For scheduling purposes you’d also like to set relative time so the report can run based on current month and current year. You can accomplish this with prompt macros.

Prompt Page Layout and Settings:

Here is what our resulting prompt page will look like:

Page Layout and Settings

Parameter Name

Steps for building the prompt page:

  1. Create the date period prompts
  2. Build a query subject that retrieves valid periods from the Time dimension so that a user can select a period.
  3. Add a static choice USE VALUE of 9999 and DISPLAY VALUE of Current Year to the mandatory Year prompt.
  4. Add a static choice USE VALUE of 9999 and DISPLAY VALUE of Current Month to the optional Month prompt.

Data Period Prompts

Your prompt page should now be completed, now on to build the query.

Building the Report Query

Building the report query

The prompt and promptmany macro perform the exact same functionality and the only difference between them is how they are related to the prompt parameter. If you have a value prompt which allows multi-select capabilities then you would use the promptmany macro, otherwise for single select prompts you would utilize the prompt macro.

Prompt data

Promptmany Macro

 Report Output

Report Output

Comparison

The new report prompts we created with macros now allows users to consume the reports against a relative time (current year and current month). Month is no longer a required prompt but will default if empty.

With the prompt page created in this fashion, scheduled reports can be created that run against the new relative time periods.

Prompt Macros

Related Posts: