How to use query item from another query without using join in relational mode (re# Cognos release 10.2.2. with Filter Join Optimization)

Check list of all posts

This document demonstrates two cases; please download the report specification with version 10.1.1 for review if needed.

Use query item from another query in filter
Sometimes, complicated logic is applied to identify data item, and then this item will be used to limit data to be selected.

Query 1 to identify Product type
Query 2 to apply filter  [Product type] in ([Query1].[Product type])
Result below

Use query item from another query as singleton

Please see report below


Sometimes, you want to have a title embedded in data container, such as crosstab above. The best way is to create another simple query to get description, while the main query is not requested to change.

Main crosstab query
Singleton query


Crosstab layout, overwrite datekey with singleton

Add singleton with Singleton query



Further update based new Cognos release 10.2.2. with Filter Join Optimization

This new feature allows Cognos to filter one side of a join using the values retrieved by the other side. This minimized the volume of data retrieved by the BI Server, particularly when working with multiple data sources or when one side of the join has significantly more rows than the other side. Report performance can be markedly improved using this feature. In fact, it has shown increases in performance up to 100 times in IBM Lab testing.

Filter Join Optimization in Framework Manager – Join Methods

 

Filter Join Optimization in Framework Manager – Filter Types