How to make cognos reports reusable - Explore the new idea of authoring Cognos reports – Part 1

Make query reusable

1 Introduction
2 Problem of current approach
2.1 Current approach
2.2 The major problems
2.2.1 Difficult to author
2.2.2 Difficult to maintain
2.2.3 Difficult to migrate
3 Solution
3.1 Terminology
 3.1.1 Query
 3.1.2 Data container
 3.1.3 Page
 3.1.4 Page set
3.2 Approach
3.2.1 Make query reusable
3.2.2 Make data container reusable
3.2.3 Make page reusable
4 Implementation
4.1 Sample
4.2 Make query reusable
4.2.1 Make query item reusable
4.2.1.1 Case 1: Page set
4.2.1.2 Case 2: Page
4.2.1.3 Case 3: data container – Crosstab
4.2.1.4 Case 4: data container – Product Line Chart
4.2.1.5 Case 5: data container – Product Type Chart
4.2.2 Make Filter reusable
4.2.2.1 Make logic filter reusable
4.2.2.2 Make prompt filter reusable
4.2.3 Use property to control query
4.2.3.1 Case 1: page set
4.2.3.2 Case 2: page
4.2.3.3 Case 3: Crosstab Node member
4.2.3.4 Case 4: Chart Node member

Introduction

The initial motive of researching a new concept is from my consulting job as Cognos consultant, where I’ve spent most of my time to author Cognos reports. It occurs to me that current approach of authoring and migrating reports doesn’t work properly. The real issue is that we don’t make Cognos reports reusable. Given that Cognos report engine doesn’t provide report reusability, this document is intended to explore the practical approach to make report reusable based on current report engine.

Problem of current approach

Current approach

Each report is independently authored by Cognos report developer based on framework manager. As business users want to see report from different angles, many reports based on same framework manager model are developed. The most used method to author these similar reports is copy and paste.

The major problems

Difficult to author

As there is no common library provided, report authors need to figure out where these reports are and somehow to filter out the needed part.  Such a part including query and report is written in the context of the report. There are two tasks to be done: 1) report developers need to somehow understand functionalities of that report; and 2) Report developers need to retrofit these queries and reports into their own report.

Difficult to maintain

As part of newly develop report is copied from another report, there will result in many copies of same logic. When business requirement changes, report developer needs to change all these copies. There are a few challenges:

Need to figure out how many such copies are request to change

Change can’t apply with simple copy and paste, as these copies are not exactly same in different report.

Difficult to migrate

Migration happens when Framework manager changes, or version changes. The similar issues occur as when redeveloper deal with maintenance.

Solution

Terminology

Query

It is Cognos query, regardless of when it is relational and dimensional. It can be complicated and simple query.

Data container

The typical data container is list, crosstab, and chart.

Page

Page contains one or more than one data containers. By the way, query can be used at page level.

Page set

Page set contains one or more than one pages. In addition, burst report can be considered as page set as well.

Approach

Make query reusable

Query is used in report at all levels, including page set (including burst), page and data containers. The query designed in report is different from query executed against data sources. Why? Cognos only takes query items that are used in report to generate real query against data source. Therefore, you can design only one query with many data items, which can serve all data containers. In other word, you can make query reusable for many different data containers, pages or even page sets.

Make data container reusable

In case when more than one data container, such a chart are used in a page, you can use master detail relationship. Define a single chart with all formats, along with title, and you can make this chart reusable. if such a data container is used by multiple pages, then you can use page set make it reusable.  Furthermore, if it is used by multiple reports, we can make this container in a common used library.

Make page reusable

The similar idea can be applied for page. In multiple page situation, page set can be used as parent of pages and assemble report. Again, in case when this page used in multiple report, it should copied into common library.

Implementation

The most important point is to make query reusable, as it is the foundation of this whole reusability approach. In order to illustrate this approach, a sample is introduced; please download report specification for review.

Sample

Create a report book to loop through all product line. This report involves page set, page, crosstab and chart. It uses a single query.

Sample page for camping equipment

Query 1

Page set

Page

Data container / Crosstab / Chart

Make query reusable

A query is composed of query item and filters.

Make query item reusable

As the whole sample report uses a single defined query Query1, it is actually used by following cases by Cognos engine

Case 1: Page set

Product line	X
Product type
Year
Revenue
Gross profit
Margin
% Margin

Case 2: Page

Product line	X
Product type
Year
Revenue
Gross profit
Margin
% Margin

Case 3: data container – Crosstab

Product Line crosstab node member

Product line	X
Product type
Year	X
Revenue	X
Gross profit	X
Margin	X
% Margin	X

Product Type crosstab node member

Product line	X
Product type	X
Year	X
Revenue	X
Gross profit	X
Margin	X
% Margin	X

Case 4: data container – Product Line Chart

Product line	X
Product type
Year	X
Revenue	X
Gross profit
Margin
% Margin

Case 5: data container – Product Type Chart

Product line
Product type	X
Year	X
Revenue	X
Gross profit
Margin
% Margin

Make Filter reusable

There are two different filters, logic filter and prompt filters. There was some detail sample in old post

Make logic filter reusable

There are two ways to make logic filter reusable. Using Cognos sample, assume that revenue can be calculated based on order methods as follows:

If Region  is  'Americas', only Order method type 'Fax','Mail' are included

If Region  is  'Asia Pacific',  only Order method type 'E-mail','Web' are included

All other regions, all Order method types are included

Conditional logic filter based on prompt

case  when (?pRegion? = 'Americas')     

then  ([Sales (query)].[Order method].[Order method type] in ('Fax','Mail') )

         when (?pRegion? = 'Asia Pacific')

then  ([Sales (query)].[Order method].[Order method type] in ('E-mail','Web') )

         else (1=1)

end


(?Level? = 'SBR' and ?TerritoryId? in ('2101','2102') and [Fact Table].[Dim Table].[Territory Id] in ('2101','2102')) or (?Level? = 'SBR' and ?TerritoryId? not in ('2101','2102') and [Fact Table].[Dim Table].[Territory Id] = ?TerritoryId?) or (?Level? <> 'SBR' and 1=1)

Conditional logic filter with built in business logic

( ([Sales (query)].[Retailers].[Region] ='Americas'

       and [Sales (query)].[Order method].[Order method type] in ('Fax','Mail'))

       or ([Sales (query)].[Retailers].[Region] <>'Americas' and 1=1))

AND

( ([Sales (query)].[Retailers].[Region] ='Asia Pacific'

and [Sales (query)].[Order method].[Order method type] in ('E-mail','Web'))

or ([Sales (query)].[Retailers].[Region] <>'Asia Pacific' and 1=1))

Make prompt filter reusable

The key concept is to make filter as optional filter. It will turn query into a similar behavior as query items. For example, define three optional filter p_ProductLine, p_ProductType and p_Product. It will end up all following 8 combinations, theoretically.

1	No filters
2	p_ProductLine
3	p_ProductType
4	p_Product
5	p_ProductLine, p_ProductType
6	p_ProductType ,p_Product
7	p_ProductLine,p_Product
8	p_ProductLine, p_ProductType ,p_Product

Use property to control query

Case 1: page set

Case 2: page

Case 3: Crosstab Node member

Case 4: Chart Node member