How to make query reusable (how to reduce the number of queries)

Check list of all posts

Layout Component Reference can be reused in Cognos, there is no way provided for Cognos to reuse query definition.  Making the query reusable is imperative when there are many reports developed to handle the similar business logic. Certainly, database layer and framework manager layer can be somehow leveraged to embed business logic, however, it is definitely not enough.  This document is intended to explore whether there is any way to make query reusable, at least the query can be copied without any change in other report. The sample below can be downloaded for detail review

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

It is requested to get List 1, 2 and 3, allowing user to choose any regions
The goal is to use a single query to get all three Lists.  If this query is good built, it can be simply copied to other report without any change.  Three ideas are explained as follows:

Conditional detail 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

This query above is not generic enough as it is required to define a prompt parameter.  

Conditional detail 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))

This improved query doesn’t need prompt at all; instead, it has the business logic built in, which can be used for any other report.

Consolidated query

The bottom line is that Cognos generates one query for each component, such as list, crosstab or chart. However, you can use a single query to serve different component.  In this case, three lists are using the same query.  You can clearly see  three queries Query2.1, 2.2 and 2.3 derived from Query2, when generating SQL/MDX.