Different Views of Same Query

[Bonediggler]

Experts:

How do I create different views of the same query on different report pages?  For example the query pulls in data from a large table, and through some calculations it categorizes sales by region.  I would then want region A on page 1, region B on page 2 and so on - but without creating a query for Region A, another query for region B etc.

Thank you!

[CognosPaul]

The simple answer is to use a page set. In the page explorer, drag in a page set. Add the regions you want to query it creates. Drag a page into the detail section. Every object inside that page can be set with a master/detail relationship to the page set query. It will generate a separate page for each region you have. Don't forget to add the regions to the properties of the page set, or somewhere on the page.

[Bonediggler]

Thanks Paul -

On a related note, is it possible to write a query, and then query off that query?  My background is more in SQL and I think in this instance I would write a CTE which pulls in all needed data and then write queries off that CTE for different report pages.

Unfortunately the SQL object is disabled in Report Studio.

Thank you!

[CognosPaul]

It is possible to create a series of derived queries based on the original.

So you'd create your base query with all of the fields you need, plus the region. You would then drag in more queries for each region. Drag the base query to the right of each of the new queries. Inside the new query, you can then pull all of the fields from the base query, and filter those results by a specific region.

The problem with that is that it's a lot of work, increases the complexity of the report, and doesn't actually do anything the master/detail wouldn't. If you needed to set specific filters for each region, then it may be worth defining each derived query separately.

In terms of performance, there are several configuration changes you can make depending on your version to improve the performance of the master/detail or nested queries. Take a look at this page: http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html

[Bonediggler]

Will do.  Thanks Paul!

[Bonediggler]

A follow up to this -

The detail pages can filter the master query.  Can they also aggregate data?  I.e. if the master page (or query) contains all raw data needed for the report, can the detail pages not only filter by region but aggregate the filtered data, perform ranking functions etc ..... without creating separate queries for this?

[CognosPaul]

It depends where the aggregation is done and if you've set the tuning correctly. If you're including the region, the results should already be aggregated correctly for each region. Ranking should use the for clause on the region. Ultimately Cognos should only need to retrieve specific rows for each detail.