Utilize Prompt Macros
Date: 14/02/2017

By Rory Cornelius

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 (ie., ?date_prompt?) just doesn't handle that very well. What's a Cognos developer to do? A prompt macro can be used 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 the Macros

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

Utilize Prompt Macros_Data Item Expression.png

This will allow us to type in whatever function we want. If I type in “running-total”, here are the results:

Utilize Prompt Macros_running total results.png

Or I could type in "average":

Utilize Prompt Macros_average results.png

Now let's flip it around and do running-total of a dynamic data item.

Utilize Prompt Macros_running total dynamic data item.png

Utilize Prompt Macros_Data Item Expression running total.png

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:

Utilize Prompt Macros_revenue results.png

Running for “Gross Profit”:

Utilize Prompt Macros_gross profit.png

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.

Conclusion

If you would like to learn more about using prompt macros in Cognos, please contact us at:


Australia Singapore, Philippines, Thailand     United States
Cornerstone PMsquare | A Cornerstone Group Company     PMsquare

Call +61 1300 840 048 or
email Piers Wilson
Call +65 6635 1700 or
email Carsten Brandt
    Call +1 (630) 607 0570 or
    email Chris Loechel


Blog post shared courtesy of PMsquare US