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 all have 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.
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
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.
This will allow us to type in whatever function we want. If I type in “running-total”, here
are the results:
Or I could type in “average”:
Now let’s flip it around and do running-total of a dynamic data item.
Notice the “sb” function within the macro. This is a macro function that adds square brackets around whatever is inside. It’s needed here to get the appropriate syntax for a data item. I could have also used the leading and trailing text parameters within the prompt for another option.
Now when I run this for “Revenue” here are the results:
Running for “Gross Profit”:
This gives the report consumer a lot of flexibility on the end result. We could easily create a dropdown list with all of the relevant aggregate functions and another for data items for the consumer to select to make this more user friendly. I hope this helps you use prompt macros in Cognos effectively for better report development.
Subscribe to our e-newsletter for more technical articles and updates delivered directly to your inbox.
If you would like to learn more how PMsquare can help your organization please reach out to us at:
Singapore, Philippines, Thailand
|PMsquare LLC||Cornerstone||PMsquare | A Cornerstone Company|
|P:+61 1300 840 048
E: Piers Wilson
|P:+65 6635 1700
E: Carsten Brandt
Blog post shared courtesy of PMsquare LLC