Thursday, April 11, 2013

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

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)  


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

#'[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') +']'#

  1. 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?)

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

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

1 comment:

  1. Hi Charles, looks like very sophisticated way of making things dynamic but I don't get how you connect items--could you explain what the #Fact_Sell Through . . ." line in teh second 1.a does? (Mayw ant to rename that to 1.c. Maybe give a screenshot of the process in action at runtime? It seems brilliant but it is somewhat hard to follow without the runtime example. . .

    Thanks for posting tho!