If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Detail Filter - Force it to go to outer query - How?

Started by cognosda, 08 Oct 2015 01:09:43 PM

Previous topic - Next topic

cognosda

I have a report whose query goes something like this:

SELECT (outer select)
FROM
  (Inner Query
  ) "T0"
WHERE
  CASE
    WHEN ("T0"."C10">"T0"."C11")
    THEN 'A'
    ELSE 'B'
  END ='B' <-- Detail filter 1
AND "T0"."C12" =1 <-- Detail filter 2
AND "T0"."C13"<=6 <-- Detail filter 3
ORDER BY "Field Names"

There are 3 detail filters in the report, and that's what you see in the where clause of the query above. I want the filters to function in such a way that the Detail filter 1 and 2 get applied to the report together, whereas the Detail filter 3 only gets applied to the result of Detail filter 1 and 2 and not along with them. So the query should look something like this:

SELECT
FROM (
(SELECT (outer select)
FROM
  (Inner Query
  ) "T0"
WHERE
  CASE
    WHEN ("T0"."C10">"T0"."C11")
    THEN 'A'
    ELSE 'B'
  END ='B' <-- Detail filter 1
AND "T0"."C12" =1 <-- Detail filter 2
ORDER BY "Field Names"
) T0 WHERE "T0"."C13"<=6 <-- Detail filter 3

How do I make this happen? I have tried "After auto aggregation" property but that gives unexpected results. Maybe I need to create another query and somehow link that to the main query ?

The result sets of the two queries are different and the second one gives the right results.

Thanks for your help.