How to design Cognos interactive reports 12 - Make report with count condition

1. Check list of all posts

   Context

Report could be very long when business users select the lowest element for a dimension, such as product and customer. In this case report execution time will be very long, which is unacceptable. Then we can calculate the number of records that can be retrieved. If the number is too high, then we give business users a message and ask for adding more filters. This document will demonstrate the idea to make report with count condition. Please see attached report specification for review (version 10.2 with sample Package GoSales)

2. Sample

Sample below is simply show and message, when user choose product line and retailer country. This is the similar sample with Group by in other blog (How to design Cognos interactive reports 07), but it will only execute if the number of rows doesn’t exceed the limit.

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

4. Query

The bottom-line is that we need to get number of records. However, it is not easy to get number of records, as it has to be generated based on the whole report. Therefore, the data query becomes the sub query for getting count.

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

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

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

5. Layout

We need to assign countquery to a singleton, where singleton becomes a parent for a conditional block.

Conditional variable is defined as below

If it exceeds limit, then message,

Otherwise, main report will be generated with faked master-detail relationship.

Also note that group y 2 won’t be problem to count the records. Because query item is constant ‘NONE’, it won’t have impact on number of records.

6. Note

Actually, this idea may not so efficient.  To find number of records, Cognos needs to run the same query dynamically with group by 1 and 2, which will take some time as well.