How to design Cognos interactive reports 07 - Make Group by flexible

Check list of all posts

1. Context

Besides making columns and filters flexible, we can also make group by flexible. Behind the scene, group by is defined as query item. If we could make query item be flexible as defined by prompt token, then we can make group by flexible. This document will demonstrate the idea to use prompt token to make Group by flexible. Please see attached report specification for review (version 10.2 with sample Package GoSales)

1. Sample

The crosstab is generated based on two Group by selected. When business users select Region, then product line, crosstab will be generated accordingly.

2. Prompt

1. First value prompt

2. Second value prompt

In case when we don’t want to have second group, we can put ‘None’ there to indicate that no group by 2. The style variable can be used to suppress group by 2 in report. Please note that quotation mark is needed for use value as ‘None’,  as it is considered as constant when querying .

3. Query

The key to make this simple report flexible is to apply prompt token

#prompt ('pGroupBy1','token')#  and

#prompt ('pGroupBy2','token')#

4. Layout

Style variable is defined as below

It will apply for both member and crosstab cell.