Token Prompts

Paul Mendelson, March 20, 2019

In Cognos Analytics, prompts allow a user to modify the SQL being delivered to the database. The most traditional prompts are used in a filter or slicer, changing the where clause. In fact, all macro statements allow you to dynamically change the query before it’s sent to the database.

Before the query can be run, Cognos will first evaluate all the macro statements in a query.

The following macro:

#
case prompt('BucketNumber','integer',0)
when '0' then '[Sales].[Product Groups].[Top Bucket]'
when '1' then '[Sales].[Product Groups].[First Bucket]'
when '2' then '[Sales].[Product Groups].[Second Bucket]'
when '3' then '[Sales].[Product Groups].[Third Bucket]'
else '[Sales].[Product Groups].[Last Bucket]'
end
#

Will act exactly like [Sales].[Product Groups].[First Bucket] when 1 is passed to the BucketNumber parameter. This in turn will cause the SQL to become:

select “firstBucket” as “First Bucket” from productGroups

But that case macro is extremely unweildy – is there an easier way to modify the expressions on the fly?

An Easier Way

Macro statements have a number of functions available to it. One of the most common examples is localizing data based on the user’s defined language.

#'[Sales].[Product Groups].[Product Name ' + $runLocale +']'#

In that case for my user it would create the expression [Sales].[Product Groups].[Product Name en] – but in my model the field name is Product Name English – what can we do?

Parameter Maps allows a vlookup feature in macros, so if we were to build a parameter map that looks like:

We’d be able to do this:

#'[Sales].[Product Groups].[Product Name ' + $language_lookup
    +']'#

But let’s talk about another feature of prompting that is incredibly powerful. Using macro prompts, we can control all the parameters of a function. Take this expression based on the Go Sales (Analysis) package:

topCount([Sales (analysis)].[Sales staff].[Sales staff].[Staff name],5,
  , [Sales (analysis)].[Sales].[Revenue])

That says, in English, “return the top 5 staff names by revenue)”. But that 5 can be replaced with a prompt:

topCount([Sales (analysis)].[Sales staff].[Sales staff].[Staff name],
  , #prompt('count','integer','5')#,[Sales (analysis)].[Sales].[Revenue])

When the user changes the number in the count prompt, it will return that number of rows:

But now we want to make the level prompted. We want the users to be able to see the top N sales staff, or the top N products.

topCount(
#prompt('level','token','[Sales (analysis)].[Sales staff].
 , [Sales staff].[Staff name]')#
 , #prompt('count','integer','5')#
 , [Sales (analysis)].[Sales].[Revenue]
)

By default, it will show us the staff name, but if we were to pass [Sales (analysis)].[Products].[Products].[Product] to the parameter, we’d see top 5 products.

Now the fun part. What if we want to let the user to choose bottom or top count? Remember, the token prompts lets us pass expression fragments. So long as the expression resolves to a valid statement, it’s fine. In this case, because it’s possible for there to be staff or retailers with null revenue, we’ll wrap the level in a filter function to filter out the nulls.

#prompt('topOrBottom','token','top')#Count(
 filter(#prompt('level','token','[Sales (analysis)].[Sales staff]
   , [Sales staff].[Staff name]')#,[Sales (analysis)].[Sales].
   , [Revenue] is not null)
 , #prompt('count','integer','5')#
 , [Sales (analysis)].[Sales].[Revenue]
)

You can view the report here: Token Prompts Report

Conclusion

Ultimately macros allow us to make extremely complex requirements in a very simple and straightforward way. Without macros, this requirement would have required some crazy case statements (not allowed in MDX), or multiple queries and a series of conditional blocks. A headache to support, and a nightmare to change. Macros: saving time and making life easy. Be sure to subscribe to our analytics newsletter for data and analytics news, updates, and insights delivered directly to your inbox.

Next Steps

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.