Utilize Prompt Macros in Cognos
Have you ever received a requirement for a prompt that gives the option of selecting a date but also defaults to the current day? The typical question mark prompt syntax (i.e. ?date_prompt?) just doesn’t handle that very well. What’s a Cognos developer to do? In this article I will show you how to use prompt macros in Cognos to accomplish this goal and many other more complex reporting requirements.
Prompt Macros Defined
Let’s start with a structure for a prompt macro and define each of the different parameters:
#prompt/promptmany(‘parameterName’, ‘dataType’, ‘defaultValue’, ‘leadingText’, ‘source’, ‘trailingText’)#parameterName – is just the name of the parameter that you are using. It would be the same name that you put between the question marks for a regular prompt (i.e. ?parameterName?)
dataType – must be one of the Cognos standard data types. It uses String by default.
defaultValue – is optional. If it is provided in the macro, the prompt becomes optional. It must match the provided data type.
leadingText – is also optional and is used as a prefix if a value is provided for the prompt. For example, it could be
#prompt(‘orderYear’, ‘integer’, ‘year(getDate())’,‘Order_Year = ‘) which would allow you to dynamically add a predicate to a where-clause in your SQL. If a prompt value of “2016” was provided, the macro would translate to
“Order_Year = 2016.”source – can optionally be used to create a tree or list prompt. It can be a field name or even a function. For instance,
#prompt(‘product’, ‘memberuniquename’, ”, ”, ‘filter([cube].[ProductDimension].[ProductHierarchy].[Product],[Measures].[Sales]>1000′))# will create a list of products to choose from that allhave Sales greater than 1,000.
trailingText – is similar to leadingText except it goes after the prompt value. This can
be especially useful in an optional promptmany filter.
Using Prompt Macros in Cognos
Let’s go through a few different examples of how a prompt macro could be used.
Example #1: Optional with Default to Current Year
The typical question mark notation has no option to provide your own values. However,
using a macro gives us more functionality.
#prompt(‘year’,’integer’,’extract(year, CURRENT_DATE)’)#Now the year prompt is optional, and if nothing is selected, the year from the current
date is used.
Example #2: Optional Multi-Select Prompt
In this example, we want to create an optional multi-select prompt. The tricky part is that
it needs to be added to the end of an existing filter, so the “and dept_id in” part of the
predicate needs to be dynamically added if a department is selected. Here is what the
prompt would look like:
#promptmany(‘departments’, ‘integer’, ‘and 1=1’, ‘ and dept_id in (‘, ”, ‘) ‘)If “departments” are selected, this will append the full statement needed: “and dept_id in
(1,2,3)”. If no department is selected, “and 1=1” is added. This dummy predicate is
needed to make sure the prompt is optional. If no value is in the defaultText parameter,
it would make the prompt required.
Prompt Macros in Cognos Using Token Data Type
Another powerful tool that can be used with prompt macros is the token datatype.
Instead of doing any sort of validation on the input as an “integer” datatype would make
sure a number is entered or similarly, a “date” datatype would require the appropriate
format, a token takes whatever is entered. This value is then placed directly in the data
item or filter. You can change functions or even data items on the fly based on a
prompt.
Let’s look an example of changing an aggregate function dynamically. We will create a
simple report showing Revenue and Gross Profit by year and then add another data
item for our aggregate.
Next Steps
If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.