,

Ironside Tech Tip: Using Macros in Cognos

keys closeup using macros concept
/by

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 using macros.

Prompt Page Layout and Settings:

Here is what our resulting prompt page will look like:

using macros static choices

using macros static choice code

Steps for building the prompt page:

  1. Create the date period prompt.
  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.

using macros static choice added to period prompts

Your prompt page should now be completed; now on to building 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 using 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