How to use prompt token macro to make report more interactive and user friendly

Check list of all posts

Prompt tokens are metadata and rather than supplying values to a query they can supply the actual definition of what the query contains and how it is to behave. This process occurs before a request is sent to the data source and in fact can be used to define what is sent to the data source for execution. This macro brings all your imaginations to change Metadata and therefore results in a very interactive report. In case when report template is pretty stable, this approach is much better than conditional block, and “case when” query calculation.

This document is intended to provide a use case to demonstrate the power of cognos prompt token.

Requirement:  Provide a report to compare data between audit database and production database for sell through.

Main challenge:  There are 11 dimensions with sell through fact, therefore results in a great deal of possibilities, such as time frame break down by wholesaler, product, product group, location, outlet etc. Is there a way to give user an unlimited possibility as needed on the fly? The answer is yes!  The magic is prompt with token. (Cube may be solution, but it requires much bigger effort)  

Solution:  

Please see screenshot below, where columns, rows and filters are made dynamic based on user’s selection.

{Samples:
#'[Fact Sell Through Sales (Audit RDS)].[Dim Time (Audit RDS)].['+prompt('pDateX', 'token') +']'#
#substitute ( 'Audit RDS', 'DW', substitute ( 'Audit RDS', 'DW', prompt('pFilter1', 'token')))# in (?pFilter1_Value?)   }

1. Make columns  filter

In this case, columns are specified by period options as below

1. Period by
2. Date value based on the selection above : #'[Fact Sell Through Sales (Audit RDS)].[Dim Time (Audit RDS)].['+prompt('pDateX', 'token') +']'#

3. Columns are specified in query ( #'[Fact Sell Through Sales (Audit RDS)].[Dim Time (Audit RDS)].['+prompt('pDateX', 'token') +']'# ), while filter is specified as  [Date_X] in (?PDateX_Value?)

2. Make rows dynamic

It is the similar approach as 1), where the selection is displayed





and date item in query is defined as prompt with token

By the way, you can leverage macro by using all functions to get data item be dynamic, please see screen below

3. Make filters dynamic

Again, it is the similar idea as item 1), the only main difference is that data items are in detail filters but not in the query item