How to make Cognos reports reusable - Explore the new idea of authoring Cognos reports – part 5

Check list of all posts and the book 

    

(please check the part 1 , part 2 , part 3 and Part 4)
Make query reusable - revisited

Context

Besides there are a few ways to reuse query explored in part1, there is another method to reuse query. This method takes advantage of prompt token and make whole query be controllable.
In centralized report development, there are many cases where query is very similar, the only difference is to group by different level, such as territory, province, or to select different item, such as product, location. This document is intended to provide a method that all query items are controlled by a parent query. The parent query can be a simple singleton, or a complicated query.

Sample

Create a query that can be customized for any query items, including measure and filter.
Please see attach screenshot below; the report specification (version 10.2) can be downloaded for detail review.

Solution

Create a report query with prompt  token


QueryItem1: #prompt ('QueryItem1','token')#
QueryItem2: #prompt ('QueryItem2','token')#
Filter1: #prompt ('Filter1','token')#
Measure1: #prompt ('Measure1','token')#

Create a control query

QueryItem1: '[Sales (query)].[Products].[Product line]'
QueryItem2: '[Sales (query)].[Retailers].[Region]'
Filter1: '[Sales (query)].[Order method].[Order method type] = ''Telephone'''
Measure1: '[Sales (query)].[Sales].[Revenue]'

Create page with parent Singleton and child crosstab

Singleton -- controlQuery
Crosstab – reportQuery

Create master detail relationship