Filter Data With a Subquery

You can create a report that uses one or more subqueries to perform operations that would otherwise require complex joins and unions. For example, you can create a query that contains a detail filter that references the results of a subquery.

The filter on the subquery references the Minimum life to date product revenue parameter, which obtains its value from a numeric text box prompt on the prompt page. The filter excludes data rows for which the life to date revenue for a product is less than the amount specified.

Procedure

Define the main query and the subquery in the queries element as two separate query elements.

To the main query, add a detailFilter that references the subquery.

Sample XML

<report xmlns="http://developer.cognos.com/schemas/report/7.0/" 
	expressionLocale="en-us">
 <modelPath>/content/package[@name='GO Sales and Retailers']
	 /model[@name='model']</modelPath>
 <queries>
  <query name="Query1">
   <source>
    <model/>
   </source>
   <selection>
    <dataItem aggregate="none" name="Product name">
     <expression>[gosales_goretailers].[Products].[Product name]</expression>
    </dataItem>
    <dataItem aggregate="none" name="Order number">
     <expression>[gosales_goretailers].[Orders].[Order number]</expression>
    </dataItem>
    <dataItem aggregate="none" name="Order date">
     <expression>[gosales_goretailers].[Orders].[Order date]</expression>
    </dataItem>
    <dataItem aggregate="total" name="Revenue">
     <expression>[gosales_goretailers].[Orders].[Revenue]</expression>
    </dataItem>
    <dataItem aggregate="total" name="Quantity">
     <expression>[gosales_goretailers].[Orders].[Quantity]</expression>
    </dataItem>
   </selection>
   <detailFilters>
    <detailFilter>
<filterExpression>[Query1].[Order date]&gt;= ?Date?</filterExpression>
</detailFilter>
<detailFilter>
<filterExpression>[Query1].[Product
name]in ([Query2].[Product name])</filterExpression>
</detailFilter> 
   </detailFilters>
  </query>
  <query name="Query2"> 
   <source>
    <model/>
   </source>
   <selection>
    <dataItem aggregate="none" name="Product name">
     <expression>[gosales_goretailers].[Orders].[Product name]</expression>
    </dataItem>
    <dataItem aggregate="total" name="Revenue">
     <expression>[gosales_goretailers].[Orders].[Revenue]</expression>
    </dataItem>
   </selection>
   <detailFilters> 
    <detailFilter postAutoAggregation="true">
<filterExpression>[Query2].[Revenue]&gt;=?Minimum life
to date product revenue?</filterExpression>
</detailFilter> 
   </detailFilters>
  </query>
 </queries>
 <layouts>
  <layout>
   <reportPages>
	...
   </reportPages>
   <promptPages>
    <page name="Prompt Page1">
     <pageBody>
      <contents>
       <block>
        <contents>
         <selectDate multiSelect="false" parameter="Date"
range="false" required="true"/> 
        </contents>
		 ...
        <contents>
         <textBox numbersOnly="true" 
         parameter="Minimum life to date product revenue"/> 
        </contents>
       </block>
      </contents>
	 ...
   </promptPages>
...
</report>