Thursday, April 18, 2013

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

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


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.


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.


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

No comments:

Post a Comment