How to design Cognos interactive reports 04 - Make filters flexible with ID and description

1. Check list of all posts

   Context

Sometimes, description is same while data ID is different. We need to make filters show to business users with both ID and description. The solution is to make query item with combined ID and description as needed as use value. This document will demonstrate the idea to use prompt token to make ID and description together. Please see attached report specification for review (version 10.2 with sample Package GoSales)

2. Sample

We can make filters as combined data items; in this case, we can apply ID and description together to give business users a much clear idea to filter data.

3. Prompt

1. First value prompt

cast([Sales (query)].[Order method].[Order method code], char(1)) + ' - ' + [Sales (query)].[Order method].[Order method type]

2. Second List box

This filter #prompt ('pFilter1_Column','token')#  makes it possible to have values depending on the selection of value prompt. Please also note that cascade source is specified for list box.

4. Query

The key to make filters flexible is to apply prompt token

#Prompt('pFilter1_Column', 'token')# in (?pFilter1_Values?)

Checking generated SQL statement as below

................

where CAST( Order_method12.ORDER_METHOD_CODE AS CHAR( 1 ) )+' - '+Order_method12.ORDER_METHOD_EN in ('1 - Fax', '2 - Telephone')

...............

5. Layout