Filter Query 2 based on value in Query 1?

[suri.cognos]

can we Filter Query 2 based on value in Query 1?

[Lynn]

can we Filter Query 2 based on value in Query 1?

A little more detail would help you get better responses. Relational or dimensional? Does Query 1 return a single row or many rows? Can you provide examples of results in Query 2 and Query 1 to illustrate?

In theory you can create a join between the two queries such that only matching rows based on the join condition are returned, but this may not necessarily perform well as the two result sets will be returned and further processed on the Cognos side rather than the database side. Apart from that issue it may or may not be suitable for your situation but you've not explained enough to know if this is a good choice for you or not.

[AnalyticsWithJay]

can we Filter Query 2 based on value in Query 1?

Of course! If you are on Cognos 10.2.2, look up Filter Join Optimization, a new feature where you can filter one side of the join with the values that are retrieved by the other side. If this is not what you want to do, you could easily do the following:

Example:
Query 1 contains one data item called Product_ID
Query 2 can have a filter that says MyTable.Product_BKEY IN (Query1.Product_ID)

If you look at the SQL, it will be

Code Select Expand


SELECT Product_BKEY
FROM Query_2
WHERE Product_BKEY IN (SELECT DISTINCT Product_ID FROM Query_1)


You'll also have to enable the cross join property for Cognos to allow this, since your queries are not joined within RS.

Since I don't know much about your solution, use this with caution and monitor performance as you develop it.

Jay

[Lynn]

Of course! If you are on Cognos 10.2.2, look up Filter Join Optimization, a new feature where you can filter one side of the join with the values that are retrieved by the other side.

Good point, and applicable only for DQM.